psql: FATAL: password authentication failed for user "postgres" postgresql version 10

Question

I recently installed Postgre version 10 on a linux red hat. I'm trying to configure that psql will prompt a user for password when accessing the database. After changing everything to scram-sha-256. I'm getting this error when accessing psql

psql: FATAL:  password authentication failed for user "postgres"

This is my pg_hba.conf:

Any idea how can I fix this? Thanks!

Answer 1

I know that something that solved to me was in setting a new password (in CMD) when it was necessary to give a password for psql to be recognized:

 set PGPASSWORD= #type here the password you want to set

After setting, you write the following (for example):

psql -h 188.81.81.92 -U postgres -d postgres -p 5432

where -h stands for host, -U for user, -d for the database root, -p for port, using the postgreSQL language.

In windows, it could happen to outcome a problem: error: connection to server (...), which can be solved by typing:

C:\\Program Files\\PostgreSQL\\14\\bin\\psql.exe

this is, you open the file psql.exe and it will work fine.

Answer 2

Postgres store password md5 format as default. If you want to change password encryption you have to follow bellow solution:

P.S: Before start, You have to undo pg_hba.conf file authenticate method to md5

1. Edit postgresql.conf and change password_encryption to

password_encryption = scram-sha-256

2. Restart Postgres service (or reload service)

3. reset the user password

# if use psql cli
\password <user>

# If use SQL command
alter user <user> with password '<password>';

4. After updating all passwords you should change pg_hba.conf authenticate method to scram-sha-256 and reset service again

---

Reference: Information about upgrade postgres password authenicate