Thread: Password Encryption and Connection Issues
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 withSCRAM-SHA-256$4096:
inpg_authid
.The imported users still have passwords in the MD5 format, e.g.,
md5a33e074800fe59f4ec8a123d0085d0e9
.Our
pg_hba.conf
still usesmd5
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 viamd5
inpg_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?
Thank you in advance for your support.
Best regards,
Alpaslan
- 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?
Is it expected behavior that users created withscram-sha-256
passwords can still connect viamd5
inpg_hba.conf
?
To ease transition from themd5
method to the newer SCRAM method, ifmd5
is specified as a method inpg_hba.conf
but the user's password on the server is encrypted for SCRAM (see below), then SCRAM-based authentication will automatically be chosen instead.
As a result, some users are able to connect, while others cannot.
On Wed, Jul 9, 2025 at 9:57 AM Alpaslan AKDAĞ <alpaslanakdag@gmail.com> wrote:Is it expected behavior that users created withscram-sha-256
passwords can still connect viamd5
inpg_hba.conf
?Yes. From the docs:To ease transition from themd5
method to the newer SCRAM method, ifmd5
is specified as a method inpg_hba.conf
but the user's password on the server is encrypted for SCRAM (see below), then SCRAM-based authentication will automatically be chosen instead.You can think of "md5" inside pg_hba.conf as "md5 or better"As a result, some users are able to connect, while others cannot.Can you expand on this? Nothing you have done should be preventing logins, as far as I can tell.Best solution: Upgrade everyone to scram, then change md5 to scram in pg_hba.conf and never look back.
On 7/9/25 06:56, Alpaslan AKDAĞ wrote: > Hello all > > As a result, some users are able to connect, while others cannot. What client is being used and what version of said client? > Best regards, > > Alpaslan > > -- Adrian Klaver adrian.klaver@aklaver.com
On 7/9/25 06:56, Alpaslan AKDAĞ wrote:
> Hello all
>
> As a result, some users are able to connect, while others cannot.
What client is being used and what version of said client?
That requires setting the password to null and then recreating the password, no?
On Wed, 2025-07-09 at 11:09 -0400, Ron Johnson wrote: > > Best solution: Upgrade everyone to scram, then change md5 to scram > > in pg_hba.conf and never look back. > > That requires setting the password to null and then recreating the > password, no? Otherwise IIRC, changing an md5 password leaves the > new password also in md5 format. No. The hashing algorithm chosen depends only on the current setting of "password_encryption", not on the hashing algorithm chosen for the previous password. Yours, Laurenz Albe
On Wed, Jul 9, 2025 at 8:09 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:That requires setting the password to null and then recreating the password, no?You might want to verify that claim, and suggest a doc patch or bug fix if you find it to be true - I sure don't see anything that remotely suggests this.
Best solution: Upgrade everyone to scram, then change md5 to scram in pg_hba.conf and never look back.
Enter it again: