Date: Sun, 12 Jan 2025 23:28:28 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@postgresql.org
Subject: Automatic upgrade of passwords from md5 to scram-sha256
Message-ID: <20250112222828.b36hpzm3ulfzlkws@hjp.at>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="4v6er5rh6xokgpbj"
Content-Disposition: inline
Archived-At: <https://www.postgresql.org/message-id/20250112222828.b36hpzm3ulfzlkws%40hjp.at>
Precedence: bulk


--4v6er5rh6xokgpbj
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

I have a PostgreSQL instance where the majority of the passwords is
still stored as MD5 hashes. I'm not particularly worried because they
are all randomly generated and should be reasonably secure against brute
force attacks even with a weak hash, and they're not that valuable
anyway, but it would still be nice if I could upgrade them to
SCRAM-SHA256.

The web framework Django will automatically and transparently rehash any
password with the currently preferred algorithm if it isn't stored that
way already.

Can PostgreSQL do that, too? (I haven't found anything)

If not, would this feature be of general interest?

Looking through chapter 53 of manual I think it would have to
implemented like this:

If the password for the user is stored as an MD5 hash, the server
replies to the startup message with an AuthenticationCleartextPassword
respnse to force the client to send the password in the clear
(obviously you only want to do that if the connection is TLS-encrypted
or otherwise safe from eavesdropping).

The client sends an PasswordMessage with the cleartext password.

The server first checks the password against the stored MD5 hash and
(assuming it's correct) then computes and stores the SCRAM-SHA256 hash, jus=
t as if the
user had issued an "alter user password" command. Finally it replies
with an AuthenticationOk message as normal.

The next time the client connects, the server will find and and use the
SCRAM-SHA256 hash.

This feature should only be enabled by a GUC.

Additional question: Do current clients (especially the ODBC client)
even support AuthenticationCleartextPassword by default?

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--4v6er5rh6xokgpbj
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmeEQgYACgkQ8g5IURL+
KF0CUw/8DPJ7NNlyw6ybSuZND5fSubAyt7nYWACwcJHdKBOeku8kQrem6Ntivedr
ml467K3jH3+Wb01CTNwfAgzdr4isTdA61JYKHZ4CCXF1lNYws1ZLWKbGTp0kvII6
oLkMm3PMyIaK/ffoOBYccxI83Ul6Qgt3ALEn+Xmc5M31Fp26RrsCxmRtPWgD6jnw
FaymtziNChy/X+kPm6M7fbBFV7ZlVBiXxRbMLdiAYmyrbuhvA/UsLPvbrjvYDiCi
bXCDdUvLR1a1TSfr4ETj093VOuQIQSOrpCjY7paW52zhnh2Esne24MlZicNaIG5f
claha4FbWrXQkFz8EwANz4lXRVO2uhNp5supBFLdu5oglv9jk8LsZlmt9Sbxl22y
hOD+UQHtxMHEMzao315mqvstNBmsdfGgyf/xyC5Utjwp2MPyIrYQTq1X8Dq7x79Y
tfr7whV740LxJWVjaET0J83rNY6U4oBQs9smowIMX/wzmhCJe+xHsRlCtL4AIeVK
7U4YZzz38OTxWLtda9u+UVPV0wPQn3JCfF13y+gHlVEXC8xHTT9BRmFaq0WhzryI
69eAy0GmElingL+7YopoOoLK9JnIWubBuk8YxkCsDH8H3Z4tkB7erYjEHWdjeNlL
ynFsN5Dw792oPRj9IJUFEK9Z+suk1dGjci2fYcQ5KA+avXn3M0Y=
=c3cB
-----END PGP SIGNATURE-----

--4v6er5rh6xokgpbj--