PostgreSQL: password authentication failed for user "postgres"

In our development environment we use postgresql for our database engine. We just create a new database, and our developer requested that they want have new role. The new role will have SELECT access into tables that they requested before.
But something happen when our developer trying to run sql command like below:
# select * from table_name;
ERROR:  could not connect to server "server_master"
DETAIL:  FATAL:  password authentication failed for user "postgres"

In this situation we have to check one by one what's the password of user postgres, and we found the password is different. To check current password of user mappings, you can type \deu+ on psql console.
In this case we will change the password of user mappings. To change the password, assuming you are on the right database, and run this on psql console:
# ALTER USER MAPPING FOR postgres SERVER server_master OPTIONS (SET password 'YOUR_NEW_PASSWORD');

Then check it again the user mappings with \deu+.
Then try it once again.

Cool, we have no error found anymore!
Reference: https://www.postgresql.org/docs/10/sql-alterusermapping.html

Leave a Reply

Please leave a comment and do not give a spam! Comments that smells of spam will be deleted without prior notice