PostgreSQL: password_encryption sets the hash for new passwords, it doesn't migrate old ones

A consultant at PGX Inc. published another chapter of the "All Your GUCs in a Row" series on Planet PostgreSQL, this time dedicated to password_encryption. The piece dismantles a common assumption among PostgreSQL administrators: that changing this parameter in postgresql.conf somehow converts passwords already stored in the database. It doesn't, and understanding why matters for planning MD5-to-SCRAM migrations without surprises in production.

From boolean to enum: where the name comes from

Through version 9.6, password_encryption was a boolean: it only decided whether PostgreSQL should hash a new password or store it exactly as typed. In 7.2 the default was off, which explains both the parameter's name (it never encrypted anything, only hashed) and why it sounds odd today.

Version 10 removed the option to store a password in plaintext and turned the parameter into an enum. Since then it accepts md5 or scram-sha-256, the default has been scram-sha-256 since version 14, and the application context is user: any role can alter its own behavior, with no special privilege.

What the parameter decides, and what it doesn't

The control exercised by password_encryption is narrow. When a CREATE ROLE or ALTER ROLE receives a plaintext password, the parameter chooses which hash goes into pg_authid.rolpassword. That's it.

It doesn't convert anything already stored, because it couldn't: the server doesn't keep the original password, only the hash. And it doesn't take part in authenticating a role that already has a password set; that step depends on the method configured in pg_hba.conf and the shape of the secret already stored in the catalog.

The server only applies the setting to strings that look like plaintext. A string already in md5 format followed by 32 hex digits, or a well-formed SCRAM-SHA-256$... secret, is stored as-is, regardless of the configured value. This behavior is mandatory: it's how pg_dumpall and pg_upgrade load roles from one cluster into another without reprocessing passwords.

Because the context is user, each role can choose its own value. The author demonstrates this with a session that has no special privilege, on an 18.6 server with the scram-sha-256 default untouched in postgresql.conf:

=> SHOW password_encryption;
scram-sha-256
=> SET password_encryption = md5;
SET
=> ALTER ROLE alice PASSWORD 'correct horse';
WARNING: setting an MD5-encrypted password
ALTER ROLE
=> ALTER ROLE alice SET password_encryption = md5;
ALTER ROLE

The last statement locks in the choice for every future session of role alice, with no privilege required. In other words: password_encryption = scram-sha-256 in postgresql.conf doesn't prevent an MD5 hash in the catalog. It only states what happens when nobody expresses a contrary opinion.

The real gatekeeper is in pg_hba.conf

The parameter that can actually refuse is the method column in pg_hba.conf. A scram-sha-256 line won't authenticate a role whose stored password is an MD5 hash, no matter what the client sends. The server log is direct:

FATAL: password authentication failed for user "alice"
DETAIL: User "alice" does not have a valid SCRAM secret.

The client only sees the first line. On Amazon RDS, where there's no editable pg_hba.conf, the equivalent is the rds.accepted_password_auth_method parameter set to scram.

In short: what decides what goes into the catalog is password_encryption (and each role can override it); what decides what actually authenticates is the method in pg_hba.conf. Confusing the two is the source of much of the poorly planned MD5-to-SCRAM migrations.

Who does the hashing: server or client

If the parameter was consulted, it's because the server received the password in plaintext inside a SQL statement, and treats that statement like any other. With log_statement set to ddl or above, it gets logged. Even with statement logging off, it shows up if the statement fails, because of log_min_error_statement: a simple typo in the role name is enough. And pg_stat_statements keeps the text exactly as written, readable by any role with the pg_monitor privilege:

=> SELECT query FROM pg_stat_statements WHERE query ~* 'role';
query
--------------------------------------------------
CREATE ROLE gina LOGIN PASSWORD 'hunter6hunter6'
ALTER ROLE frank PASSWORD 'hunter5hunter5'

The alternative is hashing on the client. psql's \password and createuser --pwprompt do this, through PQchangePassword() (a new function in libpq version 17) and PQencryptPasswordConn(), respectively. psycopg exposes the same capability as psycopg2.extensions.encrypt_password() and, in psycopg 3, pgconn.encrypt_password().

When whoever calls these functions doesn't specify an algorithm, the client asks the server which hash to use, a behavior that the parameter's own documentation doesn't mention:

LOG: statement: show password_encryption
LOG: statement: ALTER USER "erin2" PASSWORD 'SCRAM-SHA-256$4096:ogMeOdyPeKbWvcTJVtVTdg==$TcMXdGgD...'

