Amazon RDS IAM PAM Auth failed

Question

I enabled IAM Auth on my Postgresql, and my user myAWSusername has RDSFullAccess

export RDSHOST="MYRDSHOSTNAME.us-east-2.rds.amazonaws.com"
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-east-2 --username myAWSusername(not db_userx) )"
psql "host=$RDSHOST port=5432 sslmode=verify-full sslrootcert=./rds-combined-ca-bundle.pem dbname=busscanner user=db_userx"

and I get:

psql: FATAL:  PAM authentication failed for user "db_userx"

This is how created my db_userx

CREATE USER db_userx WITH LOGIN; 
GRANT rds_iam TO db_userx;

output of \du

     Role name     |                         Attributes                         |                   Member of                    
-------------------+------------------------------------------------------------+------------------------------------------------
 db_userx          |                                                            | {rds_iam}
 postgres_ro       |                                                            | {postgres_ro_group}
 postgres_ro_group | Cannot login                                               | {}
 rds_iam           | Cannot login                                               | {}
 rds_replication   | Cannot login                                               | {}
 rds_superuser     | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication}
 rdsadmin          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                   | Password valid until infinity                              | 
 rdsrepladmin      | No inheritance, Cannot login, Replication                  | {}
 read_only_user    | Password valid until infinity                              | {}

is cannot login correct for rds_iam?

This is the policy I attached to my user:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:us-east-2:MYAWSROOTACCOUNTID:dbuser:*/db_userx"
            ]
        }
    ]
}

Solution 1

you have to generate generate-db-auth-token with your db_userx from IAM policy

db-auth-token will be your PGPASSWORD

export RDSHOST="MYRDSHOSTNAME.us-east-2.rds.amazonaws.com"
export PG_USER="db_userx"
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-west-2 --username $PG_USER )"

and than:

psql "host=$RDSHOST port=5432 sslmode=verify-full sslrootcert=./rds-combined-ca-bundle.pem dbname=db_roles_test user=$PG_USER"

this is correct for db_userx

CREATE USER db_userx WITH LOGIN; 
GRANT rds_iam TO db_userx;

output of \du

                                                        List of roles
      Role name       |                   Attributes                   |                          Member of
----------------------+------------------------------------------------+--------------------------------------------------------------
 db_userx             |                                                | {rds_iam}
 pg_monitor           | Cannot login                                   | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
 pg_read_all_settings | Cannot login                                   | {}
 pg_read_all_stats    | Cannot login                                   | {}
 pg_signal_backend    | Cannot login                                   | {}
 pg_stat_scan_tables  | Cannot login                                   | {}
 rds_iam              | Cannot login                                   | {}
 rds_password         | Cannot login                                   | {}
 rds_replication      | Cannot login                                   | {}
 rds_superuser        | Cannot login                                   | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin             | Superuser, Create role, Create DB, Replication+| {}
                      | Password valid until infinity                  |
 rdsrepladmin         | No inheritance, Cannot login, Replication      | {}
 root                 | Create role, Create DB                        +| {rds_superuser}

so you can create as many users as necessary via

CREATE USER <you_user_name> WITH LOGIN;

be careful Authentication tokens have a lifespan of 15 minutes

so, after all of this, any AWS Resource with your policy will have access to RDS Db.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:us-east-2:MYAWSROOTACCOUNTID:dbuser:*/db_userx"
            ]
        }
    ]
}

Solution 2

For those of you that are still struggling with the "PAM authentication failed for user 'xxxx'", please check if your AWS account is part of an AWS Organizations organization.

If the account is part of an organisation, add rds-db:* to the service control policy of the organization unit that the account belongs to.

Also, please check to see if there is a hierarchy of the IAM user or role that doesn't have the rds-db permission.

For more info, check out these premium support AWS docs: https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-connect-using-iam/#:~:text=If%20you%20still%20receive%20an,that%20the%20account%20belongs%20to.

Comments

[tronbabylove]

Note to SQLAlchemy users: make sure you URL-encode the token returned by rds:generate-db-auth-token if you're going to use it in a connection string.