We have recently upgraded our PostgreSQL instances from version 13 to 16. During the upgrade, we also changed the password_encryption setting in postgresql.conf to scram-sha-256.
Before the upgrade, we used pg_dumpall --roles-only to export all users and their MD5-hashed passwords. After the upgrade, we executed this SQL script to restore the users, and all users with their MD5 hashes were recreated successfully.
However, we observed that:
New users created under the scram-sha-256 encryption setting have passwords starting with SCRAM-SHA-256$4096: in pg_authid.
The imported users still have passwords in the MD5 format, e.g., md5a33e074800fe59f4ec8a123d0085d0e9.
Our pg_hba.conf still uses md5 as the authentication method.
As a result, some users are able to connect, while others cannot.
My questions are:
Is it expected behavior that users created with scram-sha-256 passwords can still connect via md5 in pg_hba.conf?
Under the current settings, is it still possible to use MD5-style password hashes for user creation? How does PostgreSQL treat this compatibility?
In such a case, what would be the recommended approach or best practice to follow during upgrades in order to avoid this kind of issue?
