Password Encryption and Connection Issues

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:

1. Is it expected behavior that users created with scram-sha-256 passwords can still connect via md5 in pg_hba.conf?

2. Under the current settings, is it still possible to use MD5-style password hashes for user creation? How does PostgreSQL treat this compatibility?

3. 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?

Thank you in advance for your support.

Best regards,

Alpaslan