psql: FATAL: PAM authentication failed for user " " aws rds Postgresql

Question

I want to connect to aws rds POSTGRESQL in dev from my own computer.

I followed all the steps on how to do it from bunch of articles: https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-connect-using-iam/

https://aws.amazon.com/blogs/database/using-iam-authentication-to-connect-with-pgadmin-amazon-aurora-postgresql-or-amazon-rds-for-postgresql/.

The problem is if I create the database in aws console interface, I am able to log in ONLY once.

psql -h database.xxxxxxxx.us-west-2.rds.amazonaws.com -U user_name -d database

Other times I try to log in with the same any other command, I get

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

First and only time I login, I create a user

CREATE USER user_name WITH LOGIN; 
GRANT rds_iam TO user_name;

All other attempts including the other steps logging with the iam token etc, I get an error:

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

If I delete the database from aws console interface and then create a brand new one, I am able to log in only ONCE and and then get the error no matter what I do.

nc Command gives me Connection succeeded at all times I run it:

 nc -zv DB-instance-endpoint port

The commands I am using :

export RDSHOST="database.xxxxxxxx.us-west-2.rds.amazonaws.com"
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-west-2 --username user_name)"

I get the error if I use the PGPASSWORD in pgAdmin window. Also, I am trying to connect from the the terminal either mine or ssh into ec2, I use this command:

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

and I still get the same error

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

or If I use another command, without the .pem certificate

psql --host=database.xxxxxxxx.us-west-2.rds.amazonaws.com --port=5432 --username=user_name --password --dbname=database

Then it asks me for a password and Then I get this error

psql: error: FATAL:  PAM authentication failed for user "user_name"
FATAL:  pg_hba.conf rejects connection for host "222.22.22.22", user "user_name", database "database", SSL off

"222.22.22.22" is My Ip, I changed it of course.

I attached all the required and all the RDS access Policies to my user and still getting this error.

I am just no sure what to do at this point as I went through every single article and cannot find a solution.

Eckblad

You have anonymized and abbreviated things to the point we can't tell what is going on. If you created "iamuser", why are you getting "user_name" in the error message? Which one did you actually try to log in as? The only psql you show us is the one where you log in as the master user, which is (apparently) not the one that is failing. You emphasize you can only log in once, but once as whom? How do you delete the database and create a new one if you can no longer log in? – Earthwork

I apologize. I corrected the question and explained it in more detail – Eckblad

Are you re-running generate-db-auth-token frequently? Each token has a very limited lifespan, you need to refresh PGPASSWORD very frequently. – Earthwork

Yes, I generate it every time I need it. It is valid for 15 min. – Eckblad

Answer 1

These answers cover more the options that could cause this

• SO: Amazon RDS IAM PAM Auth failed
• AWS docs: https://repost.aws/knowledge-center/aurora-postgresql-connect-iam

The problems related to this error are:

• Lack of permissions for rds-db:connect (ref)
  • Note also that the policy permission requires DbiResourceId and in the AWS console, the "DB Identifier" is not it, you need to check the Resource ID in the Configuration view, or better get the ID as per the doc with: aws rds describe-db-instances --query "DBInstances[*].[DBInstanceIdentifier,DbiResourceId]"
• SCP policies limiting/lacking rds-db:* permissions
• Bad creation of user_name in DB (ref)

Also it is useful to point out that all of the above may be in place, and you will still get the generate-db-auth-token CLI command to produce a "token-like" string, but it may still be wrong if the assume role process is not correct.

Ditzel

Answer 2

I had a similar problem and after some playing around with psql utility I found the reason for these errors. You shall export your temporary database password/token to shell of the machine/service etc where the connection will be initiated from.

So, if psql connection is initiated from Bastion, the below command should also be run on the same Bastion server.

export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-west-2 --username user_name)"

or generate it elsewhere and export its value as

export PGPASSWORD="temporary_token_generated_for_user_name"

With this exported $PGPASSWORD variable, it psql should connect straight away, without promting for any additional passwords

Sodalite

Answer 3

It could also be because the AWS cli credentials you are using do not have enough permissions to operate on the account where the DB lives

Petroglyph

Answer 4

I found the solution finally. So if anyone has the same issue and goes nuts about it, here is the solution:

If everything is working as I described above and the only error you get is PAM.. then:

your config file is not properly set up. It does not have the username you are trying to connect, the region, and the keys.

~/.aws/config 

[profile PROFILE_NAME]
 output=json
 region=us-west-1
 aws_access_key_id=foo
 aws_secret_access_key=bar

Here is the link to the question on how to set it up:

AWS : The config profile (MyName) could not be found

Eckblad

You said "your config file is not properly set up. It does not have the username you are trying to connect". What "username" is missing? The example ~/.aws/config file you cite has no user name of any sort. – Hillhouse

Just in case this helps anybody else, this answer indirectly HELPed me resolve my PAM error. My issue was that when I first generated the PGPASSWORD I was using one AWS profile, but when I went to test the connection/psql command I had switched profiles. Regenerating the PGPASSWORD after having switched profiles resolved the PAM error. – Rowles