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: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 9 Jul 2025 13:02:06 -0400
Message-ID: <CAKAnmmLfYwA+A6_A7fHbFOpYLmzs-7wjpFfR32b7c3qJOTxscg@mail.gmail.com>
Subject: Re: Password Encryption and Connection Issues
To: =?UTF-8?Q?Alpaslan_AKDA=C4=9E?= <alpaslanakdag@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008246020639820bb4"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLfYwA%2BA6_A7fHbFOpYLmzs-7wjpFfR32b7c3qJOTxscg%40mail.gmail.com>
Precedence: bulk

--0000000000008246020639820bb4
Content-Type: text/plain; charset="UTF-8"

> 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

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote gmail=
_quote_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div di=
r=3D"ltr"><div>Best solution: Upgrade everyone to scram, then change md5 to=
 scram in pg_hba.conf and never look back.</div></div></blockquote><div><br=
></div><div>To expand more on the &quot;upgrade everyone to scram&quot;, th=
at means force all users to set a new password while using scram (which sho=
uld be the default). You can do it yourself by getting a list of users and =
changing their passwords inside psql:</div><div><br></div><div>-- List all =
users still stuck in md5-land:</div><div>greg=3D# select=C2=A0rolname=C2=A0=
from pg_authid where=C2=A0rolpassword=C2=A0~ &#39;^md5&#39;</div><div>alice=
</div><div>eve</div><div>mallory</div><div>(3 rows)</div><div><br></div><di=
v>-- Just in case, force use of scram</div><div>greg=3D# set password_encry=
ption =3D &#39;scram-sha-256&#39;;</div><div>SET</div><div><br></div><div>-=
- Reset each user&#39;s password to some strong password of your choice:</d=
iv><div>greg=3D# \password alice</div><div>Enter new password for user &quo=
t;alice&quot;:<br>Enter it again:</div><div><br></div><div>-- Repeat the ab=
ove until this query returns no rows:</div><div><div>select=C2=A0rolname=C2=
=A0from pg_authid where=C2=A0rolpassword=C2=A0~ &#39;^md5&#39;</div></div><=
div><br></div><div>Cheers,</div><div>Greg</div></div><div dir=3D"ltr" class=
=3D"gmail_signature"><div dir=3D"ltr"><div><br></div><div>--</div><div>Crun=
chy Data - <a href=3D"https://www.crunchydata.com" target=3D"_blank">https:=
//www.crunchydata.com</a></div><div>Enterprise Postgres Software Products &=
amp; Tech Support</div><div><br></div></div></div></div>

--0000000000008246020639820bb4--