How do I troubleshoot errors I receive when I use IAM authentication to connect to an Amazon RDS for PostgreSQL instance or Aurora PostgreSQL cluster?

4 minute read
2

I want to connect to my Amazon Relational Database Service (Amazon RDS) for PostgreSQL instance or Amazon Aurora PostgreSQL-Compatible Edition cluster. When I use AWS Identity Access Management (IAM) authentication to connect, I get the "PAM authentication failed for user" error.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

IAM authentication is turned off

By default, IAM authentication is turned off. Turn on IAM authentication for your RDS for PostgreSQL instance or Aurora PostgreSQL-Compatible cluster.

The IAM policy doesn't include the rds-db:connect action

To use IAM database authentication to connect to your DB instance, update your policy to include the rds-db:connect action.

Example policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                " arn:aws:rds-db:region:account-id:dbuser:(DbiResourceId for RDS PostgreSQL or DbClusterResourceId for Aurora PostgreSQL)/db_user_name"
            ]
        }
    ]
}

Note: Make sure that you use the correct resource ID and ARN in the policy. To find a DB instance's resource ID, select the DB instance or cluster on the Amazon RDS console, and then choose the Configuration tab.

For more information, see Creating and using an IAM policy for IAM database access.

Note: IAM authentication doesn't support service-specific or global condition context keys in the condition element of policy statements.

The rds_iam role isn't associated with the database user

To use IAM authentication with PostgreSQL, the database user must assume the rds_iam role. To confirm that this role is associated with the user, run the following command in the PostgreSQL client:

\du

Expected output:

                                         List of roles
Role name       |                         Attributes                         | Member of                          
----------------+------------------------------------------------------------+-------------------------------------------------------------
db_user_name    |                                                            | {rds_iam}                                
postgres        | Create role, Create DB                                     | {rds_superuser}          
                | Password valid until infinity                              |

You can also run the following query on any platform:

SELECT r.rolname,
       ARRAY(
           SELECT b.rolname
           FROM pg_catalog.pg_auth_members m
           JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
           WHERE m.member = r.oid
       ) AS memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

To create a user and grant the rds_iam role, run the following command:

CREATE USER db_user_name WITH LOGIN;
GRANT rds_iam TO db_user_name;

The connection is incorrect

Authentication tokens include several characters that can be difficult to use in the command line. Instead, save the authentication token to an environment variable. Then, use the environment variable when you connect to your Aurora PostgreSQL cluster.

Example authentication token generation with environment variables:

export RDSHOST='aurorapostgresql.cluster-abcdef12ghi.us-east-1.rds.amazonaws.com'
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-east-1 --username db_user_name)"
psql "host=$RDSHOST port=5432 sslmode=verify-full sslrootcert=global-bundle.pem dbname=postgres user=db_user_name password=$PGPASSWORD"

When the client tries to connect to the DB instance without an SSL/TLS connection, you receive the following error:

"FATAL: no pg_hba.conf entry for host "host.ip", user "<username>", database "postgres", SSL off FATAL: password authentication failed for user "db_user_name""

IAM database authentication requires an SSL/TLS connection because all data transmitted to and from your DB instance is encrypted. To avoid the preceding error, make sure that you're using an SSL/TLS connection. It's a best practice to use the sslmode option as verify-full, and provide the certificate in the sslrootcert option in your connection string.

To download the latest certificate authority (CA) certificate, see Using SSL/TLS to encrypt a connection to a DB cluster.

You're using an incorrect IAM role

You might receive an error message because you're using an incorrect IAM role to generate the authentication token. To check the assumed role of the current IAM user, run the get-caller-identity command:

aws sts get-caller-identity

If the assumed role is incorrect, then either switch from an IAM user to the correct IAM role, or assume the correct role.

If you still receive an error message, then check whether the AWS account is part of an AWS Organizations organization. If the account is part of an organization, then create a service control policy (SCP) that allows rds-db:. For more information, see SCP effects on permissions.

Also, check whether there's a hierarchy of the IAM user that doesn't have the rds-db: permission. For more information, see How to use service control policies to set permission guardrails across accounts in your AWS Organization.

If you use permissions boundaries for IAM entities, then allow the rds-db:connect action for your IAM user or role. To modify permissions boundaries for a user, see To change the permissions boundary for a user.

AWS OFFICIAL
AWS OFFICIALUpdated 5 months ago
6 Comments

Please clarify the "add rds-db:* to organization's SCP" statement towards the bottom of the article.

If you still receive an error similar to "PAM authentication failed for your user", check whether the AWS account is part of an AWS Organizations organization. If the account is part of an organization, then add rds-db:* to organization's SCP. For more information, see Creating, updating, and deleting service control policies.

Service Control Policies are meant to prevent actions, no? Even using the "create policy" interface gives you a "must be a Deny" message when attempting to create a policy with Allow for the rds-db service.

replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
EXPERT
replied a year ago

We just ran into this issue while using the python SQLAlchemy library. SQLAlchemy's create_engine method typically accepts a URL following a pattern like: postgresql+psycopg2://{USER}:{PASS}@{ENDPOINT}/{DBNAME}. We found that the tokens returned by generate_db_auth_token include URL-sensitive characters that need to be escaped before creating the connection:

import boto3
import sqlalchemy as sa
from urllib.parse import quote_plus

token = rds.generate_db_auth_token(HOST_NAME, PORT, USER)
token_escaped = quote_plus(token)
pg_url = f"postgresql+psycopg2://{USER}:{token_escaped}@{HOST_NAME}/{DB_NAME}?sslmode=require"
engine = sa.create_engine(pg_url)
conn = engine.connect()

We confirmed that the above code succeeds as-is, but fails if you use token instead of token_escaped when constructing pg_url.

See https://docs.sqlalchemy.org/en/20/core/engines.html#escaping-special-characters-such-as-signs-in-passwords. If (unlike us, at present) you are using a current version of SQLAlchemy, you can use sqlalchemy.URL to construct the URL instead of using the urllib.parse module as we did.

-- Jeff

replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied a year ago

I am still getting FATAL: password authentication failed for user even though I am providing a CA certificate and using the sslmode=verify-full.

If you get an error similar to the one in this example, then the client is trying to connect to the DB instance without SSL.

FATAL: no pg_hba.conf entry for host "host.ip", user "<username>", database "postgres", SSL off 
FATAL: password authentication failed for user "db_user_name"

IAM database authentication requires an SSL connection, and all data transmitted to and from your DB instance is encrypted. To avoid this error, make sure that the connection is using SSL. It's a best practice to use the sslmode option as verify-full and provide the certificate in the sslrootcert option in your connection string.

replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied a year ago