Arun Raj T.M.
- Jul 4, 2023
- 5 min read

How to connect to PostgreSQL AWS RDS using IAM Authentication

If you are using Amazon Relational Database Service (RDS) for PostgreSQL, you might be wondering how to connect to your database securely and conveniently. One option is to use AWS Identity and Access Management (IAM) authentication, which allows you to use your AWS credentials to generate temporary tokens that can be used as database passwords. This way, you don't have to store or manage database user credentials, and you can also leverage IAM policies and roles to control access to your database.

In this blog post, I will show you how to set up and use IAM authentication for PostgreSQL RDS in a few simple steps.

Step – 1

Create/Modify your PostgreSQL with IAM DB Authentication Enabled

1.a. Using AWS Console

1.a.i. If the DB is getting created then make sure to select Password and IAM database authentication under Database Authentication

1.a.ii. If the DB is already created then click on Modify and then select Password and IAM database authentication under Database Authentication and Continue

Once the modification is applied (immediately/after the maintenance window), you should see the option Enabled.

1.b. Using AWS CLI

1.b.i. Run the following command to modify your RDS instance and enable IAM DB authentication:

aws rds modify-db-instance \
    --db-instance-identifier <your-db-instance-identifier> \
    --enable-iam-database-authentication

Step – 2

Create the IAM user that you want to give RDS access.

2.a. From the AWS Console

2.a.i. Click on Add users

2.a.ii. Give the username and click on Next

2.a.iii. Under Set permissions select Add user to group and then click Next

2.a.iv. Under Review and Create click Next

2.a.v. Once the user is created click on the created user and under the Add permission drop down selection select Add inline policy

2.a.vi. Enter the following code as follows and click Review Policy. Give a name and Save

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

Note:

<region>: Replace with region of DB
<account>: Replace with account_ID of DB
<resource id>: Replace with DB Resource ID
iamuser: Replace with IAM user created

2.b. From the AWS CLI

2.b.i. Run the following command to create an IAM user with programmatic access:

aws iam create-user \
    --user-name <your-user-name>

Note:

<your-user-name> : Replace with IAM username to be created

2.b.ii. Run the following command to create an access key for your user:

aws iam create-access-key \
    --user-name <your-user-name>

Note:

<your-user-name> : Replace with IAM username to be created

2.b.iii. Save the output of this command, which contains your user's access key ID and secret access key. You will need them later to generate an authentication token.
2.b.iv. Run the following command to create an IAM policy for database access:

aws iam create-policy \
    --policy-name <your-policy-name> \
    --policy-document file://<path-to-your-policy-document>

Note:

<path-to-your-policy-document> - Replace with the path to a JSON file that contains the following policy document:

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

<region> - Replace with region of DB
<account-id> - Replace with account_ID of DB
<db-instance-resource-id> - Replace with DB Resource ID
<db-user-name> - Replace with DB username
You can find these values in the Amazon RDS console or by running `aws rds describe-db-instances`.

2.b.v. Save the output of this command, which contains the ARN of your policy. You will need it later to attach it to your user.
2.b.vi. Run the following command to attach the policy to your user:

aws iam attach-user-policy \
    --user-name <your-user-name> \
    --policy-arn <your-policy-arn>

Step-3

Creation of User Access Keys

3.a. From the AWS Console

3.a.i. Select the Security Credentials under the created user

3.a.ii. Scroll down to Access keys and click on Create access key.

3.a.iii. As per the requirement to use the key select option (selecting Application running on an AWS compute service as I will be accessing the DB from an EC2), select the checkbox declaration of understanding and click Next

3.a.iv. Give a Description tag (optional) and click on Create access key

3.a.v. Once the Secret key is created download the csv by clicking on Download .csv file to store safely and click on Done

3.b. From the AWS CLI

3.b.i. Run the following command to generate an authentication token using your IAM user credentials:

aws rds generate-db-auth-token \
    --hostname <your-db-endpoint> \
    --port 5432 \
    --region <your-region> \
    --username <your-db-user>

