How do I troubleshoot problems while connecting to Amazon RDS for PostgreSQL or an Aurora PostgreSQL cluster using IAM authentication?

Short description

You might not be able to connect to your Amazon RDS for PostgreSQL or Aurora DB instance using IAM authentication for one of these reasons:

• IAM authentication is turned off
• Insufficient IAM role permissions or incorrect policy
• Database user is improperly configured
• Incorrect connection string
• Incorrect IAM entity

Resolution

IAM authentication is turned off

By default, IAM authentication is turned off. Review the configuration settings for your RDS for PostgreSQL instance or Aurora PostgreSQL cluster. Make sure that IAM authentication is turned on. From the Amazon RDS console, you can modify the Aurora cluster or RDS instance by choosing Database Authentication. Then, choose IAM database authentication and Continue to update your configuration settings.

Note: When you choose Apply Immediately when updating your cluster configuration settings, all pending modifications are applied immediately. This action doesn't result in downtime.

Insufficient IAM role permissions

To successfully connect to your DB instance using IAM database authentication, you must have access to rds-db:connect action. The rds-db:connect action allows connections to the DB instance.

For example:

{
    "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: Replace db_user_name with the database account user that's associated with the IAM authentication.

Also, make sure that you're using the correct resource ID (instead of specifying only the ARN). To find a DB instance's resource ID, choose the DB instance or cluster in the Amazon RDS console. Then, choose the Configuration tab to view the resource ID.

For more information about the elements listed in the example IAM policy, see Creating and using an IAM policy for IAM database access.

Note: IAM authentication doesn't support service-specific context keys that can be used in the condition element of policy statements. IAM authentication also doesn't support all global condition context keys. For more information about global condition context keys, see AWS global condition context keys in the IAM User Guide.

Database user is improperly configured

To use IAM authentication with PostgreSQL, grant the database user the rds_iam role. To confirm that this role is associated with the user, run this command:

Note: This command runs only in the PostgreSQL client.

\du

Your output looks similar to this:

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 use this query, which runs 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) 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 this command:

CREATE USER db_user_name WITH LOGIN; 
GRANT rds_iam TO db_user_name;

Incorrect connection string

Authentication tokens consist of several characters that can be unwieldy to use on the command line. Save the token to an environment variable instead, and then use that variable when you connect to your PostgreSQL DB instance.

For example:

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"

For more information about how to connect to a PostgreSQL DB instance using an environment variable, see Connecting to an Aurora PostgreSQL cluster.

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.

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

Incorrect IAM entity

Make sure that the authentication token is being generated by the correct IAM role. To check the assumed role of the current IAM user, run this command:

aws sts get-caller-identity

If the assumed role isn't correct, you can switch to the correct IAM user/role or assume the correct role. Be sure that you are using the correct IAM role to generate the authentication token.

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.

If the account is part of an organization, check whether there is 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 are using permissions boundaries for IAM entities, make sure that rds-db:connect action is allowed for your IAM user/role. For more information on permissions boundaries, see Permissions boundaries for IAM entities. To modify permissions boundaries for a user, see Changing the permissions boundary for a user.

---