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"

It is mandatory to use SSL when connecting to RDS using IAM role. Try modifying the last line of the iam_auth_psql.sh to psql "host=$RDSHOST dbname=$DBNAME user=$USERNAME sslmode=require" and see if that works.

@JimFawkes I managed to get it working by creating a profile and assumed this user when creating the db auth token:

Create AWS Profile
aws configure --profile dbuser
AWS Access Key ID [None]: xxxxxxxxxxxxx
AWS Secret Access Key [None]: xxxxxxxxxxxxxxxxxx
Default region name [None]: eu-west-1
Default output format [None]: json

aws --profile dbuser rds generate-db-auth-token --hostname RDS.Enpoint.amazonaws.com --port 5432 --region us-east-1 --username "Postgres Privisioned User"

Using this token as password in PGADmin authenticates and connects to the instance

Does the solution work from your local system outside of aws? I am able to get working from ec2 but wont accept token when done same from my laptop.

Hi,

Yes it does, we have a ipsec tunnel running nto AWS and I have it set up from my local system running PGAdmin 4 authenticating using iam auth... Have also successfully implemented using IAM roles and AWSCLIv2 with SSO

I'm able to connect to my database without specifying the policy for the user. Not sure why its working. It probably shouldn't

Hi!
I'm trying to connect with my Postgres from my DRF app using IAM role-based authentication and am stuck at PAM authentication failed, Can anyone please suggest a workaround for this?
I'm using django_iam_dbauth package and the followings are my Django app configurations:

DATABASES = {
    "default": {
        "ENGINE": 'django_iam_dbauth.aws.postgresql',
        'HOST': config('DB_HOST'),
        'NAME': config('DB_NAME'),
        'USER': config('DB_USER'),
        'OPTIONS': {
            'use_iam_auth': True,
            'sslmode': 'require',
            'region_name': REGION_NAME
        },
    }
}

This is the error message I'm getting:
django.db.utils.OperationalError: FATAL: PAM authentication failed for user "myusername"

anyone was able to get this or something similar working for the SSO USER? because in my case since I've an sso account I don't have IAM users. How can I go around this?