How do I connect to my Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL using IAM authentication?

Short description

Users can connect to an Amazon RDS DB instance or cluster using IAM user or role credentials and an authentication token. IAM database authentication is more secure than native authentication methods because:

• IAM generates database authentication tokens using your AWS access keys, so you don't need to store database user credentials.
• Authentication tokens have a lifespan of 15 minutes, so you don't need to enforce password resets.
• IAM database authentication requires an SSL connection, so all data that you transmit to and from your RDS DB instance is encrypted.
• If your application is running on Amazon Elastic Compute Cloud (Amazon EC2), then you can use EC2 instance profile credentials to access the database. You don't need to store database passwords on your instance.

To set up IAM database authentication using IAM roles, follow these steps:

1.    Turn on IAM DB authentication on the RDS DB instance.

2.    Create an IAM user and attach an IAM policy that maps the database user to the IAM role.

3.    Attach the IAM role to the EC2 instance.

4.    Generate an AWS authentication token to identify the IAM role.

5.    Download the SSL root certificate file or certificate bundle file.

6.    Connect to the RDS DB instance using IAM role credentials and the authentication token or an SSL certificate.

If you run MySQL, then see How do I allow users to authenticate to an Amazon RDS for MySQL DB instance using their IAM credentials?

Resolution

Before you begin, be sure that you do the following:

• Launch an RDS for PostgreSQL DB instance or Aurora PostgreSQL-compatible cluster that supports IAM database authentication
• Launch an EC2 instance to connect to the database

For more information, see IAM database authentication for Amazon Aurora and IAM database authentication for RDS.

You can use IAM database authentication for PostgreSQL to connect to an Amazon RDS DB instance or Amazon Aurora PostgreSQL DB cluster. 1.    Turn on IAM authentication on your RDS DB instance or your Aurora cluster.

2.    Create an IAM user and attach the following policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "rds-db:connect"
      ],
      "Resource": [
        "arn:aws:rds-db:<region>:<account>:dbuser:<resource id>/iamuser"
      ]
    }
  ]
}

Note: Replace iamuser with the IAM user information.

3.    Log in to your Amazon RDS PostgreSQL DB instance or Aurora PostgreSQL cluster using the master user.

psql

psql -h {database or cluster endpoint} -U {Master username} -d {database name}

pgAdmin

Choose Servers from the pgAdmin navigation pane. Then, choose the server name and enter the master user password.

4.    Create a child user that has the same name as the IAM user:

CREATE USER iamuser WITH LOGIN; 
GRANT rds_iam TO iamuser;

5.    Run generate-db-auth-token with the user name that you created. This creates a temporary password for the user to be used later. See the following example connection:

[ec2-user@ip-172-31-24-237 ~]$ export RDSHOST="aurorapg-ssl.cluster-XXXXXXXXXXX.us-west-2.rds.amazonaws.com"
[ec2-user@ip-172-31-24-237 ~]$ export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-west-2 --username iamuser)"
[ec2-user@ip-172-31-24-237 ~]$ echo $PGPASSWORD
aurorapg-ssl.cluster-XXXXXXX.us-west-2.rds.amazonaws.com:5432/?Action=connect&DBUser=iamuser&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Expires=900&X-Amz-Credential=AKIA2O5GXNVDTAMABZFE%2F20190909%2Fus-west-2%2Frds-db%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Date=20190909T171907Z&X-Amz-Signature=ead28003477c3082e5a17529ac8316db4f4bdf2fa8f79d3aaea806e9bafa2673
[ec2-user@ip-172-31-24-237 ~]$

6.    To create a new server connection in pgAdmin, choose the General tab and clear (uncheck) the Connect now box. From the Connection tab, enter the host name, port, and user name, but don't enter the password yet. From the SSL tab, set the SSL mode to Require and save the server connection.

7.    (Optional) From the SSL tab, you can change the SSL mode to verify-full. Enter the path of the certificate (that you can download by running the below command) based on SSL mode selected.

wget https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem

Note: If you use a 2015-root certificate, note that it expired in 2020. To move to a 2019-root certificate, see Rotating your SSL/TLS certificate.

If your application doesn't accept certificate chains, then run the following command to download the certificate bundle that includes both the old and new root certificates:

$ wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

8.    After you create the server, connect to the server by entering the temporary token that generate-db-auth-token generated for the user iamuser, when prompted.

When using psql, run the following command to connect. The following example uses the environment variable $PGPASSWORD that you set when you generated the token. It's set in the machine and initiates the connection:

psql -h aurorapg-ssl.cfkx5hi8csxj.us-west-2.rds.amazonaws.com -p 5432 "sslmode=verify-full sslrootcert=rds-ca-2019-root.pem dbname=aurora_pg_ssl user=iamuser"

Note: Each token expires 15 minutes after you generate it. If you try to re-establish the connection with the same token, the connection fails. You must generate a new token.

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

If the account is part of an organization, then check to see if there is a hierarchy of the IAM user or role 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.

Related information

Using IAM authentication with Aurora PostgreSQL