SCRAM, password storage, and the MD5 deprecation · PostgreSQL

PostgreSQL does not store passwords. It stores a verifier: a value derived from the password that lets the server check a login attempt without holding the original. Two formats exist, and the operational difference between them is larger than “one is older”.

Reading a stored verifier

Verifiers live in pg_authid, which only superusers may read. The first characters identify the algorithm.

Read-only / Safethe two verifier formats, side by side— Captured from a live 18.6 cluster. Values truncated deliberately.

$ psql -U postgres -c "SELECT rolname, left(rolpassword,10) || '...' AS stored FROM pg_authid WHERE rolname IN ('demo_scram','demo_md5') ORDER BY rolname"

  rolname   |    stored
------------+---------------
demo_md5   | md535075f1...
demo_scram | SCRAM-SHA-...

-- in full, the SCRAM verifier begins:
SCRAM-SHA-256$4096:eE7...

An md5 prefix means the rest is md5(password || rolname). A SCRAM-SHA-256$ prefix is followed by the iteration count — 4096 — then a salt and two derived keys.

The default for new passwords is SCRAM on every supported version:

Read-only / Safethe default that decides which format is written— password_encryption is user context, so a session can change what its own ALTER ROLE writes.

$ psql -U postgres -tAc 'SHOW password_encryption'

scram-sha-256

Why the difference matters operationally

An MD5 verifier is a usable credential. The MD5 authentication exchange has the client send a value derived from the stored hash rather than from the password, so an attacker who reads pg_authid can authenticate without ever knowing the password. The hash is the credential. It is also unsalted beyond the role name, so identical passwords under the same role name produce identical hashes, and the whole space is amenable to precomputation.

A SCRAM verifier is not. SCRAM is a challenge-response exchange in which the client proves it knows the password without transmitting anything the server could replay elsewhere, and the stored verifier is salted with a per-role random value and iterated 4096 times. Reading pg_authid yields something an attacker must attack offline rather than something they can present.

That distinction changes the blast radius of a backup. A logical dump taken with pg_dumpall includes role definitions and their verifiers. With MD5, that file contains working credentials for every role. With SCRAM, it contains material that must be cracked first.

The PostgreSQL 18 deprecation

PostgreSQL 18 warns when a password is stored with MD5, and states that support will be removed.

Configuration changethe deprecation warning, captured verbatim— Emitted by CREATE ROLE and ALTER ROLE when password_encryption is md5. New in PostgreSQL 18.

$ psql -U postgres -c "SET password_encryption='md5'; CREATE ROLE demo_md5 LOGIN PASSWORD '...'"

WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future
       release of PostgreSQL.
HINT:  Refer to the PostgreSQL documentation for details about migrating to
     another password type.

The warning is controlled by md5_password_warnings, which defaults to on. Turning it off is available and is the wrong response: the warning is the estate’s inventory mechanism, and silencing it removes the signal that tells you which roles still need migrating.

Migrating an estate to SCRAM

The migration is not a switch, because a stored MD5 verifier cannot be converted — the original password is not recoverable from it. Every affected role must have its password set again.

The sequence that avoids a lockout:

# 1. Inventory. Which roles still have an MD5 verifier?
psql -U postgres -c \
  "SELECT rolname, rolcanlogin,
          CASE WHEN rolpassword LIKE 'SCRAM-SHA-256%' THEN 'scram'
               WHEN rolpassword LIKE 'md5%'           THEN 'md5'
               WHEN rolpassword IS NULL               THEN 'none'
               ELSE 'other' END AS verifier
     FROM pg_authid WHERE rolcanlogin ORDER BY verifier, rolname"

# 2. Confirm every client library in use supports SCRAM.
#    This is the step that actually blocks migrations.

# 3. Set the server default, so new passwords are written as SCRAM
psql -U postgres -c "ALTER SYSTEM SET password_encryption = 'scram-sha-256'"
psql -U postgres -c 'SELECT pg_reload_conf()'

# 4. Re-set each role's password. The value may be the same; what
#    changes is the verifier that gets stored.
psql -U postgres -c "ALTER ROLE app_web PASSWORD 'the-existing-password'"

# 5. Only once every role is migrated, change pg_hba.conf from md5
#    to scram-sha-256 and reload.

The ordering matters, and the reason is a compatibility asymmetry that is worth seeing rather than taking on trust. Both rule types were tested against both verifier formats on a live 18.6 cluster:

