CODEWITHSUNDEEP
postgresqlamazon-rdsamazon-iam
EralpB
EralpB

Reputation: 1742

Amazon RDS IAM PAM Auth failed

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

Upvotes: 29

Views: 38052

Answers (6)

ddorsogna
ddorsogna

Reputation: 302

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:*/*"
    ]
 }

Upvotes: -1

qwertmax
qwertmax

Reputation: 3450

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

Upvotes: 20

Rajender Passi
Rajender Passi

Reputation: 21

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;

Upvotes: 2

Terence Fernandes
Terence Fernandes

Reputation: 19

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

Upvotes: 0

rrey
rrey

Reputation: 106

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

Upvotes: 3

Zakes Manyoni
Zakes Manyoni

Reputation: 317

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.

Upvotes: 9

Related Questions