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"
            ]
        }
    ]
}

Lagunas

Answer 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 then:

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"
            ]
        }
    ]
}

Annelleannemarie

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. – Cyclograph

Other things to double check are db identifier, not db resource id being used in policy and assume role process being correct, as the CLI will still generate a string without failing if the permission is wrong, see also: https://mcmap.net/q/504476/-psql-fatal-pam-authentication-failed-for-user-quot-quot-aws-rds-postgresql – Buddybuderus

Answer 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.

Zackaryzacks

Have you ever make it work with organizations? I have a 'Allow: rds-db:*, resource: [*]' policy in my ROOT organization unit and i'm still getting the PAM authentication failed for user – Aslam

SCPs can only restrict access not grant it. Just make sure that there is not an SCP preventing use of rds-db:connect. If you don't have access to your AWS Organizations account to look at the existing SCPs, you can use the IAM policy simulator (docs.aws.amazon.com/IAM/latest/UserGuide/…) to see if there is a restriction in your SCPs. – Bathsheba

Another ref: repost.aws/knowledge-center/aurora-postgresql-connect-iam – Buddybuderus

Answer 3

One way (ahem) to get the "PAM authentication failed" error is if you try to connect from EC2 and haven't attached the policy allowing "rds-db:connect" to the EC2 IAM Role. Note that you can generate a token from aws rds generate-db-auth-token without the role attached; the role is only needed when attempting to authenticate to the DB.

Also, the --username parameter for aws rds generate-db-auth-token is expecting the DB user (db_userx), not an IAM user (myAWSusername).

Grandsire

Answer 4

If you are still struggling and none of the above solution worked for you then:- If you have created a user with login password and granted that user 'rds_iam' role knowingly/unknowingly ie

create user test_user with login password 'pass2122';
grant rds_iam to test_user;

Then this is wrong as rds_iam role needs to be given to those users which will be logged in via IAM AUTHENTICATION and not password authentication. So, revoke rds_iam and you will be able to login.

Revoke rds_iam from test_user;

Incompetence

Answer 5

You need to have rds-db:connect attached to the IAM Role. And then attach the role to a EC2 instance, Lambda.

Carlson

Answer 6

In my case I had a missing account number in the policy, fixed with '*' for my sandbox environment:

{
  "Effect": "Allow",
  "Action": [
       "rds-db:connect"
   ],
   "Resource": [
       "arn:aws:rds-db:*:*:dbuser:*/*"
    ]
 }

Circumspect