Skip to content

Instantly share code, notes, and snippets.

@quiver
Last active July 19, 2024 19:37
Show Gist options
  • Save quiver/509e1a6e6b54a0148527553502e9f55d to your computer and use it in GitHub Desktop.
Save quiver/509e1a6e6b54a0148527553502e9f55d 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"
@omikolaj
Copy link

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

@azeemakhter
Copy link

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"

@Pr60
Copy link

Pr60 commented Jul 28, 2023

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment