skip to content

Moving a PostgreSQL cluster from md5 to scram-sha-256, how do password_encryption and the pg_hba.conf method interact, and in what order do you switch them?

level: middleimportance: must knowfreq 60%

answer

  1. stored hash versus requested exchange
  2. an md5 line can run SCRAM
  3. rehash before switching lines
  4. 18 warns when MD5 is set

basics

~20 s

password_encryption decides how a newly set password is hashed; the pg_hba.conf method decides which exchange the server demands. An md5 line still accepts SCRAM-hashed roles, but a scram-sha-256 line fails MD5-hashed ones, so rehash every password first, then switch lines.

solid answer

~50 s

Two settings meet at login. `password_encryption` (default `scram-sha-256`) acts only when a password is set by `CREATE ROLE`/`ALTER ROLE ... PASSWORD` or psql's `\password`. Hashes already stored in `pg_authid.rolpassword` keep their format. The `pg_hba.conf` method then chooses the exchange. An `md5` line runs SCRAM automatically for roles holding a SCRAM secret and MD5 for the rest. A `scram-sha-256` line can only run SCRAM, so a role that still holds an MD5 hash fails with `password authentication failed`. The server cannot convert an MD5 hash without the plaintext. So the order is: confirm every driver supports SCRAM, keep `password_encryption = scram-sha-256`, have each role set its password again, check that no hash starting with `md5` remains, and only then change the lines and reload. In PostgreSQL 18, MD5 passwords are deprecated, and setting one raises a WARNING unless `md5_password_warnings` is off.

code

sql · 7 lines
sql
-- run as a superuser: pg_roles masks rolpassword
SELECT rolname
FROM pg_authid
WHERE rolpassword LIKE 'md5%'
ORDER BY rolname;

SHOW password_encryption;  -- expect scram-sha-256

go deeper

for a junior

Know that scram-sha-256 is the recommended password method, md5 is deprecated in 18, and the stored password format is separate from the pg_hba.conf method.

for a middle

Explain the matrix: md5 records run SCRAM for SCRAM-hashed roles, scram-sha-256 records fail MD5-hashed roles, and password_encryption acts only when a password is set.

for a senior

Lay out a lockout-free rollout: driver audit, finding MD5 roles in pg_authid, password resets, switching records last, and require_auth on clients to block downgrades.

for a principal

Plan for MD5's removal across a fleet: inventory old drivers, decide how application credentials rotate, and set a date after which md5 records fail review.

