Use scram-sha-256, not md5

The fixpostgresql.conf

password_encryption = scram-sha-256

Then re-set every existing password — this is the part that actually migrates anything:

ALTER ROLE myapp WITH PASSWORD 'the-same-or-a-new-secret';

And switch the rules in pg_hba.conf from md5 to scram-sha-256.

Why it matters

scram-sha-256 has been the default for password_encryption since PostgreSQL 14, and md5 is now on an explicit path to removal. The PostgreSQL 18 documentation states it plainly:

Support for MD5-encrypted passwords is deprecated and will be removed in a future release of PostgreSQL.

PostgreSQL 18 also adds the md5_password_warnings parameter — on by default — which makes CREATE ROLE and ALTER ROLE emit a deprecation warning into the log whenever a password is stored as md5. If your logs have started filling with those, this page is why.

Beyond the deprecation, the cryptography is the point. Postgres’s md5 scheme hashes the password with the role name as salt, which means the stored hash is the credential: anyone who reads pg_authid can replay it to authenticate, without ever cracking anything. SCRAM does not have that property — the stored verifier cannot be replayed.

Changing password_encryption does nothing on its own

This is the mistake that makes people think they’ve migrated.

password_encryption only decides how a password is hashed when it is set. Changing it does not rewrite existing hashes. A role whose password was stored as md5 in 2019 still has an md5 hash, still authenticates via md5, and is entirely unaffected by the new setting.

The hash is only rewritten when the password is set again. That’s why the ALTER ROLE ... PASSWORD step above is not optional — it is the migration. And it must come after the setting change, or you’ll write another md5 hash.

Finding the roles that need it

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

Requires superuser — pg_authid is restricted precisely because those hashes are sensitive.

Verify it worked

Confirm the setting:

SHOW password_encryption;

Then confirm no role is still on md5 — this is the real check:

SELECT count(*) FROM pg_authid WHERE rolpassword LIKE 'md5%';

Expect 0. A non-zero count means the migration is incomplete regardless of what password_encryption says.

Confirm no rule still offers md5:

SELECT count(*) FROM pg_hba_file_rules WHERE auth_method = 'md5';

Then prove a real connection negotiates SCRAM:

SELECT usename, auth_method FROM pg_stat_activity
JOIN pg_stat_gssapi USING (pid) WHERE pid = pg_backend_pid();

Simpler and more reliable — turn on log_connections and read the log line, which names the method used for the authentication that just happened.

Before you apply this

Order matters, and getting it wrong locks out the role you just “fixed”.

The trap: if you switch pg_hba.conf to scram-sha-256 before re-setting a role’s password, that role’s stored hash is still md5. SCRAM authentication cannot use an md5 hash, so the role fails to connect — and the error is a generic authentication failure, which sends people hunting for the wrong problem.

Correct order:

1. password_encryption = scram-sha-256, then reload.
2. ALTER ROLE ... PASSWORD ... for every login role. Verify the count of md5 roles is zero.
3. Only then change pg_hba.conf from md5 to scram-sha-256 and reload.

Old clients cannot do SCRAM. libpq gained support in PostgreSQL 10, and JDBC/npgsql/psycopg equivalents landed around the same era. Anything older — an appliance, a vendored driver, an ancient application server — fails to authenticate and cannot be fixed server-side. Inventory your clients before step 3, not after.

You can stage it: pg_hba.conf rules are per-host and per-user, so migrate one application’s rule at a time rather than flipping the whole file.

Related

• Remove trust authentication — do that first; it sets the passwords this page hashes.
• Enable TLS — SCRAM protects the stored hash, TLS protects the connection.