public inbox for [email protected]
help / color / mirror / Atom feedPassword Encryption and Connection Issues
8+ messages / 6 participants
[nested] [flat]
* Password Encryption and Connection Issues
@ 2025-07-09 13:56 Alpaslan AKDAĞ <[email protected]>
2025-07-09 14:06 ` Re: Password Encryption and Connection Issues David G. Johnston <[email protected]>
2025-07-09 14:58 ` Re: Password Encryption and Connection Issues Greg Sabino Mullane <[email protected]>
2025-07-09 15:11 ` Re: Password Encryption and Connection Issues Adrian Klaver <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Alpaslan AKDAĞ @ 2025-07-09 13:56 UTC (permalink / raw)
To: [email protected] <[email protected]>
Hello all
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Password Encryption and Connection Issues
2025-07-09 13:56 Password Encryption and Connection Issues Alpaslan AKDAĞ <[email protected]>
@ 2025-07-09 14:06 ` David G. Johnston <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2025-07-09 14:06 UTC (permalink / raw)
To: Alpaslan AKDAĞ <[email protected]>; +Cc: [email protected] <[email protected]>
On Wed, Jul 9, 2025 at 6:57 AM Alpaslan AKDAĞ <[email protected]>
wrote:
>
> 1. 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?
>
> This is all described quite clearly in the documentation, including the
upgrade procedure in the final paragraph.
https://www.postgresql.org/docs/current/auth-password.html
Given that pg_hba.conf still uses md5 I'm a bit confused regarding the
claim of some people being unable to authenticate; but you've provided
insufficient data to diagnose. In any case, hopefully you can just change
all passwords to use scram and move on.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Password Encryption and Connection Issues
2025-07-09 13:56 Password Encryption and Connection Issues Alpaslan AKDAĞ <[email protected]>
@ 2025-07-09 14:58 ` Greg Sabino Mullane <[email protected]>
2025-07-09 15:09 ` Re: Password Encryption and Connection Issues Ron Johnson <[email protected]>
2025-07-09 17:02 ` Re: Password Encryption and Connection Issues Greg Sabino Mullane <[email protected]>
2 siblings, 2 replies; 8+ messages in thread
From: Greg Sabino Mullane @ 2025-07-09 14:58 UTC (permalink / raw)
To: Alpaslan AKDAĞ <[email protected]>; +Cc: [email protected] <[email protected]>
On Wed, Jul 9, 2025 at 9:57 AM Alpaslan AKDAĞ <[email protected]>
wrote:
> Is it expected behavior that users created with scram-sha-256 passwords
> can still connect via md5 in pg_hba.conf?
Yes. From the docs:
> To ease transition from the md5 method to the newer SCRAM method, if md5 is
> specified as a method in pg_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.
--
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Password Encryption and Connection Issues
2025-07-09 13:56 Password Encryption and Connection Issues Alpaslan AKDAĞ <[email protected]>
2025-07-09 14:58 ` Re: Password Encryption and Connection Issues Greg Sabino Mullane <[email protected]>
@ 2025-07-09 15:09 ` Ron Johnson <[email protected]>
2025-07-09 15:28 ` Re: Password Encryption and Connection Issues Laurenz Albe <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Ron Johnson @ 2025-07-09 15:09 UTC (permalink / raw)
To: [email protected] <[email protected]>
On Wed, Jul 9, 2025 at 10:59 AM Greg Sabino Mullane <[email protected]>
wrote:
> On Wed, Jul 9, 2025 at 9:57 AM Alpaslan AKDAĞ <[email protected]>
> wrote:
>
>> Is it expected behavior that users created with scram-sha-256 passwords
>> can still connect via md5 in pg_hba.conf?
>
>
> Yes. From the docs:
>
>> To ease transition from the md5 method to the newer SCRAM method, if md5 is
>> specified as a method in pg_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.
>
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.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Password Encryption and Connection Issues
2025-07-09 13:56 Password Encryption and Connection Issues Alpaslan AKDAĞ <[email protected]>
2025-07-09 14:58 ` Re: Password Encryption and Connection Issues Greg Sabino Mullane <[email protected]>
2025-07-09 15:09 ` Re: Password Encryption and Connection Issues Ron Johnson <[email protected]>
@ 2025-07-09 15:28 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2025-07-09 15:28 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Password Encryption and Connection Issues
2025-07-09 13:56 Password Encryption and Connection Issues Alpaslan AKDAĞ <[email protected]>
2025-07-09 14:58 ` Re: Password Encryption and Connection Issues Greg Sabino Mullane <[email protected]>
@ 2025-07-09 17:02 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Greg Sabino Mullane @ 2025-07-09 17:02 UTC (permalink / raw)
To: Alpaslan AKDAĞ <[email protected]>; +Cc: [email protected] <[email protected]>
> Best solution: Upgrade everyone to scram, then change md5 to scram in
> pg_hba.conf and never look back.
>
To expand more on the "upgrade everyone to scram", that means force all
users to set a new password while using scram (which should be the
default). You can do it yourself by getting a list of users and changing
their passwords inside psql:
-- List all users still stuck in md5-land:
greg=# select rolname from pg_authid where rolpassword ~ '^md5'
alice
eve
mallory
(3 rows)
-- Just in case, force use of scram
greg=# set password_encryption = 'scram-sha-256';
SET
-- Reset each user's password to some strong password of your choice:
greg=# \password alice
Enter new password for user "alice":
Enter it again:
-- Repeat the above until this query returns no rows:
select rolname from pg_authid where rolpassword ~ '^md5'
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Password Encryption and Connection Issues
2025-07-09 13:56 Password Encryption and Connection Issues Alpaslan AKDAĞ <[email protected]>
@ 2025-07-09 15:11 ` Adrian Klaver <[email protected]>
2025-07-09 15:17 ` Re: Password Encryption and Connection Issues Ron Johnson <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2025-07-09 15:11 UTC (permalink / raw)
To: Alpaslan AKDAĞ <[email protected]>; [email protected] <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Password Encryption and Connection Issues
2025-07-09 13:56 Password Encryption and Connection Issues Alpaslan AKDAĞ <[email protected]>
2025-07-09 15:11 ` Re: Password Encryption and Connection Issues Adrian Klaver <[email protected]>
@ 2025-07-09 15:17 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Ron Johnson @ 2025-07-09 15:17 UTC (permalink / raw)
To: [email protected] <[email protected]>
On Wed, Jul 9, 2025 at 11:11 AM Adrian Klaver <[email protected]>
wrote:
> 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?
>
This is a salient point:clients from the pre-PG10 can only connect using
md5. Thus, we have to use md5 hashes even in PG16. 😭
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-07-09 17:02 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-09 13:56 Password Encryption and Connection Issues Alpaslan AKDAĞ <[email protected]>
2025-07-09 14:06 ` David G. Johnston <[email protected]>
2025-07-09 14:58 ` Greg Sabino Mullane <[email protected]>
2025-07-09 15:09 ` Ron Johnson <[email protected]>
2025-07-09 15:28 ` Laurenz Albe <[email protected]>
2025-07-09 17:02 ` Greg Sabino Mullane <[email protected]>
2025-07-09 15:11 ` Adrian Klaver <[email protected]>
2025-07-09 15:17 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox