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

Short Description

You might receive the "PAM authentication failed for user" error message when the following occurs:

• IAM authentication is turned off
• The IAM policy doesn't include the rds-db:connect action
• The rds_iam role isn't associated with the database user
• The connection is incorrect
• An incorrect IAM role is used

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 Amazon 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.

The following is an 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 Amazon Resource Name (ARN) in the policy.

To find a DB instance's resource ID, select the DB instance or cluster in the Amazon Aurora and 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

The following is the 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.

The following is an 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 use 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.

An incorrect IAM role is used

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.

Connect from a different AWS account

If you connect to an Aurora PostgreSQL-Compatible cluster from a different AWS account, then generate the authentication token using temporary credentials from the cluster's account. Run the following assume-role AWS CLI command to assume a role in the cluster's account:

aws sts assume-role \
    --role-arn arn:aws:iam::CLUSTER-ACCOUNT-ID:role/ROLE-NAME \
    --role-session-name cross-account-session

Note: Replace CLUSTER-ACCOUNT-ID with the AWS account ID where the cluster resides and ROLE-NAME with your IAM role name.

Then, export the temporary credentials and generate the authentication token:

export AWS_ACCESS_KEY_ID=TEMPORARY-ACCESS-KEY
export AWS_SECRET_ACCESS_KEY=TEMPORARY-SECRET-KEY
export AWS_SESSION_TOKEN=TEMPORARY-SESSION-TOKEN

export PGPASSWORD="$(aws rds generate-db-auth-token --hostname CLUSTER-ENDPOINT --port 5432 --region REGION --username DB-USER-NAME)"

Note: Replace TEMPORARY-ACCESS-KEY, TEMPORARY-SECRET-KEY, and TEMPORARY-SESSION-TOKEN with the values from the assume-role output. Replace CLUSTER-ENDPOINT with your cluster endpoint, REGION with your AWS Region, and DB-USER-NAME with your database user name.

If you generate the token using the source account's credentials directly, then you receive a "password authentication failed" error even if all IAM policies and trust relationships are correctly configured. The token must be generated from credentials that belong to the same account as the cluster.

Related information

IAM database authentication for Aurora

Connecting to your DB cluster using IAM authentication

To change the permissions boundary for a user