Horton
Menu

Thread

  1. Automatic upgrade of passwords from md5 to scram-sha256

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-01-12T22:28:28Z

    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, just 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

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

  2. Re: Automatic upgrade of passwords from md5 to scram-sha256

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-01-12T22:59:20Z

    "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.

Really?  That implies that the framework has access to the original
cleartext password, which is a security fail already.

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

No.  The server has only the hashed password, it can't reconstruct
the original.

> 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).

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.
It's already possible to configure libpq to refuse such requests
(see require_auth parameter), although that hasn't been made the
default.

			regards, tom lane

  3. Re: Automatic upgrade of passwords from md5 to scram-sha256

    Bruce Momjian <bruce@momjian.us> — 2025-01-12T23:37:29Z

    On Sun, Jan 12, 2025 at 05:59:20PM -0500, Tom Lane wrote:
> > 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).
> 
> 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.
> It's already possible to configure libpq to refuse such requests
> (see require_auth parameter), although that hasn't been made the
> default.

Agreed.  I think weakening the MD5 handshake to switch to a more secure
hash algorithm is unwise.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.

  4. Re: Automatic upgrade of passwords from md5 to scram-sha256

    Isaac Morland <isaac.morland@gmail.com> — 2025-01-13T00:15:57Z

    On Sun, 12 Jan 2025 at 17:59, Tom Lane <tgl@sss.pgh.pa.us> 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.
>
> Really?  That implies that the framework has access to the original
> cleartext password, which is a security fail already.


It happens upon user login. If the user's password is hashed with an old
algorithm, it is re-hashed during login when the Django application running
on the Web server has the password sent by the user:

https://docs.djangoproject.com/en/5.1/topics/auth/passwords/#password-upgrading

But of course this only works if the old method in use involves sending the
password to the server.

  5. Re: Automatic upgrade of passwords from md5 to scram-sha256

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-01-13T00:17:20Z

    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.
> 
> 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)
> 
> 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).
> 
> 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

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

  6. Re: Automatic upgrade of passwords from md5 to scram-sha256

    Joe Conway <mail@joeconway.com> — 2025-01-13T17:11:12Z

    On 1/12/25 17:59, 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.
> 
> Really?  That implies that the framework has access to the original
> cleartext password, which is a security fail already.
> 
>> Can PostgreSQL do that, too? (I haven't found anything)
> 
> No.  The server has only the hashed password, it can't reconstruct
> the original.
> 
>> 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).
> 
> 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.
> It's already possible to configure libpq to refuse such requests
> (see require_auth parameter), although that hasn't been made the
> default.

<hand-wavy-thought>
Given PQchangePassword[1] in pg17, I wonder if the next step could be to 
have libpq somehow know/detect that an algorithm change is needed and 
execute that (or some equivalent) from the client side? And presumably 
we could ask pgjdbc to implement something similar.
</hand-wavy-thought>

Joe

[1] 
https://www.postgresql.org/docs/17/libpq-misc.html#LIBPQ-PQCHANGEPASSWORD
-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

  7. Re: Automatic upgrade of passwords from md5 to scram-sha256

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-01-13T17:19:06Z

    On Sun, Jan 12, 2025 at 5:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
 [snip]

> 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.
> It's already possible to configure libpq to refuse such requests
> (see require_auth parameter), although that hasn't been made the
> default.
>

ALTER ROLE xxx WITH PASSWORD accepts hashed values, so a client with the
SCRAM-SHA algorithm could:
1. remember the password that was just used to log in,
2. generate the new hash,
3. send that as an ALTER ROLE statement.

Anything which shows up in the logs would be no different than when someone
types ALTER ROLE ... WITH PASSWORD from the psql prompt.

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

  8. Re: Automatic upgrade of passwords from md5 to scram-sha256

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-01-13T20:41:00Z

    On 2025-01-13 12:19:06 -0500, Ron Johnson wrote:
> On Sun, Jan 12, 2025 at 5:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  [snip]
> 
>     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.
>     It's already possible to configure libpq to refuse such requests
>     (see require_auth parameter), although that hasn't been made the
>     default.
> 
> 
> ALTER ROLE xxx WITH PASSWORD accepts hashed values, so a client with the
> SCRAM-SHA algorithm could:
> 1. remember the password that was just used to log in,
> 2. generate the new hash, 
> 3. send that as an ALTER ROLE statement.

Modifying the client to re-set the password is actually something I
thought about. There are some technical unknowns (e.g. is
PQencryptPasswordConn accessible through ODBC?) and some organisational
difficulties (e.g. can we get the customers to upgrade to the newest
version?), but I guess in our case it would be doable. But in general
changing every to client to upgrade the password doesn't seem feasible.
Unless maybe you are proposing that libpq should do that? That might
work, but it probably also shouldn't do it by default.

        hp


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

  9. Re: Automatic upgrade of passwords from md5 to scram-sha256

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-01-14T01:09:41Z

    On Mon, Jan 13, 2025 at 3:41 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

> On 2025-01-13 12:19:06 -0500, Ron Johnson wrote:
> > On Sun, Jan 12, 2025 at 5:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >  [snip]
> >
> >     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.
> >     It's already possible to configure libpq to refuse such requests
> >     (see require_auth parameter), although that hasn't been made the
> >     default.
> >
> >
> > ALTER ROLE xxx WITH PASSWORD accepts hashed values, so a client with the
> > SCRAM-SHA algorithm could:
> > 1. remember the password that was just used to log in,
> > 2. generate the new hash,
> > 3. send that as an ALTER ROLE statement.
>
> Modifying the client to re-set the password is actually something I
> thought about. There are some technical unknowns (e.g. is
> PQencryptPasswordConn accessible through ODBC?) and some organisational
> difficulties (e.g. can we get the customers to upgrade to the newest
> version?), but I guess in our case it would be doable. But in general
> changing every to client to upgrade the password doesn't seem feasible.
> Unless maybe you are proposing that libpq should do that? That might
> work, but it probably also shouldn't do it by default.
>

That seems to me to be the fastest way to get the feature out to users.
(JDBC would also need it.)

Then clients like psql, pgAdmin, etc would need to add those calls.

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