Connections & authintermediateFree in full

Rotate md5 passwords to SCRAM without locking out clients

Flipping pg_hba.conf to scram-sha-256 while roles still store md5 verifiers logs every one of them out on the same reload. Rotate the verifiers first, while the rule still says md5, then flip — and nothing drops.

Problem

What you're actually looking at

The symptom as it shows up on a real server.

Two independent things have to line up: what pg_hba.conf demands of a connecting client, and which password verifier is stored for each role. Changing password_encryption only affects passwords set after the change — it never rewrites the verifiers already in pg_authid. Flip the pg_hba.conf rule first and every role that has not been rotated fails authentication instantly.

Meridian is retiring md5 hashing on a security deadline. The new rule lands in pg_hba.conf, the server reloads, and every service whose password has not been re-set since dies at the same second with a FATAL authentication failure. The server is healthy, the network is fine, and nothing can log in.

In plain English

There are two separate settings here, and people usually only think about one. The first is what the server asks for when a client knocks on the door — that lives in pg_hba.conf. The second is the stored form of each role's password, which was fixed at the moment that password was last set. Changing the server-wide default only changes how the next password gets stored; it does not go back and rewrite the ones already there. So if you change the door policy to 'SCRAM only' while half your roles still have an old md5 password on file, those roles cannot answer the new question and are locked out the instant you reload. What saves you is that the older md5 door policy already accepts a SCRAM password. That lets you rotate everybody quietly first, with nothing breaking, and change the door last.

Before you start

  • • Superuser access, to read pg_authid and to edit and reload pg_hba.conf.
  • • A confirmed inventory of client driver versions — SCRAM needs libpq 10+, psycopg2 2.8+, or PgJDBC 42.2.0+ — because a driver that cannot speak SCRAM fails regardless of what the server stores.
  • • The ability to set each role's password — or to have its owner set it — during the rollover window.

How to identify it

  • ›Clients fail with SQLSTATE 28P01, password authentication failed, immediately after a pg_hba.conf change and reload.
  • ›Older client drivers report FATAL: unsupported authentication method: 10 — the server is asking for SASL/SCRAM and the driver only speaks md5.
  • ›pg_authid shows roles whose rolpassword still begins with md5 while pg_hba_file_rules already shows scram-sha-256.
  • ›The failure is total and instantaneous rather than gradual — every affected role breaks on the same reload, which is what distinguishes it from a wrong password.
  • ›The cluster was recently upgraded to PostgreSQL 14 or later, where the default password_encryption changed to scram-sha-256 — so passwords set since the upgrade are already SCRAM while older ones are not, leaving a mixed estate nobody audited.

Pitfalls to avoid

  • ✕Do not change the pg_hba.conf method before rotating verifiers — that single step is what causes the outage.
  • ✕Do not stack a scram-sha-256 line above an md5 line and call it dual-protocol support. pg_hba.conf has no fall-through: the first rule whose connection type, address, database and user match is the only one used, so the md5 line below it is unreachable and every un-rotated role still fails. Leave the single rule on md5 instead — it already accepts both verifier types.
  • ✕Do not assume setting password_encryption rewrites existing passwords; it applies only to passwords set after it, so every existing role keeps its md5 verifier until someone re-sets it.
  • ✕Do not rotate before auditing client driver versions — a driver with no SCRAM support fails no matter what the server stores, and that failure looks identical. SCRAM needs libpq 10 or newer, psycopg2 2.8 or newer, or PgJDBC 42.2.0 or newer.
  • ✕Do not overlook the roles that are not applications: monitoring agents, backup jobs and a pooler's own auth user hold md5 verifiers too, and they break just as hard.

