password_encryption was a boolean from 7.2 through 9.6, and the question it answered was whether PostgreSQL should hash a new password or store it exactly as typed. In 7.2 the default was off. That is where the name comes from, and why a parameter that has never encrypted anything is still called this. PostgreSQL 10 removed the option of keeping passwords in the clear and turned the parameter into an enum. Today it takes md5 or scram-sha-256, the default has been scram-sha-256 since 14, and the context is user.
What it controls is narrow. When CREATE ROLE or ALTER ROLE is handed a password in clear text, this parameter picks the hash that goes into pg_authid.rolpassword. It converts nothing that is already stored (it could not; the server does not have the passwords), and it has no part in authenticating a role that has a password, where the exchange a client is put through depends on pg_hba.conf and on the form of the stored secret. The case against MD5, and the order in which to migrate off it, are in the md5_password_warnings post. This one is about what the parameter decides and what it does not.
A default, and only that
The server applies the setting only to strings that look like clear text. A string that is already md5 followed by 32 lowercase hex digits, or a well-formed SCRAM-SHA-256$… secret, is stored as given under either value. It has to work that way, because that is how pg_dumpall and pg_upgrade carry roles from one cluster to the next.
And since the context is user, the role whose password it is can choose. This is an unprivileged role on an 18.6 server whose postgresql.conf leaves the default alone:
1 => SHOW password_encryption; 2 scram-sha-256 3 => SET password_encryption = md5; 4 SET 5 => ALTER ROLE alice PASSWORD 'correct horse'; 6 WARNING: setting an MD5-encrypted password 7 ALTER ROLE 8 => ALTER ROLE alice SET password_encryption = md5; 9 ALTER ROLE
The last statement makes the choice stick for every later session of hers, and it needed no privilege either. So password_encryption = scram-sha-256 in postgresql.conf does not keep MD5 hashes out of the catalog. It says what happens when nobody expresses an opinion.
The setting that can refuse is the method column of pg_hba.conf. A scram-sha-256 line will not authenticate a role whose stored password is an MD5 hash, whatever the client sends, and the server log says why:
1 FATAL: password authentication failed for user "alice" 2 DETAIL: User "alice" does not have a valid SCRAM secret.
(The client sees only the first line. On RDS, where there is no pg_hba.conf to edit, the equivalent is setting rds.accepted_password_auth_method to scram.)
Who does the hashing
If the parameter was consulted, the server received the password in clear text inside a SQL statement, and it handles that statement the way it handles any other. With log_statement at ddl or above, it is logged. With statement logging off it is logged anyway if the statement fails, courtesy of log_min_error_statement; a typo in the role name is enough. And pg_stat_statements keeps the text as written, where any role with pg_monitor can read it:
1 => SELECT query FROM pg_stat_statements WHERE query ~* 'role'; 2 query 3 -------------------------------------------------- 4 CREATE ROLE gina LOGIN PASSWORD 'hunter6hunter6' 5 ALTER ROLE frank PASSWORD 'hunter5hunter5'
The alternative is to hash in the client. psql’s \password and createuser --pwprompt do, by way of libpq’s PQchangePassword() (new in 17) and PQencryptPasswordConn() respectively; psycopg exposes the same thing as psycopg2.extensions.encrypt_password() and, in psycopg 3, pgconn.encrypt_password(). Unless the caller names an algorithm, what goes to the server first is the parameter’s other job, which its own documentation does not mention:
1 LOG: statement: show password_encryption 2 LOG: statement: ALTER USER "erin2" PASSWORD 'SCRAM-SHA-256$4096:ogMeOdyPeKbWvcTJVtVTdg==$TcMXdGgD…'
The client asks the server which hash to produce. A psql from 18 pointed at a 13 server at its default therefore stores an MD5 hash, a psql from 13 pointed at an 18 server stores a SCRAM secret, and a role that has set its own default to md5 gets MD5 from \password as well. A caller that names the algorithm asks nobody. Neither does the original PQencryptPassword(), which libpq still exports and which only ever produces MD5.
Hashing in the client has a cost. The server never sees the password, so passwordcheck cannot examine it. With the module loaded on 18.6, ALTER ROLE gina PASSWORD 'short' was refused for being under eight bytes, and \password then gave the same role a one-character password without complaint. The only test that survives is whether the password equals the role name, which the module checks by hashing the name and comparing. I would still use \password. A length rule that binds only the people who send passwords through the log is not protecting much.
SCRAM secrets do not repeat
An MD5 hash is a function of the password and the role name, so setting the same password twice stores the same string. (It is also why renaming a role with an MD5 password clears the password, with a NOTICE; a SCRAM secret survives the rename.) A SCRAM secret carries a random salt. Set the same password again and the stored secret is different.
That matters wherever a copy of the secret lives outside pg_authid, and the usual place is a PgBouncer auth_file. PgBouncer can log in to the server with a SCRAM secret only when the client itself authenticated with SCRAM and the secret in the file is identical to the server’s, salt included. I copied one MD5 role and one SCRAM role from pg_authid into an auth_file (PgBouncer 1.26.0 in front of 18.6), and both connected. Then I set the same password again on each and restarted PgBouncer. The MD5 role kept working. The SCRAM role, supplying the correct password, got this:
1 FATAL: password authentication failed for user "app"
Nothing breaks at the ALTER ROLE. It breaks when PgBouncer next has to open a server connection for that role, which can be a good while later.
Under MD5 the file needed editing when a password changed. Under SCRAM it needs editing every time anyone runs ALTER ROLE … PASSWORD, including a provisioning run that sets the password the role already had. auth_query is the way out, since it reads pg_authid at each login. The catch is the auth_user it runs as. If that role logs in to the server with SCRAM, a SCRAM secret for it in the file fails with wrong password type, because there is no client exchange for PgBouncer to borrow keys from; its password has to be in the file in plain text, or it has to get in by a method that needs no password. The SCRAM pass-through that postgres_fdw and dblink gained in 18 has the same requirement of identical secrets. There the fix is to copy the secret to the other server with ALTER ROLE … PASSWORD 'SCRAM-SHA-256$…', not the password.
The lines already in the file
Through 13 the server still accepted on, true, yes and 1 as synonyms for md5, left over from the boolean. The 14 release notes call them “legacy (and undocumented),” and 14 removed them. Push a template that still says password_encryption = on to a server on 14 or later and the reload logs a complaint and skips the line. The next restart does not come up:
1 LOG: invalid value for parameter "password_encryption": "on" 2 HINT: Available values: md5, scram-sha-256. 3 FATAL: configuration file "/var/lib/postgresql/guc/d18/postgresql.conf" contains errors
The more common leftover is an explicit password_encryption = md5, added years ago for a driver that could not do SCRAM and carried through every upgrade since by configuration management. On that server the default change in 14 never happened. There are two places to look:
1 SELECT setting, source, sourcefile, sourceline 2 FROM pg_settings WHERE name = 'password_encryption'; 3 4 SELECT r.rolname, d.datname, s.setconfig 5 FROM pg_db_role_setting s 6 LEFT JOIN pg_roles r ON r.oid = s.setrole 7 LEFT JOIN pg_database d ON d.oid = s.setdatabase 8 WHERE EXISTS (SELECT FROM unnest(s.setconfig) c 9 WHERE c LIKE 'password_encryption=%');
Run the first as a superuser with no override of its own. If it says anything but default, remove the line it points to (ALTER SYSTEM RESET if the file is postgresql.auto.conf), and remove whatever the second one finds. If one role has to stay on MD5 a while longer, SET password_encryption = md5 in the session that sets its password and leave the server alone. Set passwords with \password. Once the catalog has no MD5 hashes left, change md5 to scram-sha-256 in pg_hba.conf. That line can say no. This parameter cannot.