RDS IAM database authentication for MySQL and PostgreSQL: setup, auth tokens and fixing login failures

The goal: applications and engineers log in to RDS or Aurora with a short-lived token signed by their IAM identity instead of a password stored in a config file. No password to rotate, access granted and revoked through IAM, and every connection tied to a role.

The setup has four parts that all have to line up: the DB setting, a database user mapped to IAM, an IAM policy with rds-db:connect, and a client that sends the token over TLS. Most failures come from one of those four being slightly wrong.

export AWS_REGION=eu-west-1
DB=orders-prod          # instance identifier, or cluster identifier for Aurora
DBUSER=app_orders

#Step 1: enable IAM authentication on the database

RDS instance:

aws rds modify-db-instance --db-instance-identifier "$DB" \
  --enable-iam-database-authentication --apply-immediately

Aurora cluster (the setting is on the cluster, not the instances):

aws rds modify-db-cluster --db-cluster-identifier "$DB" \
  --enable-iam-database-authentication --apply-immediately

Confirm:

aws rds describe-db-instances --db-instance-identifier "$DB" \
  --query 'DBInstances[0].[IAMDatabaseAuthenticationEnabled,DbiResourceId,Endpoint.Address,Endpoint.Port]' --output text

For Aurora use describe-db-clusters and read IAMDatabaseAuthenticationEnabled and DbClusterResourceId.

Tip

Enabling IAM authentication doesn't disable password logins. Existing users keep working, so you can roll it out one application at a time. Apply it in a maintenance window anyway, because --apply-immediately applies any other pending modifications at the same time.

#Step 2: create a database user mapped to IAM

The database user name is what IAM policies refer to. Connect as the master user or another admin.

#MySQL, MariaDB, Aurora MySQL

CREATE USER 'app_orders'@'%' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
ALTER USER 'app_orders'@'%' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders.* TO 'app_orders'@'%';

#PostgreSQL, Aurora PostgreSQL

CREATE USER app_orders;
GRANT rds_iam TO app_orders;
GRANT CONNECT ON DATABASE orders TO app_orders;
GRANT USAGE ON SCHEMA public TO app_orders;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_orders;

Warning

On PostgreSQL, a user with the rds_iam role can only log in with an IAM token, even if it also has a password. Granting rds_iam to an existing user that applications log in to with a password breaks those logins immediately. Create a new user, move the application, then drop the old one.

#Step 3: allow rds-db:connect in IAM

The resource ARN uses the resource ID, not the instance name. That trips up almost everyone:

arn:aws:rds-db:<region>:<account-id>:dbuser:<DbiResourceId or DbClusterResourceId>/<db-user-name>

RID=$(aws rds describe-db-instances --db-instance-identifier "$DB" \
  --query 'DBInstances[0].DbiResourceId' --output text)
ACCT=$(aws sts get-caller-identity --query Account --output text)

cat > rds-connect.json <<EOF
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": "rds-db:connect",
    "Resource": "arn:aws:rds-db:${AWS_REGION}:${ACCT}:dbuser:${RID}/${DBUSER}"
  }]
}
EOF

aws iam put-role-policy --role-name orders-api-task-role \
  --policy-name rds-connect-orders --policy-document file://rds-connect.json

For Aurora, the resource ID looks like cluster-ABCDEFGHIJKL; take it from DbClusterResourceId. Attach the policy to whatever identity the application runs as: ECS task role, EKS Pod Identity or IRSA role, Lambda execution role, or EC2 instance profile.

For engineers, put the same action in an IAM Identity Center permission set. You can use * for the resource ID segment to allow a user name on every database in the account, but keep the user name specific.

#Step 4: generate a token and connect

The token is a SigV4-signed string valid for 15 minutes. Generating it is a local signing operation: it makes no API call and succeeds even if the caller has no permissions at all. Permission problems only surface when the database checks the token.

Download the RDS CA bundle once:

curl -sS -o global-bundle.pem https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

HOST=$(aws rds describe-db-instances --db-instance-identifier "$DB" \
  --query 'DBInstances[0].Endpoint.Address' --output text)
TOKEN=$(aws rds generate-db-auth-token --hostname "$HOST" --port 5432 \
  --region "$AWS_REGION" --username "$DBUSER")

PostgreSQL:

PGPASSWORD="$TOKEN" psql "host=$HOST port=5432 dbname=orders user=$DBUSER sslmode=verify-full sslrootcert=global-bundle.pem"

MySQL:

mysql --host="$HOST" --port=3306 --user="$DBUSER" --password="$TOKEN" \
  --ssl-ca=global-bundle.pem --enable-cleartext-plugin

#In application code (Python)

Generate a fresh token for each new connection. Don't cache it in a connection string at start-up; pooled connections that reconnect after 15 minutes will fail.

import boto3, psycopg2

HOST = "orders-prod.abcdefgh1234.eu-west-1.rds.amazonaws.com"
rds = boto3.client("rds", region_name="eu-west-1")

def connect():
    token = rds.generate_db_auth_token(
        DBHostname=HOST, Port=5432, DBUsername="app_orders", Region="eu-west-1")
    return psycopg2.connect(host=HOST, port=5432, dbname="orders",
                            user="app_orders", password=token,
                            sslmode="verify-full", sslrootcert="global-bundle.pem")

An already open connection stays open after its token expires; the token is only checked at login.

#Fixing login failures

Check what the database thinks of the user:

-- MySQL
SELECT user, host, plugin FROM mysql.user WHERE user = 'app_orders';

-- PostgreSQL
SELECT r.rolname, m.rolname AS member_of
FROM pg_roles r JOIN pg_auth_members am ON am.member = r.oid
JOIN pg_roles m ON m.oid = am.roleid
WHERE r.rolname = 'app_orders';

Check the identity and its effective permission from the application host:

aws sts get-caller-identity
aws iam simulate-principal-policy \
  --policy-source-arn arn:aws:iam::111122223333:role/orders-api-task-role \
  --action-names rds-db:connect \
  --resource-arns "arn:aws:rds-db:${AWS_REGION}:${ACCT}:dbuser:${RID}/${DBUSER}" \
  --query 'EvaluationResults[].[EvalDecision]' --output text

simulate-principal-policy doesn't reproduce every policy layer (session policies, for example). Treat an allowed result as a strong hint that the problem is the token, the host or the database user, not as proof that IAM is fine.

Caution

Restoring a snapshot or creating a blue/green deployment gives the new database a different resource ID. Policies that name the old DbiResourceId stop working after the switchover or restore. Update the ARN as part of the runbook, or scope by user name with a wildcard resource ID in accounts that hold a single workload.

#RDS Proxy

If the application connects through RDS Proxy, IAM authentication between the client and the proxy is configured on the proxy itself, separately from the proxy-to-database authentication (Secrets Manager credentials or IAM, depending on your proxy's settings and version). The rds-db:connect ARN for client logins then references the proxy's resource ID (prx-...) rather than the database's. Check the proxy's auth settings before debugging the database.

#Prevent it next time

• Create IAM-only database users with names that match the application, and never grant rds_iam to users that still log in with passwords.
• Build the rds-db:connect ARN from describe-db-instances or describe-db-clusters in infrastructure code, and rebuild it after any restore or blue/green switchover.
• Generate tokens per connection in a pool's connect hook; never store a token in configuration.
• Enforce TLS with the RDS CA bundle in every client and test verify-full before the next CA rotation.
• Keep the master password in Secrets Manager for break-glass use only, and alert on its retrieval with CloudTrail.