## Two settings, two moments Password authentication in PostgreSQL involves two settings that act at different times, and most migration failures come from mixing them up. - **`password_encryption`** is a parameter read when a password is **set**: `CREATE ROLE ... PASSWORD`, `ALTER ROLE ... PASSWORD`, or psql's `\password`, which hashes on the client so the cleartext never reaches the server log. Its values are `scram-sha-256` (the default) and `md5`. Changing it rewrites nothing that is already stored. - **The `pg_hba.conf` method** is read when a client **logs in**. It decides which exchange the server asks for: `scram-sha-256`, `md5` or `password` (cleartext). The stored secret lives in `pg_authid.rolpassword`. An MD5 secret is the string `md5` followed by the MD5 of the password concatenated with the role name. A SCRAM secret starts with `SCRAM-SHA-256$`, followed by the iteration count, a random salt and two derived keys. ## What happens at login | Stored secret | `md5` record | `scram-sha-256` record | `password` record | |---|---|---|---| | SCRAM-SHA-256 | SCRAM runs automatically | SCRAM runs | cleartext checked against the secret | | MD5 hash | MD5 challenge-response | fails as a wrong password | cleartext checked against the hash | | no password (NULL) | always fails | always fails | always fails | The automatic switch in the first row exists to ease migration: an `md5` record lets SCRAM-hashed and MD5-hashed roles log in side by side. The reverse does not work. Under a `scram-sha-256` record, a role with an MD5 hash goes through a mock SCRAM exchange that is bound to fail. The client sees only `password authentication failed for user ...`, while the server log adds a DETAIL saying the user `does not have a valid SCRAM secret`. The `password` column explains why `password` records belong only on TLS connections. The client sends the cleartext, and the server hashes it to compare with whatever secret is stored. That works with either hash format, but anyone who can read the traffic reads the password. ## Why old hashes cannot be converted A SCRAM secret is derived from the plaintext password with a salt and an iteration count (`scram_iterations`, default 4096, fixed when the secret is created). An MD5 hash cannot be turned back into the plaintext, so the only way to get a SCRAM secret is for someone to set the password again. Two side effects follow from the MD5 format: - Renaming a role clears an MD5 password, because the role name is part of what was hashed. SCRAM secrets survive a rename. - A password string that is already hashed is stored as it is, whatever `password_encryption` says. Restoring an old `pg_dumpall` therefore brings MD5 hashes back. ## A migration order that avoids lockouts 1. Confirm that every client library and driver that connects can speak SCRAM. 2. Check `SHOW password_encryption;` and make sure no role or database setting pins it to `md5`; it is a user-settable parameter. 3. As a superuser, list the roles still holding MD5 hashes (query below). `pg_roles` will not help, because its `rolpassword` always reads `********`. 4. Have each person set a new password with `\password`, and rotate application passwords through your secret store. 5. Re-run the query until it returns no rows. 6. Change the `md5` records to `scram-sha-256`, check them with `pg_hba_file_rules`, and reload. 7. Optionally, set `require_auth=scram-sha-256` in client connection strings so a client refuses any weaker exchange the server offers. Doing step 6 first locks out every role that has not yet been rehashed. ## What PostgreSQL 18 changed - MD5 password support is **deprecated** and is due to be removed in a future major release. - `CREATE ROLE` and `ALTER ROLE` emit a WARNING (`setting an MD5-encrypted password`) whenever the stored result is an MD5 hash, including a pre-hashed string from a dump. The new parameter `md5_password_warnings`, default `on`, controls it. - MD5 logins still work in 18, and `md5` is still an accepted value of `password_encryption`. The `scram-sha-256` default is not new in 18. - `scram_iterations` (default 4096) sets the iteration count for SCRAM secrets created from then on. Raising it slows brute-force attacks on stolen secrets. The documentation warns that a role whose secret was created with a different count from the server's configured value can reveal to an unauthenticated user that the role exists. For the clinic-records cluster, this means the audit finding 'MD5 hashes in use' is closed only when the query below is empty and no `pg_hba.conf` record names `md5`.

  • How do you find the roles that still hold MD5 hashes?
    Query `pg_authid` as a superuser: `SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';`. `pg_roles` will not help, because its `rolpassword` column always reads `********`. A migrated role's secret starts with `SCRAM-SHA-256$`, so the same query with that prefix shows the progress.
  • How can a client refuse to be downgraded to MD5 or a cleartext password?
    libpq's `require_auth` option lists the methods the client accepts. With `require_auth=scram-sha-256`, the connection fails if the server asks for anything else. Over TLS, `channel_binding=require` also binds SCRAM to the TLS channel (SCRAM-SHA-256-PLUS), so a man-in-the-middle with its own certificate cannot complete the exchange.
  • Why did a role lose its password after ALTER ROLE ... RENAME TO?
    An MD5 hash is the MD5 of the password concatenated with the role name, so the name works as the salt. After a rename the old hash could never match again, and PostgreSQL clears it. SCRAM secrets carry their own random salt and survive a rename.

  • Changing password_encryption rehashes existing passwords at the next reload.
  • A pg_hba.conf md5 record forces MD5 even for roles with SCRAM secrets.
  • The server can convert stored MD5 hashes to SCRAM without the passwords.
  • PostgreSQL 18 rejects MD5 logins outright.
  • Switch pg_hba.conf to scram-sha-256 first and reset passwords afterwards.

Assumes PostgreSQL 18, which deprecates MD5 passwords and adds md5_password_warnings; the scram-sha-256 default of password_encryption predates 18.