Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Password authentication failed right after switching pg_hba.conf from md5 to scram-sha-256

ErrorFATAL: password authentication failed for user "appuser"

Short answer

A password stored as an MD5 hash cannot be used with the scram-sha-256 method. Changing pg_hba.conf alone is not enough: set password_encryption to scram-sha-256, then set every user's password again so a SCRAM hash is stored. Old client libraries that lack SCRAM support fail the same way.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Connect as a superuser (the postgres user over the local socket still works if its rule was not changed) and list the stored hash type of each role next to the active setting.

    SHOW password_encryption;
    SELECT rolname,
           CASE WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram'
                WHEN rolpassword LIKE 'md5%' THEN 'md5'
                ELSE 'none' END AS stored_hash
    FROM pg_authid
    WHERE rolcanlogin;
  2. 2Match what you see, then jump to its fix

    1. 1stored_hash shows md5 for the role that cannot log inThe role still has an MD5 hash storedFix
    2. 2SHOW password_encryption returns md5, and a password you just reset is still stored as md5password_encryption is still md5, so the new password is hashed as md5 againFix
    3. 3Only one application fails, and psql from the server itself logs in with the same passwordThe client library is too old for SCRAMFix

What does the error look like?

  • FATAL: password authentication failed for user "appuser"
  • pg_hba.conf line changed from md5 to scram-sha-256, and every login with a password now fails

Cause 1 of 3

The role still has an MD5 hash stored

1Check

A password hash is created when the password is set, not when pg_hba.conf is read. A password stored as MD5 works only with the md5 and password methods. With the scram-sha-256 method it cannot succeed.

SELECT rolname, left(rolpassword, 3) AS prefix FROM pg_authid WHERE rolname = 'appuser';

2Fix

Make sure password_encryption is scram-sha-256, then set the password again. \password asks for the new password and sends it already encrypted, so it stays out of history and logs.

-- in postgresql.conf: password_encryption = 'scram-sha-256'
SELECT pg_reload_conf();
\password appuser

3Verify

The role now stores a SCRAM hash, and a TCP login with the password works.

SELECT left(rolpassword, 14) FROM pg_authid WHERE rolname = 'appuser';
psql "host=127.0.0.1 dbname=postgres user=appuser" -c "select 1"

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

password_encryption is still md5, so the new password is hashed as md5 again

1Check

The hash type is chosen by password_encryption at the moment the password is set. Resetting a password with the old setting gives another MD5 hash.

SHOW password_encryption;

2Fix

Set password_encryption = 'scram-sha-256' in postgresql.conf, reload, and only then re-set the passwords. Do it in this order: setting, passwords, then pg_hba.conf.

SELECT pg_reload_conf();
SHOW password_encryption;

3Verify

Re-set one password and check its hash prefix as in the first step.

SELECT rolname, left(rolpassword, 14) FROM pg_authid WHERE rolname = 'appuser';

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The client library is too old for SCRAM

1Check

The documentation says scram-sha-256 is not supported by older client libraries. Drivers and libpq builds from before SCRAM support cannot complete this login.

psql --version

2Fix

Upgrade the driver or libpq the application uses. Until every client is upgraded, keep the md5 method in pg_hba.conf: with md5 in the rule and a SCRAM hash stored, the server switches to SCRAM on its own, and with an MD5 hash stored it keeps using MD5.

# pg_hba.conf, until all clients are upgraded
host  all  all  10.0.0.0/8  md5

3Verify

Reload the rules and log in from the application host.

SELECT pg_reload_conf();

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Follow the documented order: password_encryption first, new passwords second, pg_hba.conf last.
  • Keep one superuser session open while you edit pg_hba.conf, so a bad rule never locks you out.
  • Run SELECT * FROM pg_hba_file_rules after editing, to catch syntax errors before the reload.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.