15

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
mYnDstrEAm
  • 751
  • 2
  • 8
  • 26
  • 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 May 01 '19 at 21:07
  • Easy step-by-step [tutorial](https://blog.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram) how to upgrade from `md5` to `scram-sha-256`. – Mateech Jun 10 '21 at 14:09

2 Answers2

21

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.

Community
  • 1
  • 1
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    Another issue as I recall is that the user must be using a connection library/driver that has added support for the [SCRAM](https://en.wikipedia.org/wiki/Salted_Challenge_Response_Authentication_Mechanism) protocol. Perhaps the OP’s *psql* supported SCRAM but not their *pgAdmin*? – Basil Bourque Nov 20 '18 at 21:25
  • 1
    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. – mYnDstrEAm Nov 21 '18 at 11:38
0

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)
ibre5041
  • 4,903
  • 1
  • 20
  • 35