How to connect to Amazon RDS PostgreSQL with IAM credentials

readme.md

Description

• Amazon RDS for PostgreSQL
• Amazon Aurora PostgreSQL

supports IAM DB authentication.

Here's how to do it.

Steps

1. Launch PostgreSQL instance with IAM auth enabled
2. Create IAM auth user with rds_iam ROLE(CREATE USER jane_doe WITH LOGIN; GRANT rds_iam to jane_doe;)
3. Add new policy for IAM access(for policy template, see iam-policy.json)
4. Request atemporary credential($ aws rds generate-db-auth-token) and use it as DB user password

IAM DB Auth command

If you're calling aws rds generate-db-auth-token API from IAM credentials, IAM auth is quite straightforward. Just pass your temp password via an environment variable(PGPASSWORD).

$ RDSHOST=xxx.yyy.us-east-1.rds.amazonaws.com
$ USERNAME=jane_doe
$ export PGPASSWORD="$( aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --username $USERNAME )"
$ psql "host=$RDSHOST dbname=$DBNAME user=$USERNAME"

But if you're calling that API from IAM role(e.g. EC2 instance profile/Lambda), you need a workaround. As of writing this, PostgreSQL does not support Role-based authentication. To cirsumvent this, you need to explicitly assume IAM role.

iam_auth_psql.sh is a simple helper script for this workaround. Just modify variables at the top of the script and run $ bash iam_auth_psql.sh.

References

• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html
• https://forums.aws.amazon.com/message.jspa?messageID=872280

iam-policy.json

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

iam_auth_psql.sh

#! /bin/bash
# helper script to connect to Amazon RDS PostgreSQL with IAM credentials
# https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html

REGION=us-east-1
AWS_ACCOUNT_ID=123456789012
ROLE=EC2WebRole
ROLE_SESSION_NAME=ROLE_SESSION_NAME
DURATION=900

RDSHOST=xxx.yyy.us-east-1.rds.amazonaws.com
USERNAME=jane_doe
DBNAME=dbname

# explicitly assume role
result="$(aws sts assume-role \
  --role-arn arn:aws:iam::$AWS_ACCOUNT_ID:role/$ROLE \
  --role-session-name $ROLE_SESSION_NAME \
  --duration-seconds $DURATION \
  --region $REGION)"

export AWS_ACCESS_KEY_ID=`echo ${result} | jq -r '.Credentials.AccessKeyId'`
export AWS_SECRET_ACCESS_KEY=`echo ${result} | jq -r '.Credentials.SecretAccessKey'`
export AWS_SESSION_TOKEN=`echo ${result} | jq -r '.Credentials.SessionToken'`

# connect to PostgreSQL via IAM DB auth
export PGPASSWORD="$( aws rds generate-db-auth-token  \
  --hostname $RDSHOST \
  --port 5432 \
  --username $USERNAME \
  --region $REGION)"

psql "host=$RDSHOST dbname=$DBNAME user=$USERNAME"