How to fix FATAL: IAM authentication failed in PostgreSQL

Check your AWS RDS, Aurora, or Google Cloud SQL console to confirm IAM authentication is enabled.

For AWS RDS/Aurora:
1. Open the AWS RDS console
2. Select your database instance
3. Click "Modify"
4. Scroll to "Database authentication"
5. Ensure "Password and IAM database authentication" is selected
6. Click "Continue" and apply changes immediately or during maintenance window

For Google Cloud SQL:
1. Open Cloud SQL in Google Cloud Console
2. Select your instance
3. Go to "Users"
4. Confirm your user is configured for Cloud SQL Auth or IAM authentication

Connect to your PostgreSQL instance using the master user credentials and run:

-- For AWS RDS/Aurora, grant the rds_iam role
CREATE USER iam_user_name;
GRANT rds_iam TO iam_user_name;

-- Verify the role was granted
SELECT * FROM pg_roles WHERE rolname = 'iam_user_name';

For existing users, simply grant the role:

ALTER USER existing_user CREATEROLE;
GRANT rds_iam TO existing_user;

This step is NOT required for Google Cloud SQL users.

Ensure your IAM user or role has permission to connect to the RDS database. Add this policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "rds-db:connect",
      "Resource": "arn:aws:rds:{region}:{account-id}:db:{db-instance-resource-id}"
    }
  ]
}

Replace:
- {region}: Your AWS region (e.g., us-east-1)
- {account-id}: Your AWS account ID
- {db-instance-resource-id}: Your database resource ID (found in RDS console)

Note: AmazonRDSFullAccess does not include rds-db:connect by default.

Generate the authentication token using the AWS CLI with the correct database endpoint:

# Set your variables
export RDSHOST="your-cluster.region.rds.amazonaws.com"
export DBPORT="5432"
export REGION="us-east-1"
export DBUSER="iam_user_name"

# Generate the token (valid for 15 minutes)
export PGPASSWORD="$(aws rds generate-db-auth-token \
  --hostname $RDSHOST \
  --port $DBPORT \
  --region $REGION \
  --username $DBUSER)"

# Verify the token was generated
echo $PGPASSWORD | head -c 50

Critical: Use the exact endpoint from the AWS RDS console (e.g., prod-db.cdracwecaau0.eu-central-1.rds.amazonaws.com), NOT:
- IP addresses
- DNS aliases or custom domains
- Short instance names

For Google Cloud SQL, obtain tokens using gcloud sql connect or the Google Cloud client libraries.

IAM authentication REQUIRES SSL. Connect with proper SSL settings:

# Download the RDS CA certificate
wget https://truststore.pem.s3.amazonaws.com/global-bundle.pem

# Connect using psql with SSL verification
psql "host=$RDSHOST \
  port=$DBPORT \
  sslmode=verify-full \
  sslrootcert=global-bundle.pem \
  dbname=postgres \
  user=$DBUSER \
  password=$PGPASSWORD"

SSL mode options (in order of security):
- verify-full: Recommended, verifies certificate and hostname
- verify-ca: Verifies certificate validity only
- require: Encrypts connection but skips verification
- disable: No encryption (FAILS for IAM authentication)

For application connections (Python, Node.js, Java), ensure your driver uses these same SSL settings.

If using AWS IAM authentication, your AWS credentials must be valid and, if using MFA, your session must be active.

# Check your assumed IAM identity
aws sts get-caller-identity

# Output should show your user/role ARN:
# {
#   "UserId": "AIDAQ...",
#   "Account": "123456789012",
#   "Arn": "arn:aws:iam::123456789012:user/your-username"
# }

If the identity is wrong, assume the correct role:

# Example: assume a specific role
export AWS_PROFILE=my-profile

# Or use assume-role directly
aws sts assume-role --role-arn arn:aws:iam::123456789012:role/your-role \
  --role-session-name my-session

If using MFA, reauthenticate with your MFA device.