MIME-Version: 1.0
References: <CAHKeUX8D-c=Y8su6eFLZXJ09VOVLnSUHYD-649NoXW8cHdBPhg@mail.gmail.com>
 <CAKAnmmK9hQE-Cs2rVx_7brmubA1qCyNPmnOf=eA2HuxPwMy9=A@mail.gmail.com>
In-Reply-To: <CAKAnmmK9hQE-Cs2rVx_7brmubA1qCyNPmnOf=eA2HuxPwMy9=A@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 9 Jul 2025 11:09:10 -0400
Message-ID: <CANzqJaC2zgNWzizoXLHS6EOmKOOezq3Rnd9WvJZ2rB7-__=OEA@mail.gmail.com>
Subject: Re: Password Encryption and Connection Issues
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003090be0639807664"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaC2zgNWzizoXLHS6EOmKOOezq3Rnd9WvJZ2rB7-__%3DOEA%40mail.gmail.com>
Precedence: bulk

--0000000000003090be0639807664
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Jul 9, 2025 at 10:59=E2=80=AFAM Greg Sabino Mullane <htamfids@gmail=
.com>
wrote:

> On Wed, Jul 9, 2025 at 9:57=E2=80=AFAM Alpaslan AKDA=C4=9E <alpaslanakdag=
@gmail.com>
> 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 authenticati=
on
>> 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.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Jul 9, 2025 at 10:59=E2=80=AFAM G=
reg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail=
.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr">On Wed, Jul 9, 2025 at 9:57=E2=80=AFAM Alpaslan AKDA=C4=9E &lt;<a =
href=3D"mailto:alpaslanakdag@gmail.com" target=3D"_blank">alpaslanakdag@gma=
il.com</a>&gt; wrote:</div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
">Is it expected behavior that users created with <code>scram-sha-256</code=
> passwords can still connect via <code>md5</code> in <code>pg_hba.conf</co=
de>?</blockquote><div><br></div><div>Yes. From the docs:=C2=A0</div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><span style=3D"color:rgb(0,0,0);=
font-family:&quot;Open Sans&quot;,sans-serif;font-size:18px">To ease transi=
tion from the=C2=A0</span><code style=3D"box-sizing:border-box;font-family:=
monospace,monospace;font-size:18px;color:rgb(0,0,0);background-color:rgb(24=
8,249,250);border-radius:0.25rem;margin:0.6rem 0px">md5</code><span style=
=3D"color:rgb(0,0,0);font-family:&quot;Open Sans&quot;,sans-serif;font-size=
:18px">=C2=A0method to the newer SCRAM method, if=C2=A0</span><code style=
=3D"box-sizing:border-box;font-family:monospace,monospace;font-size:18px;co=
lor:rgb(0,0,0);background-color:rgb(248,249,250);border-radius:0.25rem;marg=
in:0.6rem 0px">md5</code><span style=3D"color:rgb(0,0,0);font-family:&quot;=
Open Sans&quot;,sans-serif;font-size:18px">=C2=A0is specified as a method i=
n=C2=A0</span><code style=3D"box-sizing:border-box;font-family:monospace,mo=
nospace;font-size:18px;color:rgb(0,0,0);background-color:rgb(248,249,250);b=
order-radius:0.25rem;margin:0.6rem 0px">pg_hba.conf</code><span style=3D"co=
lor:rgb(0,0,0);font-family:&quot;Open Sans&quot;,sans-serif;font-size:18px"=
>=C2=A0but the user&#39;s password on the server is encrypted for SCRAM (se=
e below), then SCRAM-based authentication will automatically be chosen inst=
ead.</span></blockquote><div><br></div><div>You can think of &quot;md5&quot=
; inside pg_hba.conf as &quot;md5 or better&quot;=C2=A0</div><div><br></div=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex">As a result, some users =
are able to connect, while others cannot.</blockquote><div><br></div><div>C=
an you expand on this? Nothing you have done should be preventing logins, a=
s far as I can tell.</div><div><br></div><div>Best solution: Upgrade everyo=
ne to scram, then change md5 to scram in pg_hba.conf and never look back.</=
div></div></blockquote><div><br></div><div>That requires setting the passwo=
rd to null and then recreating the password, no?=C2=A0 Otherwise IIRC, chan=
ging an md5 password leaves the new password also in md5 format.</div><div>=
</div></div><div><br></div><span class=3D"gmail_signature_prefix">-- </span=
><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &=
lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still ali=
ve.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></d=
iv>

--0000000000003090be0639807664--