Note:

<your-db-endpoint> - Replace with DB endpoint URL
<your-region> - Replace with region of DB
<your-db-user> - Replace with DB user
You can find these values in the Amazon RDS console or by running `aws rds describe-db-instances`.

3.b.ii. The output of this command is a long string that looks like this:

<your-db-endpoint>:5432/?Action=connect&DBUser=<your-db-user>&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Expires=900...

3.b.iii. This is your authentication token, which is valid for 15 minutes. You can use it as your database password to connect to your database using IAM authentication.

Step-4

Creation of DB Access Role

4.a. From the AWS Console

4.a.i. Click on Create role

4.a.ii. Under Trusted entity type select AWS Service, Under Use case select EC2 and then select Next

4.b.iii. Under Add permissions search “rdsfull” and select AmazonRDSFullAccess and click on Next

4.a.iv. Give the role a name and click on Create role

4.b. From the AWS CLI

4.b.i. Run the following command to create an IAM role with the trust policy document and the RDSFullAccess policy:

aws iam create-role \
    --role-name <your-role-name> \
    --assume-role-policy-document file://trustpolicy.json \
    --path /service-role/ \
    --description "<your-role-description>" \
    --permissions-boundary arn:aws:iam::aws:policy/RDSFullAccess

Note:

trustpolicy.json - Replace with json file with the contents:

{
    "Version": "2012-10-17",
    "Statement": [
      {
        "Effect": "Allow",
        "Principal": { "Service": "ec2.amazonaws.com"},
        "Action": "sts:AssumeRole"
      }
    ]
  }

<your-role-name > - Replace with role name
<your-role-description> - Replace with role description

Step-5

Adding Role to EC2

5.a. From the AWS Console

5.a.i. Select the EC2 (any) created, Click on Actions, then under Security select Modify IAM role

5.a.ii. Select the created RDS access role created and click on Update IAM role

5.b. From the AWS CLI

5.b.i. Run the following command to associate an IAM role with your RDS instance:

aws rds add-role-to-db-instance \
    --db-instance-identifier <your-db-instance-identifier> \
    --role-arn <your-role-arn>

Note:

<your-db-instance-identifier> - Replace with the DB instance id
<your-role-arn> - Replace with the Role ARN that was created earlier

Step – 6

Setting up the IAM user on DB and accessing the DB using the IAM user and token

6.a. Connecting to the RDS using DB Credentials

6.a.i. Connect to the PostgreSQL RDS using pgadmin on the created server (I have used a Windows EC2 for this demo). Click on Add New Server

6.a.ii. Give a name of your choice,

6.a.iii. Under the Connection tab enter the RDSEndpoint URL and provide the Master username under Username

6.a.iv. Right click on the created server and click on Connect Server

6.a.v. In the prompt enter the Master password of the RDS DB and click on OK

6.a.vi. You should be able to see the DB connected as follows

6.b. Creating the IAM user using rds_iam on the DB

6.b.i. Right click on the DB and click on Query Tool

6.b.ii. In the query box enter the query as follows and click on run:

CREATE USER iamuser WITH LOGIN;
GRANT rds_iam TO iamuser;

6.c. Creating the tocken for the IAM user created

6.c.i. Connect to an EC2 (Linux machine) and run aws configure

Note:

AWS Access Key ID: Provide AWS Access key ID generated in step 3
AWS Secret Access Key: Provide AWS Secret key generated in step 3
Default region name: Provide region details of the RDS and Instance
Default output format: json

6.c.ii. Run the following commands to create the token

export RDSHOST="RDSendpoint.URL"
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region ap-south-1 --username iamuser)"
echo $PGPASSWORD

Note:

RDSEndpoint.URL – Replace with the RDSEndpoint URL
Iamuser – replace with the user created
Copy the token and keep handy (it is a very long token that is generated)
The token is valid only for 15 minutes and if the DB connection is tried after will be unsuccessful

6.d. Connecting the RDS using the IAM user.