Skip to content

Instantly share code, notes, and snippets.

@AnthonyWC
Forked from quiver/iam-policy.json
Created February 7, 2022 19:44
Show Gist options
  • Save AnthonyWC/0dc7abde23f23c21537427b180c9771f to your computer and use it in GitHub Desktop.
Save AnthonyWC/0dc7abde23f23c21537427b180c9771f to your computer and use it in GitHub Desktop.
How to connect to Amazon RDS PostgreSQL with IAM credentials

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

{
"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"
]
}
]
}
#! /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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment