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.

Blockbusting

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. – Audy

Easy step-by-step tutorial how to upgrade from md5 to scram-sha-256. – Subaxillary

Answer 1

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.

Popliteal

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? – Comedietta

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. – Blockbusting

Answer 2

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)

Suzannesuzerain