AWS RDS: FATAL: password authentication failed for user "postgres"

Question

I have Postgres RDS instance which was available and working 7 days ago. Today I noticed in my app log, which is hosted in an EC2 instance that connectivity fails with

FATAL: password authentication failed for user "postgres". 

Same when I try to connect from my local machine (was just fine 1 week ago). How come my master user is not able to login anymore? What could might have happened? Is it blocked somehow, expired or stucked? What I tried so far is rebooting the RDS instance and executed the automatic RDS troubleshooting, no success. Any hint would be appreciated.

Spare

Answer 1

This error would only occur for one reason which is invalid authentication information. Perform the following checks:

• Check the username is correct (is postgres the username you specified when creating the RDS DB, you can validate this in the console)
• Check the password is correct

Assuming both of these values you specified you believe to be correct, you should reset the password from the RDS management console.

Regarding rotation of the password, outside of an IAM user/IAM role modifying the password (which could be validated via CloudTrail). This could be programmatic or via the console.

Can you also ensure you have not setup password rotation via Secrets Manager.

Minyan

Nope, I dont have password rotation enabled. The username is correct, the password was correct couple of days ago either. I have no idea how come it gets invalid without any intervention from my side. How can I reset it? What else can be done? – Spare

It can be reset via the console: aws.amazon.com/premiumsupport/knowledge-center/… – Minyan

Regarding auditing validate your CloudTrail logs for RDS to see if you can see any malicious action. – Minyan

Answer 2

This happened to me on RDS postgres as well and the only way I could get it to work was by using a pgpass file

Obstetric

Answer 3

If you Copy+Paste your HOST/URL/PASSWORD from AWS secret manager make sure that there is no 'New Line' chacrcter at the end of the string (try to paste it in some text editor to be sure).

That was the problem for me.

Janeenjanek

Answer 4

FATAL: password authentication failed for user "postgres" can happen when either password is not correct, but, also when database user name is not correct. The name of the database can be set differently from the database user name(in AWS called: master username).

So, if you have chosen a different name when creating the RDS DB in this form:

..then you have to pick a proper name which you can find in RDS Configuration (or validate this in the console):

Many times people set the DB name as 'postgres', but sometimes they pick a different master username which is wise because in the future it is easier to manage a situation where you have two different db with two master usernames, one for each. In that case, the DB name should also be chosen wisely.

Metabolize

Answer 5

When setting up an RDS instance for testing from my personal computer, I encountered two issues:

Making the RDS Public: You might expect that explicitly configuring your RDS to be public would automatically make it available on the public subnet. However, it does not. I had to manually configure a route table and include a route 0.0.0.0/0. Once I did that, it worked fine for testing.

User and Database Configuration: While configuring the RDS, I created a user and password for testing purposes. However, when trying to connect through DBeaver, I received a "Fatal ERROR: database 'username' does not exist" message. This issue arises because PostgreSQL creates a default database called 'postgres' and attempts to find a database with the same name as the user, even if you leave it blank.

If you use 'postgres' as the username, you can leave the 'Database:' field blank, but you'll encounter the error: "FATAL: password authentication failed for user 'postgres'."

Solution: In DBeaver, specify 'postgres' in the database field, along with your defined username and password, to resolve this issue. This is how it worked for me

Endocardial

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Hanan