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

5 minute read
0

I have an Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition DB instance. I want to use AWS Identity and Access Management (IAM) authentication to connect to my instance.

Short description

IAM database authentication is more secure than native authentication methods because of the following reasons:

  • IAM uses your AWS access keys to generate database authentication tokens. 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. This encrypts all data that you transmit to and from your Amazon RDS DB instance.
  • If your application runs on Amazon Elastic Compute Cloud (Amazon EC2), then you can use Amazon 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. To connect to the RDS DB instance, use your 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, complete the following prerequisites:

  • Launch an Amazon 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 Aurora and IAM database authentication for Amazon RDS.

To connect to an RDS DB instance or Aurora PostgreSQL-Compatible DB cluster, use IAM database authentication for PostgreSQL:

  1. Turn on IAM authentication on your RDS DB instance or your Aurora cluster.

  2. Create an IAM user, and then 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 for PostgreSQL DB instance or Aurora PostgreSQL-Compatible cluster as the primary 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 primary 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 username that you created. This creates a temporary password that you use in a later step:

    [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 the Connect now box. From the Connection tab, enter the hostname, port, and username, 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. For the selected SSL mode, enter the certificate's path. To download the path, run the following command:

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

    Note: 2015-root certificates are expired as of 2020. To move to a 2019-root certificate, see Rotating your SSL/TLS certificate.

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

    $ wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
  8. After you create the server, connect to the server. Enter the temporary token that generate-db-auth-token generated for the user iamuser, when prompted.

For psql, run the following command to connect. This example uses the environment variable $PGPASSWORD that you set when you generated the token. This variable 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, then the connection fails. You must generate a new token.

If you still receive an error that's similar to PAM authentication failed for your user, then 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 that the account belongs to. Also, check if there's 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-Compatible

4 Comments

Are the instructions for the certificate still correct? For example https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html#UsingWithRDS.SSL.CertificatesAllRegions lists different URLs to download the certificate bundle from.

Akash
replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied a year ago

Is the database then still aware of the user that assumed the IAM role? Otherwise one could not trace executed queries back to certain users.

replied a year ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied a year ago