IAM access to PostgreSQL databases

Set up IAM database authentication for a Golden Path PostgreSQL database, so applications and developers connect with short-lived IAM tokens instead of passwords.

Golden Path database clusters enable IAM database authentication by default (iam_database_authentication_enabled = true in the terraform-aws-modules/rds-aurora module). The AWS documentation explains the benefits and limitations of IAM database authentication. For background on the database choices, read the RFC for RDBMS in the Golden Path.

Step 1: Find the master credentials

The database template stores the cluster endpoint and master username in AWS Parameter Store. The cluster and its parameters follow the naming pattern <environment>-main:

ENVIRONMENT=my-team-dev

aws ssm get-parameter \
  --name "/$ENVIRONMENT/database/$ENVIRONMENT-main/db_endpoint" \
  --query Parameter.Value \
  --output text

aws ssm get-parameter \
  --name "/$ENVIRONMENT/database/$ENVIRONMENT-main/db_username" \
  --query Parameter.Value \
  --output text

The master username is root. AWS manages the master password (manage_master_user_password = true in the database template) and stores it in AWS Secrets Manager, not in Parameter Store:

SECRET_ARN=$(aws rds describe-db-clusters \
  --db-cluster-identifier "$ENVIRONMENT-main" \
  --query "DBClusters[0].MasterUserSecret.SecretArn" \
  --output text)

aws secretsmanager get-secret-value \
  --secret-id "$SECRET_ARN" \
  --query SecretString \
  --output text | jq -r .password

Step 2: Create the database user

This is a manual, one-time step per database. Connect to the database as root through the ok forward tunnel, using the credentials from step 1.

Create a user and grant it the special rds_iam role. Replace mydbuser with a name that reflects the intended usage:

CREATE USER mydbuser WITH LOGIN;
GRANT rds_iam TO mydbuser;

Then grant the user the access it needs. Run one or more of the following statements, or any other grants based on your requirements.

Allow the user to access objects in a schema (this doesn't grant access to the tables themselves):

GRANT USAGE ON SCHEMA schema_name TO mydbuser;

Allow read access to one table:

GRANT SELECT ON table_name TO mydbuser;

Allow read access to all tables in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO mydbuser;

Step 3: Allow an IAM role to connect

The IAM identity that connects to the database needs the rds-db:connect permission for the database user. Create the permission with Terraform in your application's stack. Don't create IAM resources manually with the AWS CLI or the AWS console, since the Golden Path manages all infrastructure as code.

The example below lets the application's ECS task role connect as mydbuser. The locals environment, region, account_id, and main_container_name already exist in application stacks generated by the Golden Path templates:

locals {
  db_user = "mydbuser"
}

data "aws_rds_cluster" "main" {
  cluster_identifier = "${local.environment}-main"
}

data "aws_iam_policy_document" "rds_connect" {
  statement {
    effect  = "Allow"
    actions = ["rds-db:connect"]
    resources = [
      "arn:aws:rds-db:${local.region}:${local.account_id}:dbuser:${data.aws_rds_cluster.main.cluster_resource_id}/${local.db_user}"
    ]
  }
}

resource "aws_iam_policy" "rds_connect" {
  name        = "${local.environment}-${local.main_container_name}-rds-connect"
  description = "Allow rds-db:connect as DB user '${local.db_user}' on cluster '${data.aws_rds_cluster.main.cluster_identifier}'"
  policy      = data.aws_iam_policy_document.rds_connect.json
}

resource "aws_iam_role_policy_attachment" "rds_connect" {
  role       = module.ecs_service.tasks_iam_role_name
  policy_arn = aws_iam_policy.rds_connect.arn
}

The aws_rds_cluster data source looks up the cluster resource ID that the rds-db:connect ARN requires, so you don't need to find it manually.

Apply the configuration:

terraform init
terraform apply

Step 4: Verify the connection

Verify that IAM authentication works by connecting through the ok forward tunnel with a generated token instead of a password.

1. Download the certificate bundle for your region:

   curl -o eu-west-1-bundle.pem https://truststore.pki.rds.amazonaws.com/eu-west-1/eu-west-1-bundle.pem
   

   Certificate authority and region

   Golden Path clusters pin the rds-ca-rsa2048-g1 certificate authority (ca_cert_identifier in the database template), which the regional bundle includes. If your environment runs in another region, replace eu-west-1 in the URL and in the commands below.

2. Start ok forward as described in Connect to a database from your computer. Note the database endpoint you select and the local port you choose.

3. Generate a token and connect. Set RDSHOST to the endpoint you selected in ok forward, and LOCAL_PORT to the local port you chose:

   RDSHOST=my-team-dev-main-one.abc123xyz.eu-west-1.rds.amazonaws.com
   LOCAL_PORT=4812
   
   export PGPASSWORD="$(aws rds generate-db-auth-token \
     --hostname "$RDSHOST" \
     --port 5432 \
     --region eu-west-1 \
     --username mydbuser)"
   
   psql "host=$RDSHOST hostaddr=127.0.0.1 port=$LOCAL_PORT user=mydbuser dbname=postgres sslmode=verify-full sslrootcert=eu-west-1-bundle.pem"
   

Two details make this work through the tunnel:

• Generate the token for the database's real endpoint and port 5432, not for localhost or the local port. The token is a signed request tied to that endpoint, and the database rejects tokens generated for other hostnames. Tokens expire after 15 minutes.
• The host and hostaddr pair makes psql connect to the tunnel at 127.0.0.1 while validating the server certificate against the real endpoint name, so sslmode=verify-full works.

You should get a psql prompt. Confirm that you are connected as the new user:

SELECT current_user;