Connecting with IAM Authentication to pgAdmin on Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL

This blog post was last reviewed and updated in July 2024.

Amazon Relational Database Service (RDS) allows you to utilize AWS Identity and Access Management (IAM) for managing database access to your Amazon RDS for PostgreSQL instances and Amazon Aurora PostgreSQL clusters. Database administrators can link database users with IAM users and roles. With IAM database authentication, there’s no need to use a password for connecting to a database cluster; instead, you’ll utilize an authentication token.

An authentication token is a distinctive string generated by Aurora upon request, employing AWS Signature Version 4. Each token remains valid for 15 minutes. You won’t have to store user credentials in the database, as IAM manages authentication externally. For more details, refer to the Client Authentication section on the PostgreSQL documentation website.

This post outlines how to implement IAM authentication with tools that you may already be using to connect to your Aurora PostgreSQL cluster. The procedures are equally applicable for your Amazon RDS for PostgreSQL instance. By following the provided commands, you can set up resources and configure your environment for IAM authentication.

Additionally, the post guides you through connecting to the cluster using either the psql command-line tool or pgAdmin with IAM credentials.

Prerequisites

RDS supports Transport Layer Security (TLS) for encrypting client/server communications for PostgreSQL database instances. It is highly advisable to enable SSL/TLS certificate verification. For detailed information, check Securing connections to RDS for PostgreSQL with SSL/TLS. You must download the certificate from the specified Amazon S3 bucket in the user guide. Furthermore, before setting up your Aurora database cluster, ensure your environment is configured for Amazon Aurora.

In this post, two different IAM principals are employed. The first is used to execute the AWS Command Line Interface (AWS CLI) command for creating the database, enabling IAM authentication, and establishing IAM resources for IAM database authentication. The second IAM principal corresponds to the database user account, which is utilized for generating authentication tokens that grant access to the database.

Note: This post employs an IAM user as a second IAM principal to demonstrate the feature. However, for production workloads, AWS advises adhering to security best practices by implementing federated authentication with multi-factor authentication (MFA) for human identities and IAM roles with temporary credentials for machine identities. For further insights, refer to Achieve auditability with Amazon RDS IAM authentication using attribute-based access control to explore using IAM database authentication via federation for human identities.

Setup

You can either utilize your existing Aurora PostgreSQL cluster or RDS for PostgreSQL database and enable IAM database authentication, or create a new one. If you need to provision a new Aurora PostgreSQL cluster, you can do so through the AWS Management Console, AWS CLI, AWS Software Development Kit (SDK), or an AWS CloudFormation template. This post uses the AWS CLI to create a new Aurora PostgreSQL cluster. For guidance on setting up authentication and access credentials for AWS CLI, see the appropriate documentation. The AWS CLI is employed with the first IAM principal’s credentials to provision a new Aurora PostgreSQL cluster, while either the AWS CLI or an AWS SDK can be used to generate an authentication token; this post employs the AWS CLI with the second IAM principal’s credentials for token generation.

Creating a Database

If an Aurora PostgreSQL cluster or RDS PostgreSQL instance is not already available, you’ll need to create one. Configure your database with a security group that permits access from your client machine. Utilize the following CLI command:

aws rds create-db-cluster --db-cluster-identifier <cluster-name> --engine aurora-postgresql 
--master-username <user-name> --master-user-password <password> 
--db-subnet-group-name <subnet-name> --vpc-security-group-ids <security-group>

Be sure to replace the placeholders with your actual cluster name, user name, password, subnet name, and security group. If an Aurora PostgreSQL database is already in place that you wish to work with, you may skip this step.

The command above creates a database cluster. If you create a database cluster via the console, RDS automatically generates the primary instance (writer) for your database cluster. However, if you use the AWS CLI, you must explicitly create the primary instance for your database cluster using the following command:

aws rds create-db-instance --db-instance-identifier <instance-name> 
--db-cluster-identifier <cluster-name> --engine aurora-postgresql --db-instance-class db.r4.large

Again, replace the placeholders with the appropriate instance name and cluster name. For additional information, see Creating an Amazon Aurora DB Cluster.

Enabling IAM Authentication

By default, IAM database authentication is turned off for database instances and clusters. You can enable (or disable) IAM database authentication using the console, the AWS CLI, or the RDS API. For more information, see Enabling and Disabling IAM Database Authentication.

To enable IAM authentication from the command line, you need to know your cluster name, which can be found on the RDS console or through the output values of the describe-db-clusters AWS CLI command. Use the following command:

aws rds describe-db-clusters 
--query "DBClusters[*].[DBClusterIdentifier]"

The command below enables IAM authentication on the cluster:

aws rds modify-db-cluster 
--db-cluster-identifier <cluster-name> 
--apply-immediately 
--enable-iam-database-authentication

Be sure to replace the placeholder with your cluster name.

IAM Resources for Database Access

This post attaches a policy granting an action of rds-db:connect to a specific IAM user. The following diagram demonstrates this workflow. You can create other Amazon Resource Names (ARNs) to support various access configurations and attach policies to multiple users or roles. For more details, see Creating and Using an IAM Policy for IAM Database Access.

Policy

To enable an IAM user or role to connect to your database instance or cluster, you must create an IAM policy and attach it to an IAM user or role. For additional information, see Create and Attach Your First Customer Managed Policy.

You’ll construct the policy document using four key details:

The Region of your cluster
Your AWS account number
The database resource ID
Your database user name

Specify an Amazon Resource Name (ARN) that describes one database user account within a single database instance using the format:

arn:aws:rds-db:<region>:<account-id>:dbuser:<resource-id>/<database-user-name>

Note: “dbuser” is a keyword in the above ARN format.

For RDS:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["rds-db:connect"],
      "Resource": ["arn:aws:rds-db:us-east-1:123456789012:dbuser:db-ABCDEFGHIJKL01234/mydbuser"]
    }
  ]
}

For Aurora:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["rds-db:connect"],
      "Resource": ["arn:aws:rds-db:us-west-2:123456789012:dbuser:db-ABCDEFGHIJKL01234/mydbuser"]
    }
  ]
}

For further insights, you can visit the excellent resource on Amazon New Hire Orientation to understand what to expect on your first day.

If you’re looking to transition to a new position, check out this blog post for helpful tips. Also, as outlined by SHRM, understanding the prescreening process for new hires is crucial for compliance.

Site Address: 6401 E HOWDY WELLS AVE LAS VEGAS NV 89115
Location Name: Amazon IXD – VGT2