I inherited a cluster that had been running since PostgreSQL 9.5: forty-six roles in pg_authid, every pg_hba.conf line set to md5, and password_encryption left at its default of md5. The security review said "move to SCRAM" and the ticket looked like a one-line change, so on a Saturday we set password_encryption = scram-sha-256 and swapped every md5 method in pg_hba.conf to scram-sha-256 in the same deploy. Nothing broke on Saturday, because nothing was logging in. Monday at 07:20 the batch jobs started failing authentication, and by 07:45 we knew why: forty-one of the forty-six roles still had md5 hashes stored, and a scram-sha-256 hba line cannot verify an md5 secret — there is no SCRAM verifier to check the proof against. Every one of those users stayed locked out until somebody reset their password by hand, which took until Wednesday because half the passwords lived in a retired colleague's password manager. The migration itself is easy. We ran it backwards.
What follows is the version that works: why md5 had to go, what SCRAM actually buys you, the ordering that keeps every session alive, and the audit query that tells you when you are done.
Why is md5 password storage actually broken?
Because the stored verifier is fast to brute force and, worse, password-equivalent on its own. PostgreSQL's md5 format stores md5(md5(password || username) || salt), where the salt is a four-byte random value from the login challenge and the inner "salt" is the role name. Two problems fall out of that design. First, speed: MD5 was built for throughput, and a mid-range GPU chews through billions of candidate hashes per second, so any dump of pg_authid becomes an offline cracking exercise measured in hours for human-chosen passwords. Second, replay: the value the client needs to answer the server's challenge is exactly the stored md5(password || username) — whoever reads pg_shadow once owns a working credential forever, no cracking required. Security people call that pass-the-hash, and md5 gives it to you for free. The username-as-salt choice has one more practical side effect worth knowing before you migrate: because the role name is baked into the hash, renaming a role clears an md5-encrypted password — the documentation says so plainly, and I have watched a rename silently log an application out.
How does SCRAM-SHA-256 fix what md5 got wrong?
It replaces the fast hash with a salted, iterated one and replaces the password-equivalent verifier with one that cannot be replayed. PostgreSQL has supported SCRAM-SHA-256 — the Salted Challenge Response Authentication Mechanism from RFC 7677 — since version 10. The password is stretched through 4096 iterations of PBKDF2-style hashing with a proper random salt, so each guess costs real CPU instead of a rounding error. The server stores a StoredKey, a ServerKey, the salt, and the iteration count. The StoredKey can verify a client's proof but cannot be used to produce one, so a leaked pg_authid row is no longer a working credential — pass-the-hash dies here. The exchange is also mutual: the client checks the server's ServerSignature, which proves the server knows the ServerKey, so a spoofed server gets caught. On top of that sits channel binding: scram-sha-256-plus cryptographically ties the authentication to the TLS session via tls-server-end-point, which defeats man-in-the-middle relays, at the price of requiring SSL and drivers that support it. The honest cost sheet: 4096 iterations is noticeable CPU when you open thousands of fresh connections per second, which is one more argument for a pooler, and every client in your estate needs a SCRAM-capable driver before the final flip.
What migration order keeps every user logged in?
Rotate every password while pg_hba.conf still says md5, and only flip the hba method after the audit shows zero md5 secrets left. The trick that makes this safe is a documented transition aid: when the hba method is md5 but the stored secret is a SCRAM verifier, PostgreSQL automatically runs SCRAM verification instead. So a role whose password has been re-hashed to SCRAM keeps logging in through the old md5 line, transparently, with no client changes. That inverts what we did on that Saturday. The sequence is:
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
-- repeat for every role; this re-hashes using the new setting
ALTER ROLE reporting_read PASSWORD 'new-cleartext-password';
ALTER ROLE etl_writer PASSWORD 'new-cleartext-password';password_encryption takes effect on reload — no restart — and it only shapes how newly set passwords are stored, which is the point: ALTER ROLE ... PASSWORD re-hashes the cleartext you hand it using the current setting. That is also the awkward truth of step two. Re-hashing requires cleartext, and Postgres cannot convert an existing md5 hash into a SCRAM verifier — the md5 hash is all it has, and the password is not recoverable from it. Somebody has to know or generate each password. Application roles are easy for us and we rotate them with the secret manager; human roles mean a password reset email or a psql session with \password. Budget the calendar time for chasing forty-one humans, because that, not the SQL, is the long pole.
How do you find the roles still holding md5 hashes?
Read the verifier format straight out of the catalog — pg_authid.rolpassword (or pg_shadow.passwd if you prefer the view) carries a prefix that names the algorithm, so the audit is one WHERE clause:
SELECT rolname,
CASE
WHEN rolpassword IS NULL THEN 'no password'
WHEN rolpassword LIKE 'SCRAM-SHA-256%' THEN 'scram'
WHEN rolpassword LIKE 'md5%' THEN 'md5'
ELSE 'other'
END AS storage
FROM pg_authid
ORDER BY storage, rolname;md5 secrets start with the literal md5, SCRAM verifiers start with SCRAM-SHA-256$ followed by the iteration count, salt, and the two keys. Run it before you start, run it after every rotation batch, and treat a non-zero md5 count as the blocker for the hba flip — that count, not a date on a calendar, is your go criterion. Two edge cases to read correctly: a NULL rolpassword is a role that cannot log in by password at all, which is fine if intentional and a finding if not, and roles with LOGIN and no password are worth a second look on any inherited cluster. This audit is the kind of check that belongs in the recurring production health check rather than a one-off migration script — secret formats drift back when someone provisions a role with an old playbook.
What changes in pg_hba.conf, and what breaks on the driver side?
pg_hba.conf matching is first-match-wins, top to bottom, so the flip is a mechanical edit of the method column — md5 becomes scram-sha-256 on every password line — followed by SELECT pg_reload_conf(). Because you rotated first, no stored md5 secrets remain, and no client notices anything except that the wire exchange changed. Keep the ordering discipline you already have: narrow lines above broad ones, and resist the temptation to leave a catch-all md5 line at the bottom "just in case", because that is exactly how one forgotten role drags the whole cluster's posture back. If you want channel binding, that is a second step, not part of the flip: switch to hostssl lines, require SSL, and only then consider scram-sha-256-plus with clients on channel_binding=require — test it against your oldest driver first. On drivers: anything linked against libpq 10 or later speaks SCRAM, which covers psql, psycopg2, and most C-based clients on any recent system; the mainstream Java, .NET, Node, and Go drivers have all implemented it natively for years. The ones that bite are ancient: an ETL tool frozen on a 2016 JDBC jar, a reporting appliance nobody owns. Find them by checking each client's driver version during the rotation window, while failure is still impossible — after the flip, the same survey is an outage.
Watching the migration with MonPG
Every phase of this is observable, and we instrumented the second migration the way we should have instrumented the first. Failed authentication attempts show up in the log with the role name and client address — after the hba flip, a spike in FATAL: password authentication failed is your five-minute warning that some client or role got missed. Connection rates and open session counts tell you whether rotation broke anything subtle, like a pooler holding stale credentials. And the pg_authid audit query is a one-line scheduled check: alert when the md5 count is non-zero and the migration supervises itself. MonPG graphs connection activity, per-database session counts, and log-derived error rates as part of its PostgreSQL monitoring, which is how the second run of this migration — forty-six roles, zero lockouts, done in an afternoon of SQL plus a week of calendar time for the humans — stayed boring. Boring is the entire goal. The only exciting SCRAM migration is the one you run backwards.