Trace it

  1. 01

    Audit what each role actually stores

    This is the query the outage hinges on, and it is the one nobody runs first. The verifier's prefix tells you which protocol that role can complete: a SCRAM verifier begins with SCRAM-SHA-256$, an old one begins with md5. rolpassword is readable only by a superuser, and it is deliberately absent from pg_roles — you have to read pg_authid.

    SELECT rolname,
       CASE
         WHEN rolpassword IS NULL                THEN 'no password stored'
         WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram-sha-256'
         WHEN rolpassword LIKE 'md5%'            THEN 'md5'
         ELSE 'other'
       END AS stored_verifier
FROM   pg_authid
WHERE  rolcanlogin
ORDER  BY 2, 1;
  2. 02

    Audit what pg_hba.conf currently demands

    pg_hba_file_rules reads the rules as the server has actually parsed them, which is more trustworthy than reading the file — it reflects the last successful load and surfaces malformed lines in its error column. Compare auth_method here against the verifiers above: every row where the rule says scram-sha-256 but a matching role still stores md5 is a lockout waiting for the next reload.

    SELECT line_number, type, database, user_name, address, auth_method, error
FROM   pg_hba_file_rules
ORDER  BY line_number;
  3. 03

    Confirm what the next password will be stored as

    password_encryption governs only future ALTER ROLE ... PASSWORD statements. It has context 'user', so it can be set for a single session while you rotate, without touching the cluster default. If this still reads md5, rotating a role would re-store an md5 verifier and quietly keep the problem.

    SELECT name, setting, context FROM pg_settings WHERE name = 'password_encryption';

Resolution approach

  1. 1.Audit client driver versions first and upgrade anything too old to speak SCRAM; this is the only step that needs an application deploy.
  2. 2.Set password_encryption = 'scram-sha-256' so every password set from this point stores a SCRAM verifier.
  3. 3.Re-set each role's password while the pg_hba.conf rule still says md5. The md5 method accepts a role that stores a SCRAM verifier and upgrades that handshake automatically, so rotated roles keep working and un-rotated roles are untouched.
  4. 4.Only once the audit returns no md5 verifiers, change the rule to scram-sha-256 and reload — at that point the flip changes nothing for anyone.

Stop it recurring

  1. 01

    Rotate one role in place

    Setting password_encryption for just this session avoids changing cluster-wide behaviour mid-rollover. ALTER ROLE ... PASSWORD then re-derives and stores a SCRAM verifier for that role. The password value itself does not have to change — re-setting the same secret is enough to replace the verifier, which means no application config has to be touched.

    SET password_encryption = 'scram-sha-256';
ALTER ROLE app_reporting PASSWORD 'the-same-secret-as-before';

SELECT rolname,
       CASE WHEN rolpassword LIKE 'SCRAM-SHA-256$%' THEN 'scram-sha-256'
            WHEN rolpassword LIKE 'md5%'            THEN 'md5'
            ELSE 'other' END AS stored_verifier
FROM   pg_authid WHERE rolname = 'app_reporting';
  2. 02

    Gate the flip on the audit returning zero

    Make this the explicit go/no-go for changing pg_hba.conf, rather than a calendar date. While it returns rows, the flip is an outage; when it returns none, the flip is a formality. Keep it as a scheduled check afterwards too — a restored role, or a role created while password_encryption was still md5, can reintroduce one.

    SELECT count(*) AS roles_still_on_md5
FROM   pg_authid
WHERE  rolcanlogin
  AND  rolpassword LIKE 'md5%';

Verify you're done

SELECT count(*) AS roles_still_on_md5 FROM pg_authid WHERE rolcanlogin AND rolpassword LIKE 'md5%';  -- must be 0 before pg_hba.conf is switched to scram-sha-256
Related28P01

Related errors

SQLSTATEs this runbook resolves

The error pages that send an on-call engineer here.

More in this category

Other Connections & auth runbooks

Neighbouring incidents that share the same diagnostic surface.

Browse all 75 runbooks →

Connected

How this connects to the rest of the library

A live view of this page's real cross-references — what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.

Open in the interactive map →