How to Resolve RDS Postgres Master User Cannot Use Password After Enabling IAM

If you ever try to set up IAM auth on RDS one of the mistakes you can easily make is set up the roles incorrectly for the master user. AWS uses rds_iam role for users which want to use IAM auth. One mistake you can easily make is while testing GRANT rds_iam role to master which blocks the master user thus denying login with a password.

What happens then is you are unable to log into the database via normal password authentication. You can still generate a token and get into the db but this can be cumbersome and may not work in all situations.

aws rds generate-db-auth-token 
  --no-verify-ssl 
  --hostname <HOSTNAME> 
  --port 5432 
  --region <AWS_REGION> 
  --username <USERNAME>

Then use the token generated by above as the password to connect to your DB.

If you want to still connect using password and are locked out then follow the steps below.

First find out what do your users and roles look like.

SELECT 
  r.rolname, 
  ARRAY(
    SELECT b.rolname 
    FROM pg_catalog.pg_auth_members m 
    JOIN pg_catalog.pg_roles b 
    ON (m.roleid = b.oid) 
    WHERE m.member = r.oid
  ) memberof 
  FROM pg_catalog.pg_roles r 
  WHERE r.rolname !~ '^pg_' 
  ORDER BY 1;

You can expect to see :-

master -> rds_superuser, rds_iam

This means the master is both a superuser and also has role rds_iam granted to it. Now you need to remove this grant to fix the issue. You cannot do it while you are in the role.

So, the steps to follow are.

1. Create a lambda role with the following IAM policy to allow rds Connect.

{
    "Version": "2012-10-17",
    "Statement": [
    {
        "Effect": "Allow",
        "Action": [
            "rds-db:connect"
        ],
        "Resource": [
            "arn:aws:rds-db:<<region>>:<<account-id>>:dbuser:*/*"
        ]
    }
]}

2. Create and add a lambda layer for postgres as you will be able to easily edit the lambda code on the console instead of packaging. You can use the following resources to quickly add the required libs for psycopg2. You can also package locally if you prefer.

mkdir layer
cd layer
pip install aws-psycopg2 -t .

mkdir -p python/lib/python3.9/site-packages
mv -R . python/lib/python3.9/site-packages

zip -r psycopg2.zip python

3. Find out the current state

import psycopg2
import boto3

def connect_to_postgres(host, database, user, token, port=5432):
 try:
  connection = psycopg2.connect(
   host=host,
   database=database,
   user=user,
   password=token,
   port=port,
   sslmode='require'
  )
  cursor = connection.cursor()
  print("Connected")

  cursor.execute("SELECT r.rolname, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) memberof FROM pg_catalog.pg_roles r WHERE r.rolname !~ '^pg_' ORDER BY 1;")
    row = cursor.fetchone()
    while row is not None:
      print(row)
      row = cursor.fetchone()
  
  connection.commit()
  cursor.close()
  
  connection.close()
 except psycopg2.Error as e:
  print(f"Error: {e}")

def lambda_handler(event, context):
    host = "DB_HOST_NAME"
    database = "postgres"
    user = "master"
    port = 5432
 
    session = boto3.Session()
    client = session.client('rds')
    
    token = client.generate_db_auth_token(DBHostname=host, Port=port, DBUsername=user, Region='ap-southeast-2')
    connect_to_postgres(host, database, user, token, port)

You should see something like this. Notice roles for master that also includes rds_iam.

('master', ['rds_superuser', 'rds_iam'])
('rds_ad', [])
('rds_iam', [])
('rds_password', [])
('rds_replication', [])
('rds_superuser', ['pg_monitor', 'pg_signal_backend', 'rds_replication', 'rds_password'])
('rdsadmin', [])
('rdsproxyadmin', [])

4. Create a new user — db_user_name as members of rds_iam and superuser. Modify lambda code as below.

import psycopg2
import boto3

def connect_to_postgres(host, database, user, token, port=5432):
 try:
  connection = psycopg2.connect(
   host=host,
   database=database,
   user=user,
   password=token,
   port=port,
   sslmode='require'
  )
  cursor = connection.cursor()
  print("Connected")

  cursor.execute("CREATE USER db_user_name WITH LOGIN")
  cursor.execute("GRANT rds_iam TO db_user_name")
  cursor.execute("GRANT rds_superuser TO db_user_name")

  cursor.execute("SELECT r.rolname, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) memberof FROM pg_catalog.pg_roles r WHERE r.rolname !~ '^pg_' ORDER BY 1;")
    row = cursor.fetchone()
    while row is not None:
      print(row)
      row = cursor.fetchone()
  
  connection.commit()
  cursor.close()
  
  connection.close()
 except psycopg2.Error as e:
  print(f"Error: {e}")


def lambda_handler(event, context):
    host = "DB_HOST_NAME"
    database = "postgres"
    user = "master"
    port = 5432
 
    session = boto3.Session()
    client = session.client('rds')
    
    token = client.generate_db_auth_token(DBHostname=host, Port=port, DBUsername=user, Region='ap-southeast-2')
    connect_to_postgres(host, database, user, token, port)

It will show you a resulting USERS with their roles such as below

('master', ['rds_superuser', 'rds_iam'])
('db_user_name', ['rds_superuser', 'rds_iam'])
('rds_ad', [])
('rds_iam', [])
('rds_password', [])
('rds_replication', [])
('rds_superuser', ['pg_monitor', 'pg_signal_backend', 'rds_replication', 'rds_password'])
('rdsadmin', [])
('rdsproxyadmin', [])

5. Remove the master from rds_iam by using the new login. Modify Lambda code.

import psycopg2
import boto3

def connect_to_postgres(host, database, user, token, port=5432):
 try:
  connection = psycopg2.connect(
   host=host,
   database=database,
   user=user,
   password=token,
   port=port,
   sslmode='require'
  )
  cursor = connection.cursor()
  print("Connected")

  cursor.execute("REVOKE rds_iam FROM master")
  
  connection.commit()
  cursor.close()
  
  connection.close()
 except psycopg2.Error as e:
  print(f"Error: {e}")

def lambda_handler(event, context):
    host = "DB_HOST_NAME"
    database = "postgres"
    user = "db_user_name"  ## NOTE: WE ARE USING THE NEW LOGIN 
    port = 5432
 
    session = boto3.Session()
    client = session.client('rds')
    
    token = client.generate_db_auth_token(DBHostname=host, Port=port, DBUsername=user, Region='ap-southeast-2')
    connect_to_postgres(host, database, user, token, port)

Should show you a result such as below

('master', ['rds_superuser'])
('db_user_name', ['rds_superuser', 'rds_iam'])
('rds_ad', [])
('rds_iam', [])
('rds_password', [])
('rds_replication', [])
('rds_superuser', ['pg_monitor', 'pg_signal_backend', 'rds_replication', 'rds_password'])
('rdsadmin', [])
('rdsproxyadmin', [])

6. Now you should be able to login with Master/password via your db client.

7. Cleanup

• Remove the Lambda
• Remove the role and policy for the lambda
• Remove the db_user_name from the users in the db