This has a practical consequence in environments with mixed versions: a psql 18 pointed at a version 13 server (whose default is still MD5) stores an MD5 hash; a psql 13 pointed at a version 18 server stores a SCRAM secret; and a role that has already set md5 as a personal preference gets MD5 from \password too, regardless of the server version. Whoever calls the function and names the algorithm explicitly doesn't ask anyone anything, and the original PQencryptPassword(), which libpq still exports, only produces MD5.

The price of hashing on the client

Hashing on the client has a cost that rarely shows up in the documentation: the server never sees the password in plaintext, so the passwordcheck module can't examine it. The author tested this on an 18.6 server with passwordcheck loaded: ALTER ROLE gina PASSWORD 'short' was rejected for having fewer than eight bytes, but \password gave the same role a one-character password without complaint.

The only check that survives the client path is whether the password matches the role name, because the module does this by hashing the name and comparing hashes. In practice, any complexity policy that relies on passwordcheck only applies to whoever sends the password through the path that passes through the server log: a length rule that protects only part of the password-change routes protects little.

SCRAM secrets don't repeat, and that breaks PgBouncer

An MD5 hash is a function of the password and the role name: setting the same password twice produces the same string. That's also why renaming a role with an MD5 password erases the password, with a warning NOTICE. A SCRAM secret carries a random salt: setting the same password again produces a different stored secret, and renaming doesn't affect the secret.

This matters whenever a copy of the secret lives outside pg_authid, and the most common place is a PgBouncer auth_file. PgBouncer can only authenticate to the server with a SCRAM secret when the client also authenticated via SCRAM and the secret in the file is identical to the server's, salt included.

The author copied an MD5 role and a SCRAM role from pg_authid into an auth_file (PgBouncer 1.26.0 in front of a PostgreSQL 18.6), and both connected normally. Then he set the same password again on each one and restarted PgBouncer: the MD5 role kept working, the SCRAM role, even receiving the correct password, failed:

FATAL: password authentication failed for user "app"

Nothing breaks at the moment of ALTER ROLE. It breaks the next time PgBouncer needs to open a new connection to the server for that role, which can be much later.

Under MD5, the file only needed editing when the password actually changed. Under SCRAM, it needs editing every time someone runs ALTER ROLE ... PASSWORD, including a provisioning routine that resets a password the role already had.

auth_query solves this by reading pg_authid on every login, but there's a catch: if the role used as auth_user authenticates via SCRAM, its SCRAM secret in the file fails with an incompatible password-type error, because there's no client exchange from which PgBouncer can reuse keys. That role's password needs to be in plaintext in the file, or it needs to log in through a method that requires no password.

The same problem hits the SCRAM pass-through that postgres_fdw and dblink gained in version 18: it also requires identical secrets on both sides. The fix there is to copy the secret between servers with ALTER ROLE ... PASSWORD 'SCRAM-SHA-256$...', never the original password.

Legacy values and how to clean up the configuration

Through version 13, the server still accepted on, true, yes, and 1 as synonyms for md5, a leftover from the boolean era. Version 14's release notes call these values "legacy (and undocumented)", and 14 removed them for good. A configuration template with password_encryption = on pushed to a version 14 or later server makes the reload log an error and ignore the line; the next restart simply won't come up:

LOG: invalid value for parameter "password_encryption": "on"
HINT: Available values: md5, scram-sha-256.
FATAL: configuration file "/var/lib/postgresql/guc/d18/postgresql.conf" contains errors

More common is finding an explicit password_encryption = md5, inherited from years ago because of a driver that didn't support SCRAM, carried along by every management configuration since then. On that server, version 14's default change never actually took effect. Two queries reveal the problem:

SELECT setting, source, sourcefile, sourceline
FROM pg_settings WHERE name = 'password_encryption';

SELECT r.rolname, d.datname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase
WHERE EXISTS (SELECT FROM unnest(s.setconfig) c
 WHERE c LIKE 'password_encryption=%');

The path the author recommends to clean up the environment is:

1. Run the first query as a superuser with no override of their own; if source isn't default, remove the flagged line (ALTER SYSTEM RESET if the file is postgresql.auto.conf).
2. Remove any result from the second query, which shows overrides by role or database.
3. If some role needs to stay on MD5 for a while, use SET password_encryption = md5 only in the session that sets its password, without touching the whole server.
4. Always set passwords through \password, never through ALTER ROLE ... PASSWORD directly in SQL.
5. Only after the catalog no longer has any MD5 hash left, swap md5 for scram-sha-256 in pg_hba.conf.

That last line is the only one that can refuse a connection. The password_encryption parameter, by itself, cannot.