Read-only / Safefour combinations, one failure— Captured 2026-08-27. demo_scram holds a SCRAM verifier, demo_md5 an MD5 one.

$ psql 'host=127.0.0.1 user=... dbname=postgres sslmode=disable' -tAc 'SELECT current_user'

-- pg_hba rule: md5
 demo_scram  ->  demo_scram        (accepted)
 demo_md5    ->  demo_md5          (accepted)

-- pg_hba rule: scram-sha-256
 demo_md5    ->  FATAL:  password authentication failed for user "demo_md5"
 demo_scram  ->  demo_scram        (accepted)

`pg_hba` rule	SCRAM verifier	MD5 verifier
`md5`	accepted	accepted
`scram-sha-256`	accepted	rejected

An md5 rule accepts both. A scram-sha-256 rule accepts only one. That asymmetry is what makes the ordering safe: you can complete the password migration entirely under the existing md5 rules, verifying each role as you go, and change the rule only when nothing depends on the old behaviour.

Step 5 before step 4 locks out every role still holding an MD5 verifier, all at once, at reload.

Note the failure message: password authentication failed. It is indistinguishable from a wrong password, so an operator who makes this mistake will reasonably start by investigating credentials rather than verifier formats.

Changing pg_hba.conf to scram-sha-256 before re-setting the passwords

This is the specific way the migration goes wrong, and it goes wrong completely rather than partially.

An md5 line in pg_hba.conf will accept a role holding either verifier format — it upgrades gracefully. A scram-sha-256 line will only accept a SCRAM verifier. So changing the rule first fails every role that has not yet been re-set, all at once, at reload.

There is a corollary that makes the safe order genuinely safe: because md5 accepts both, you can complete steps 3 and 4 across the whole estate with no rule change at all, verifying as you go, and change the rule last when the inventory query returns no md5 rows. Nothing is at risk until that final step, and by then nothing depends on it.

The other thing to check before starting is client library support. SCRAM has been supported by the mainstream drivers for years, but an old JDBC, ODBC or language binding pinned in an application that nobody has rebuilt may not speak it, and that failure appears only when the role’s verifier changes.

What SCRAM actually exchanges, and why the server never sees the password

The stored verifier is SCRAM-SHA-256$<iterations>:<salt>$<StoredKey>:<ServerKey>. Neither key is the password, and neither is sufficient to authenticate on its own — which is the property that makes reading pg_authid less useful to an attacker than reading an MD5 hash.

The exchange runs in four messages. The client sends a username and a random nonce. The server replies with the salt, the iteration count and its own nonce. The client derives a salted key from the password it was given, computes a proof over the message transcript, and sends the proof. The server verifies the proof using StoredKey, and sends back its own signature computed with ServerKey.

Three properties follow, and each has an operational consequence.

The password never crosses the connection, in any form, so an unencrypted connection does not leak it — though everything else the session does is still in clear, which is why TLS remains necessary for reasons unrelated to authentication.

The proof is bound to the transcript, including both nonces, so it cannot be replayed against another connection.

The server authenticates itself to the client with the final signature. The client can verify it is talking to a server that holds the verifier, which is the basis of the channel-binding variant SCRAM-SHA-256-PLUS that additionally binds the exchange to the TLS channel and defends against a man-in-the-middle that terminates TLS.

The iteration count of 4096 is the cost imposed on an offline attacker per guess. It is also a cost paid by every legitimate authentication, which is the reason it is not simply set enormously high, and a reason connection pooling matters when authentication happens per request.

Production discipline

Inventory verifier formats before assuming an estate is on SCRAM. One query against pg_authid gives the answer.
Re-set every password before changing pg_hba.conf. md5 accepts both formats; scram-sha-256 accepts only one.
Leave md5_password_warnings on. It is the inventory mechanism, and silencing it removes the signal.
Verify client library support first. It is the step that actually blocks these migrations, and it fails only once the verifier changes.
Treat pg_dumpall --globals-only output as a credential store, and keep it separate from the data dump.
Remember an MD5 verifier is a working credential, so a leaked one requires rotation rather than assessment.

Cross-course references

Secrets, PKI & Certificate Management — Part II (Secret lifecycle) covers rotation without downtime, which is the pattern the migration above follows, and Part XVIII (Incidents) covers responding to a leaked verifier.
Linux for Production Sysadmins — Part XXVII (Auth) covers the equivalent hash-format migration in the host password stack.
Git, CI/CD & GitOps — Part XXXV (Secrets in Git) covers the accidental commit of a globals dump, which is how these files usually escape.