Date: Mon, 13 Jan 2025 01:17:20 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Automatic upgrade of passwords from md5 to scram-sha256
Message-ID: <20250113001720.vdwg2bygkt5gaa4c@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <20250112222828.b36hpzm3ulfzlkws@hjp.at>
 <372571.1736722760@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="fvmnly65ne5s6b6v"
Content-Disposition: inline
In-Reply-To: <372571.1736722760@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/20250113001720.vdwg2bygkt5gaa4c%40hjp.at>
Precedence: bulk


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

On 2025-01-12 17:59:20 -0500, Tom Lane wrote:
> "Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> > The web framework Django will automatically and transparently rehash any
> > password with the currently preferred algorithm if it isn't stored that
> > way already.
>=20
> Really?  That implies that the framework has access to the original
> cleartext password, which is a security fail already.

It's a server-side web framework, and it doesn't require JavaScript in
the browser. So the only way it can authenticate the user is by
receiving username and password in a POST request (except for HTTP Basic
or Digest authentication which are both worse, IMHO).

SCRAM could be implemented in an authentication module, it just needs a
SCRAM implementation in JavaScript which can be included in the login
page. Somebody has probably already implemented this, but it's not in
the core distribution.

Anyway, Django is just the inspiration for the idea.


> > Can PostgreSQL do that, too? (I haven't found anything)
>=20
> No.  The server has only the hashed password, it can't reconstruct
> the original.

But it could get the original during login.


> > 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).
>=20
> I think this idea is a nonstarter, TLS or not.  We're generally moving
> in the direction of never letting the server see cleartext passwords.

A way to transparently upgrade from MD5 to SCRAM would IMHO be useful
for that. Requesting the clear text password once is IMHO preferable to
being stuck with MD5 until the users decide (or can be forced) to change
their passwords (oh, and if you send an "alter user password" command
the server will also see the clear text password unless the client can
and does) compute the hash).

PostgreSQL's MD5 hash is, as far as I can see, password equivalent. You
don't actually need the original password, only the stored MD5
hash for a successful login:

concat('md5', md5(concat(md5(concat(password, username)), random-salt)))

md5(concat(password, username)) is stored in the pg_shadow table.

That's not good.


> It's already possible to configure libpq to refuse such requests
> (see require_auth parameter), although that hasn't been made the
> default.

If it's not the default there's a decent chance that the users haven't
changed it.

        hp

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

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

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmeEW4QACgkQ8g5IURL+
KF0JuRAAhkPztp3x3rEOWl4b4u0htJFrVa/0zZd/HDhf/PTe0PtaV/PNYS8xtDHN
kvCYQfZJUEm7EsNnUnrUbrXiB0pYPCpz/K5a+DdzixBe/ZFtKyARupk8CH2Q9pYj
WwP3jhpsko+r9HhFJdHK4QyEaapQ8Pvz46Sdtsg2JNwnKH/tleLkqJWD2dVXxY4e
3K+SN6ToZ33YDrWxUWTYquJCuJTrZVLDacM0NA63+e2+SY+sN9aQM+DAy5HCm7QW
xeUiZZ32DdeUjJ3+KQv29yGZXntHDcnE5MUJbg/34/tQo1nMO4zWWJYIYdvpp7En
1EU5M031QjaeXKHUfJ0whytmv/F7YyPXtMJiCHrNudCEC/+KgVDDCoCc7C7cLhBD
dDOjjStUVUpO4S0TyftbFv+V49EuNUSi8XBiRYM7EE0XgGXjVUpLpX00pFvVugW/
K7aRtChzAy/FJA5J2ilgJeewU8QrZW8lgejTchVKGBqUZE5LOqjumtuSqM5l6BMb
bpzBVuL3GmmJzRrYukW8xHL2TLJ6yKl7cK3F5rF9ztJFloEjxGuit0gCy0ASGStG
b/1ahHMvk1m3eWB1bZArVmL1x6bgdtOPeZI0ZdEC0iO2d7YWqr4mkv2mPKpqD9EY
VScjkHn7Nf3OlH1Ju5DNep2fWyGpeDEIiKpCVLyo+PokFUlyPjc=
=y8ih
-----END PGP SIGNATURE-----

--fvmnly65ne5s6b6v--