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.

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.

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)