IAM based authentication with Aurora Postgres SQL

This article provides step by step instruction on how to enable IAM authentication and connect to RDS using the IAM role. This tutorial can be applied to Aurora serverless Postgres as well.

During or after creating the database, enable IAM authentication for the database. This can be verified in the console as shown below

Connect to the RDS database using master username and password. Even the IAM master user can connect with IAM and password can be totally eliminated. To simplify and ease development effort without sacrificing security we can start to use the username password combination for admin. With the current db session create a development user that will be using IAM authentication. In the below example, user is created with name postgres_developer_role.

SQL COMMANDS

CREATE USER "postgres_developer_role"
GRANT rds_iam TO "postgres_developer_role";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO " postgres_developer_role";

Create an IAM role that is be used and associated with this database user. In the below example replace account number 1234567890 with a valid value. This assume role policy is left open to any entity in the account. Based on the use case necessary conditions can added to restrict the principals that can assume the role.

    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "s1",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::1234567890:root"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Next attach the below permission policy

{
    "Statement": [
        {
            "Action": [
                "rds-db:connect"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:rds-db:<REGION>:<ACCOUNT_NUM>:dbuser:*/ postgres_developer_role"
            ],
            "Sid": "AllowRDSConnect"
        }
    ],
    "Version": "2012-10-17"
}

Next step is to assume the above role

aws sts assume-role --role-arn "arn:aws:iam::<ACCOUNT_NUM>:role/postgres-developer-role" --role-session-name "dbsession" >out.json

Create a shell script with below code that reads the json parses the value and export them.

#!/bin/bash
#Read content of file passed as argument to variable json
json=$(cat "$1")

# Extract values from JSON using jq
access_key_id=$(echo "$json" | jq -r '.Credentials.AccessKeyId')
secret_access_key=$(echo "$json" | jq -r '.Credentials.SecretAccessKey')
session_token=$(echo "$json" | jq -r '.Credentials.SessionToken')

# Print the exported variables
echo "Exported environment variables:"
echo "export AWS_ACCESS_KEY_ID=$access_key_id"
echo "export AWS_SECRET_ACCESS_KEY=$secret_access_key"
echo "export AWS_SESSION_TOKEN=$session_token"

Calling the shell script will print the export commands which can be executed in user’s shell environment. Alternatively the permission policy can be added to the Lambda role or EC2 role. With the policy directly attached to EC2 the steps to export credentials to env variables can be skipped.

Run the rds generate token aws cli command to fetch the token.

aws rds generate-db-auth-token \
    --hostname <HOSTNAME> \
    --port <PORT> \
    --region <REGION> \
    --username postgres_developer_role

The output of above command needs to be used as password in the RDS connection string. For example, if psql client is available in a terminal window execute below command.

psql -h <HOSTMAME> -p <PORT> -d <DATABASENAME> -U postgres_developer_role sslmode=verify-full sslrootcert=global_bundle.pem

Note that the above command uses sslmode as verify full and hence the cert has to be passed. The pem file can be downloaded from AWS url below. https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

Sample errors and resolution

Here's the guidance on how to solve the issues:

1. PAM authentication failed for user "user_role" The issue can be solved by changing the IAM policy to have the correct db resource. An example IAM policy Arn is arn:aws:rds-db:${region}:${account_id}:dbuser:*/user_role. Ensure that the keyword "dbuser" is present, and the role name at the end matches the db user name.

2. FATAL: database "user_database" does not exist The issue can be solved by specifying the correct database name in the -d flag while connecting to the PostgreSQL database using the psql command. The psql command requires the database name to be passed during the connection. Without this parameter, it assumes there is a database with the name of the user used for the connection and fails.

3. FATAL: password authentication failed for user "user" The issue can be solved by providing the correct password for the user "user" while connecting to the PostgreSQL database using the psql command. Note that the DB temp token expires in 15 minutes.

4. FATAL: no pg_hba.conf entry for host "IP_ADDRESS", user "user", database "database_name", no encryption The issue can be solved by adding the SSL parameters like sslmode=verify-full sslrootcert=/path/to/certificate.pem while connecting to the PostgreSQL database.