MIME-Version: 1.0
From: =?UTF-8?Q?Alpaslan_AKDA=C4=9E?= <alpaslanakdag@gmail.com>
Date: Wed, 9 Jul 2025 15:56:58 +0200
Message-ID: <CAHKeUX8D-c=Y8su6eFLZXJ09VOVLnSUHYD-649NoXW8cHdBPhg@mail.gmail.com>
Subject: Password Encryption and Connection Issues
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000eea69b06397f730d"
Archived-At: <https://www.postgresql.org/message-id/CAHKeUX8D-c%3DY8su6eFLZXJ09VOVLnSUHYD-649NoXW8cHdBPhg%40mail.gmail.com>
Precedence: bulk

--000000000000eea69b06397f730d
Content-Type: text/plain; charset="UTF-8"

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

--000000000000eea69b06397f730d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div><div>Hello all<br><br></div>We have recently upg=
raded our PostgreSQL instances from version 13 to 16. During the upgrade, w=
e also changed the <code>password_encryption</code> setting in <code>postgr=
esql.conf</code> to <code>scram-sha-256</code>.<p>Before the upgrade, we us=
ed <code>pg_dumpall --roles-only</code> to export all users and their MD5-h=
ashed passwords. After the upgrade, we executed this SQL script to restore =
the users, and all users with their MD5 hashes were recreated successfully.=
</p>
<p>However, we observed that:</p>
<ul><li>
<p>New users created under the <code>scram-sha-256</code> encryption settin=
g have passwords starting with <code>SCRAM-SHA-256$4096:</code> in <code>pg=
_authid</code>.</p>
</li><li>
<p>The imported users still have passwords in the MD5 format, e.g., <code>m=
d5a33e074800fe59f4ec8a123d0085d0e9</code>.</p>
</li><li>
<p>Our <code>pg_hba.conf</code> still uses <code>md5</code> as the authenti=
cation method.</p>
</li></ul>
<p>As a result, some users are able to connect, while others cannot.</p>
<p>My questions are:</p>
<ol><li>
<p>Is it expected behavior that users created with <code>scram-sha-256</cod=
e> passwords can still connect via <code>md5</code> in <code>pg_hba.conf</c=
ode>?</p>
</li><li>
<p>Under the current settings, is it still possible to use MD5-style passwo=
rd hashes for user creation? How does PostgreSQL treat this compatibility?<=
/p></li><li>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?</l=
i></ol>
<p>Thank you in advance for your support.<br><br></p><p>Best regards,<br></=
p><p>Alpaslan</p></div></div><div><br></div></div>

--000000000000eea69b06397f730d--