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!

Pooya · Accepted Answer · 2022-01-18 08:29:19Z

10

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

Edit postgresql.conf and change password_encryption to

password_encryption = scram-sha-256

Restart Postgres service (or reload service)
reset the user password

# if use psql cli
\password <user>

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

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

edited Jan 18, 2022 at 8:29

answered Jan 18, 2022 at 5:30

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

roboragi Over a year ago

Thanks @Pooya, I've been trying some solutions online but yours work. Thanks!

Laurenz Albe Over a year ago

For step 2 a reload is sufficient.

Pooya Over a year ago

Yeah, I edited my comment, I am appreciative for tip

Tsar Bomba Over a year ago

@Pooya No luck here. I'm running pg in a Docker, so I'm passing the password in via env var: "POSTGRES_PASSWORD=mypassword". Do I also need to go into the container instance and alter the password, every time I start it up? I haven't tried that yet, but man...this scram stuff has been a headache.

Pooya Over a year ago

@TsarBomba According to the Postgres image, You can use POSTGRES_HOST_AUTH_METHOD environment for set authenticate method in the Postgres (When init container on first time - without exist postgres filesystem)

João Víctor Melo · Accepted Answer · 2022-09-05 11:21:51Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related