AWS allows the user to create DB instances using the Amazon RDS dashboard and connect them to the pgAdmin or other clients. It also allows the user to create a secure connection using either a user password or an IAM authentication key. The key is usable for only 15 minutes and after that, the user needs to create a new key to be able to connect to the server.

This guide will explain the use of IAM credentials to connect the AWS RDS PostgreSQL database.

How to Connect PostgreSQL AWS RDS Database Using IAM Authentication?

To connect to the IAM identification, visit the RDS dashboard from the AWS Management Console:

After that, head into the database instance by clicking on its name:

Head into the “Configuration” section from the database page:

Make sure that the IAM DB authentication is “Enabled”:

Visit the IAM dashboard from the AWS dashboard:

Click on the “Roles” page from the left panel:

Create an IAM role for the RDS:

Use the following policy for the IAM role by simply changing the resource section containing the Account ID and DB Resource ID:

{
  "Version": "2012-10-17",
  "Statement": [
  {
  "Effect": "Allow",
  "Action": [
  "rds-db:connect"
  ],
  "Resource": [
  "arn:aws:rds-db:ap-southeast-1:***********3:dbuser:<DB   Resource ID>/Master <Username>"
  ]
  }
  ]
   }

After that, visit the “Connectivity & security” section and copy the Endpoint:

Use the following command by changing the username and endpoint copied from the RDS page:

psql -h database-1.c6d50j4forkq.ap-southeast-1.rds.amazonaws.com -U postgres

Create a new user named “iamuser” using the LOGIN keyword:

CREATE USER iamuser WITH   LOGIN;

After that, grant the IAM role to the user created previously:

GRANT rds_iam TO iamuser;

The role has been created and attached to the user:

Visit the EC2 dashboard and create an EC2 instance:

Select the instance and expand the “Actions” menu, hover over the “Security” list, and select the “Modify IAM role” button:

Select the role and click on the “Update IAM role” button:

Once the role is attached to the instance, select it and click on the “Connect” button:

Copy the command provided in the “SSH client” section:

Paste the command on the terminal and change the path of the key pair file:

Use the following commands to get the password used to connect to the PostgreSQL database:

export RDSHOST="aurorapg-ssl.cluster-XXXXXXXXXXX.us-west-2.rds.amazonaws.com"
 export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region   ap-southeast-1 --username iamuser)"
   echo $PGPASSWORD

The above command will provide the SSL token password:

Open the pgAdmin client from the local system and click on the “Add New Server” button:

Type the name of the server and turn off the “Connect now” button from the “General” tab:

Head into the “Connection” tab to type the Hostname and username of the DB instance from AWS RDS:

Visit the “Parameter” page to change the SSL mode value to “require” and click on the “Save” button:

Execute the database server and paste the password obtained from connecting to the EC2 instance and click on the “OK” button:

The connection has been established successfully:

That’s all about connecting to the AWS RDS PostgreSQL database using IAM authentication.

Conclusion

To connect to the DB instance using IAM authentication, create an RDS database instance with IAM authentication enabled. Create an IAM role with a policy allowing the DB connection using IAM and then attach it to the EC2 instance. Connect to the EC2 instance using its command from the SSH client and get the password from there. Use the obtained password to connect to the AWS RDS on the pgAdmin client application from the local system.