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"

just to clarify, the assumed role ARN for the postgresql workaround is the role where the rds-db:connect policy was attached to?

the role where the rds-db:connect policy was attached to?

No, it's the client side role(e.g. EC2).

Thanks for the details, most importantly Step 2 on granting role. I had reached dead end with this, not knowing whats going wrong.

Can we give multiple users access via same EC2 instance using IAM authentication? We can only export single user at a time right?

Anyone managed to get it working from a lambda?

thank you, it works. I manage to get it work it in lambdas throw Serverless framework.
My problem was that I didn't defined the iam config in the serverless.js.

provider: {
    iamRoleStatements: [
      {
        Effect: "Allow",
        Action: [
            "rds-db:connect"
        ],
        Resource: [
            "arn:aws:rds-db:eu-west-2:YourAccountId:dbuser:*/*"
        ]
     }
    ],
  }

I am curious to find out if it is possible to use this authentication method with a tool such as pgadmin, it will alleviate having to provision a read only user on tons of databases for users who need a simple view of the database (Non Technical folks)

I used the same script as yours. I still get the error saying "PAM authentication failed for user "

Below are the steps I followed

1. Create Postgresql DB with IAM authentication enabled.
2. Create a role with db connect policy.
3. Connect to the DB with master username and create a user with rds_iam role inside the DB.
4. Attach the role created in 2nd step to a EC2 instance along with admin policy.
5. Run the script given above in the EC2 instance created in step 4

Ended up with this error

"$ bash iam_auth_psql.sh
psql: FATAL: PAM authentication failed for user "aarav"
FATAL: pg_hba.conf rejects connection for host "10.0.88.149", user "aarav", database "iam_poc", SSL off
"

Below is my role policy

{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"rds-db:connect"
],
"Resource": [
"arn:aws:rds-db:eu-central-1:2684xxxxxx:dbuser:db-2EXAMPLE4B5RDMCHIVAUYM/*"
]
}
]
}

I am not sure where i am going wrong. Please assist.

I also tried generating token with IAM credentials.
Still the same error

I am curious to find out if it is possible to use this authentication method with a tool such as pgadmin, it will alleviate having to provision a read only user on tons of databases for users who need a simple view of the database (Non Technical folks)

@jahedb-wa did you find a way of solving this for pgadmin?

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?