How to use scram-sha-256 in Postgres 10 in Debian? Getting "FATAL: password authentication failed"

Question

I edited pg_hba.conf:

sudo su postgres
nano /etc/postgresql/10/main/pg_hba.conf

and added this line:

local   all             username                               scram-sha-256

and changed all md5 to scram-sha-256 in that file.

As the postgres user, I created a new user with superuser rights:

sudo su postgres
psql

CREATE USER username WITH SUPERUSER PASSWORD 'password';

Then I restarted Postgres:

/etc/init.d/postgresql restart

and tried to login with pgAdmin4 where I changed the username under the database's Connection properties. But neither that nor psql -U username testdb < ./testdb.sql work as I'm getting:

FATAL: password authentication failed for user "username"

So how can I get Postgres working with scram-sha-256 on my Debian9/KDE machine? It worked earlier when I left all the md5 in pg_hba.conf as they were.

Or if the logging has been configured, you will see in the log: DETAIL: User "foouser" does not have a valid SCRAM verifier. Indeed, it makes sense that PostgreSQL should be configured to actually store those hashes in the correct format. — David Tonhofer
– David Tonhofer, Commented May 1, 2019 at 21:07
Easy step-by-step tutorial how to upgrade from md5 to scram-sha-256. — Mateech
– Mateech, Commented Jun 10, 2021 at 14:09

Community · Accepted Answer · 2021-02-06 00:45:00Z

22

The fine manual says:

To upgrade an existing installation from md5 to scram-sha-256, after having ensured that all client libraries in use are new enough to support SCRAM, set password_encryption = 'scram-sha-256' in postgresql.conf, make all users set new passwords, and change the authentication method specifications in pg_hba.conf to scram-sha-256.

edited Feb 6, 2021 at 0:45

CommunityBot

11 silver badge

answered Nov 20, 2018 at 21:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Basil Bourque Over a year ago

Another issue as I recall is that the user must be using a connection library/driver that has added support for the SCRAM protocol. Perhaps the OP’s psql supported SCRAM but not their pgAdmin?

mYnDstrEAm Over a year ago

It worked after setting 'scram-sha-256' in postgresql.conf, restarting postgresql and then ALTERing the user. I didn't set it in that config file as it wasn't really an "upgrade" from MD5 to SCRAM but a new installation and the password_encryption line was commented out. I also got that same error when ALTERing the user before restarting postgresql. It's pretty clear indeed; but maybe it would be a good idea to move that up to the "scram-sha-256" section as that's where I'd expect any info on that encryption method to be located.

ibre5041 · Accepted Answer · 2022-08-08 14:37:11Z

4

Also check current password hash format:

postgres=# select passwd from pg_shadow where usename='username';
passwd
--------------
md5...

postgres=# set password_encryption = 'scram-sha-256';
SET
postgres=# alter user username with password 'secretpass';
ALTER ROLE
postgres=# select passwd from pg_shadow where usename='username';
passwd
--------------------------
SCRAM-SHA-256$...
(1 row)

answered Aug 8, 2022 at 14:37

ibre5041

5,3261 gold badge24 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to use scram-sha-256 in Postgres 10 in Debian? Getting "FATAL: password authentication failed"

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related