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?

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.