How do I connect to my RDS PostgreSQL instance using IAM authentication?

Last updated: 2020/08/19

How can I use AWS Identity and Access Management (IAM) authentication to connect to an Amazon Relational Database Service (Amazon RDS) PostgreSQL DB instance?

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), 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. Enable 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, see How do I allow users to connect to an Amazon RDS MySQL DB instance with IAM credentials?

Resolution

Before you begin, be sure that you launched an RDS PostgreSQL DB instance or Aurora PostgreSQL cluster that supports IAM database authentication and an EC2 instance to connect to the database.

For more information, see IAM database authentication for Amazon Aurora and Availability for 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.    Enable 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: In the policy example, the IAM user is called iamuser.

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. 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, it expires in 2020. To move to a 2019-root certificate, see Rotating your SSL/TLS certificate.

Note: If your application doesn't accept certificate chains, 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 have created 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:

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 have generated 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 like "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, add rds-db:* to the service control policy (SCP) of the organization unit that the account belongs to. For more information, see Updating an SCP.

If the account is part of an organization, 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.


Did this article help?


Do you need billing or technical support?