Securely Accessing AWS PostgreSQL RDS Instances With IAM

Relational database services have long been a cornerstone for storing and retrieving application data, dating back to the 1970s. However, managing credentials for applications, users, and administrators has always been a challenge due to the static nature of traditional credentials. With AWS RDS, we can address this issue by offering built-in IAM authentication for RDS PostgreSQL, enabling secure, short-lived access scoped to specific users or groups. Additionally, this approach enhances auditing capabilities by allowing detailed tracking of who accessed the database and when.

Prerequisites

Before setting up secure PostgreSQL authentication, ensure you have the following:

• An AWS account with administrative access.
• AWS CLI with account configuration connected.
• An RDS PostgreSQL instance.
• PSQL installed.
• Terraform installed on your local machine with AWS provider configured.

Step 1: Set Up IAM Roles and Policies

In this step, we’ll create an IAM policy and attach it to a group that will be used to authenticate to the RDS instance.

Terraform Configuration

First, define the necessary variables (we’ll assume you have a way of securely inserting your account ID, but for now, we’ll use a variable) that will be used within the following steps:

variable "account_id" {
  description = "The AWS account ID"
  type        = string
}

variable "usergroup_name" {
  description = "The name of the IAM group for database users"
  type        = string
  default     = "DatabaseUsers"
}

variable "db_iam_user" {
  type        = string
  description = "The name of the IAM user for the database"
  default     = "db_users"
}

variable "region" {
  description = "The AWS region where the RDS instance is located"
  type        = string
  default     = "us-west-2"
}

variable "db_instance_id" {
  description = "The resource ID of the RDS instance"
  type        = string
}

Note: We defaulted the database user to db_users if none is provided, and for future access examples will utilize that IAM name. In practice, you can provide any name you’d like and change the subsequent commands for accessing the RDS instance.

Create The IAM Policy

resource "aws_iam_policy" "db_policy" {
  name        = "DatabaseAccessPolicy"
  description = "Policy for IAM user to connect to RDS instance"

  policy = jsonencode({
    Version = "2012-10-17",
    Statement = [
      {
        Effect = "Allow",
        Action = [
          "rds-db:connect"
        ],
        Resource = [
          "arn:aws:rds-db:${var.region}:${var.account_id}:dbuser:${var.db_instance_id}/${var.db_iam_user}"
        ]
      }
    ]
  })

  tags = {
    Environment = "production"
  }
}

The aws_iam_policy resource above creates a new IAM policy named DatabaseAccessPolicy. This policy allows the rds-db:connect action on a specific RDS database user. The Resource field specifies the ARN of the RDS database user, which is constructed using the provided variables and is tagged with an environment label production.

For more information on Terraform’s creation of the policy, refer to the official aws_iam_policy documentation.

Attach Policy To Group

resource "aws_iam_group_policy_attachment" "db_policy_attachment" {
  group      = var.usergroup_name
  policy_arn = aws_iam_policy.db_policy.arn
}

The aws_iam_group_policy_attachment resource we’ve just created attaches the previously created DatabaseAccessPolicy to an existing IAM group specified by the usergroup_name variable. This allows all users in the specified IAM group to use the permissions defined in the DatabaseAccessPolicy. For this guide, we’ll presume that this AWS group already exists and the users desiring to access the DB are already members.

For more information on Terraform’s policy group attachment, refer to aws_iam_group_policy_attachment documentation.

Step 2: Enable IAM Authentication On Your RDS Instances

Now that we’ve configured a user with the permissions within AWS to access the database, we need to configure RDS PostgreSQL to recognize and accept the AWS IAM authentication method. There are a few ways to achieve this.

Terraform Configuration

If you are already using Terraform for your project, or just because we used it in a previous step, we can enable it with the flag iam_database_authentication_enabled on a aws_db_instance. This boolean flag enables a mapping of AWS IAM accounts to database accounts.

AWS CLI

If you prefer to do this with a CLI tool, AWS has you covered with the handy modify-db-instance command.

An example of enabling IAM authentication with this command is as follows:

aws rds modify-db-instance \
    --db-instance-identifier <insert-db-identifier> \
    --enable-iam-database-authentication \
    --apply-immediately

Step 3: Create the Database User

After enabling IAM authentication on your RDS instance, you need to create a PostgreSQL user that matches the IAM user. This user will be used to authenticate against the database using the IAM-generated token.

You can utilize psql or a database migration tool such as Liquibase for Java or golang-migrate for GO.

In either case, you can utilize the following SQL to create and map IAM to a newly created PostgreSQL user.

CREATE USER db_users WITH LOGIN;
GRANT rds_iam TO db_users;

Step 4: Connecting To Your RDS Database

Lastly, we need to grab our token and log in to our database.

To generate a token in AWS RDS, we can utilize the AWS CLI command generate-db-auth-token:

aws rds generate-db-auth-token \
    --hostname {db-endpoint} \
    --port {db-port} \
    --region {aws-region} \
    --username db_users # This is taken from the user created for IAM access in the previous steps

Once the command is issued, it will return a short-lived authentication token tied directly to our IAM group.

Example token:

mydb.123456789012.us-east-1.rds.amazonaws.com:3306/?Action=connect&DBUser=db_user&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIEXAMPLE%2Fus-east-1%2Frds-db%2Faws4_request&X-Amz-Date=20210123T011543Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=88987EXAMPLE1EXAMPLE2EXAMPLE3EXAMPLE4EXAMPLE5EXAMPLE6

With this returned token, we can now connect to our database in AWS with psql using the following command:

psql "host=<rds-endpoint> port=5432 dbname=<db-name> user=db_user password=<iam-token> sslrootcert=<certificateFile> sslmode=verify-full"

Note: In order to connect, you may need to utilize a certificate file provided by AWS to access RDS databases.

Final Thoughts

By following this guide, you have successfully set up IAM authentication for your AWS RDS PostgreSQL database. This approach enhances the security of your database by leveraging AWS IAM roles and policies, reducing the need for static database credentials. You have learned how to create an IAM user, configure RDS for IAM authentication, generate an authentication token using the AWS CLI, and connect to your database using psql. This method not only simplifies credential management but also aligns with best practices for secure database access in the cloud.