Thread

Commits

Deprecate MD5 passwords.
- db6a4a985bc0 18.0 landed

sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-10-09T19:55:16Z

In this message, I propose a multi-year, incremental approach to remove MD5
password support from Postgres.

The problems with MD5 password hashes in Postgres are well-understood, so I
won't discuss them in too much detail here.  But suffice it to say that MD5
has been considered to be unsuitable for use as a cryptographic hash
algorithm for some time [0], and cracking MD5-hashed passwords is trivial
on modern hardware [1].  Furthermore, MD5 password hashes in Postgres are
vulnerable to pass-the-hash attacks [2] [3], i.e., knowing the username and
hashed password is sufficient to authenticate.

The SCRAM-SHA-256 method added in v10 is not subject to these problems and
AFAIK is generally considered far superior.  Since v14, this method has
been the default for the password_encryption parameter, which determines
the algorithm to use to store new passwords on disk (unless the password
has already been hashed by the client, as is recommended).

Given there is a battle-tested alternative to MD5, I propose we take the
following steps.  I am not wedded to the exact details, but I feel that
this would be a reasonably conservative path forward.

 1.  In v18, continue to support MD5 passwords, but place several notes in
     the documentation and release notes that unambiguously indicate that
     MD5 password support is deprecated and will be removed in a future
     release.

 2.  In v19, allow upgrading with MD5 passwords and allow authenticating
     with them, but disallow creating new ones (i.e., restrict/remove
     password_encryption and don't allow setting pre-hashed MD5 passwords).

 3.  In v20, allow upgrading with MD5 passwords, but disallow using them
     for authentication.  Users would only be able to update these
     passwords to SCRAM-SHA-256 after upgrading.

 4.  In v21, disallow upgrading with MD5 passwords.  At this point, there
     should be no remaining MD5 password support in Postgres.

With this plan, the first version with all MD5 password support removed
would be released in 2028.  Considering SCRAM-SHA-256 was first introduced
in 2017 and has been the default for password_encryption since 2021, users
will have had several years to migrate.

Thoughts?

[0] https://en.wikipedia.org/wiki/MD5#Security
[1] https://www.postgresql.org/docs/devel/pgcrypto.html#PGCRYPTO-HASH-SPEED-TABLE
[2] https://hashcat.net/misc/postgres-pth/postgres-pth.pdf
[3] https://www.postgresql.org/docs/devel/auth-password.html

-- 
nathan

Re: sunsetting md5 password support

Jelte Fennema-Nio <postgres@jeltef.nl> — 2024-10-09T20:30:15Z

On Wed, 9 Oct 2024 at 21:55, Nathan Bossart <nathandbossart@gmail.com> wrote:
> In this message, I propose a multi-year, incremental approach to remove MD5
> password support from Postgres.

+many for the general idea

I think it makes sense to also remove the "password" authentication
option while we're at it (this can currently be used with SCRAM stored
passwords).

>  3.  In v20, allow upgrading with MD5 passwords, but disallow using them
>      for authentication.  Users would only be able to update these
>      passwords to SCRAM-SHA-256 after upgrading.

This step sounds like a great way to stress out DBAs who don't read
the release notes carefully. I imagine the following situation: DBA
upgrades to v20. The upgrades succeed, but suddenly half the
applications cannot connect.

It seems much nicer to have the upgrade fail hard in the check phase,
to force all users and thus applications to upgrade their hash to the
new format, i.e. go right from step 2 to step 4.

> Thoughts?

I don't know what a reasonable deprecation cycle is. But I believe all
clients have supported SCRAM auth for quite a long time, so honestly
even disallowing upgrading with md5 passwords in v18/v19 might be
acceptable. I guess my main question is: Who's life do we make easier
by postponing the removal? Is the pain going to be significantly less
for some people by doing a spread out deprecation, instead of just
saying: We stop supporting it next release.

If the pain is going to be the same amount for users anyway, only
postponed a few years, then I don't see much of a reason to wait.

Side-thought: What about the deprecation cycle for md5/password auth
for libpq? I think we'd want to keep it at least 5 years after we
remove it from the server. Probably even longer. But I think we at
least might want to change the default of require_auth to
"!md5,!password" after 5 years.

On Wed, 9 Oct 2024 at 21:55, Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> In this message, I propose a multi-year, incremental approach to remove MD5
> password support from Postgres.
>
> The problems with MD5 password hashes in Postgres are well-understood, so I
> won't discuss them in too much detail here.  But suffice it to say that MD5
> has been considered to be unsuitable for use as a cryptographic hash
> algorithm for some time [0], and cracking MD5-hashed passwords is trivial
> on modern hardware [1].  Furthermore, MD5 password hashes in Postgres are
> vulnerable to pass-the-hash attacks [2] [3], i.e., knowing the username and
> hashed password is sufficient to authenticate.
>
> The SCRAM-SHA-256 method added in v10 is not subject to these problems and
> AFAIK is generally considered far superior.  Since v14, this method has
> been the default for the password_encryption parameter, which determines
> the algorithm to use to store new passwords on disk (unless the password
> has already been hashed by the client, as is recommended).
>
> Given there is a battle-tested alternative to MD5, I propose we take the
> following steps.  I am not wedded to the exact details, but I feel that
> this would be a reasonably conservative path forward.
>
>  1.  In v18, continue to support MD5 passwords, but place several notes in
>      the documentation and release notes that unambiguously indicate that
>      MD5 password support is deprecated and will be removed in a future
>      release.
>
>  2.  In v19, allow upgrading with MD5 passwords and allow authenticating
>      with them, but disallow creating new ones (i.e., restrict/remove
>      password_encryption and don't allow setting pre-hashed MD5 passwords).
>
>  3.  In v20, allow upgrading with MD5 passwords, but disallow using them
>      for authentication.  Users would only be able to update these
>      passwords to SCRAM-SHA-256 after upgrading.
>
>  4.  In v21, disallow upgrading with MD5 passwords.  At this point, there
>      should be no remaining MD5 password support in Postgres.
>
> With this plan, the first version with all MD5 password support removed
> would be released in 2028.  Considering SCRAM-SHA-256 was first introduced
> in 2017 and has been the default for password_encryption since 2021, users
> will have had several years to migrate.
>
> Thoughts?
>
> [0] https://en.wikipedia.org/wiki/MD5#Security
> [1] https://www.postgresql.org/docs/devel/pgcrypto.html#PGCRYPTO-HASH-SPEED-TABLE
> [2] https://hashcat.net/misc/postgres-pth/postgres-pth.pdf
> [3] https://www.postgresql.org/docs/devel/auth-password.html
>
> --
> nathan
>
>

Re: sunsetting md5 password support

Greg Sabino Mullane <htamfids@gmail.com> — 2024-10-09T20:31:01Z

Big +1 to the idea, but it's not going to be pretty; there is a lot of
baked-in MD5 stuff around.



>  2.  In v19, allow upgrading with MD5 passwords and allow authenticating
>      with them, but disallow creating new ones (i.e., restrict/remove
>      password_encryption and don't allow setting pre-hashed MD5 passwords).
>

Certainly not remove it, that would break lots of things. Perhaps one
release with a strong warning when md5 is used, that cannot be disabled,
then disallow new ones?


>  3.  In v20, allow upgrading with MD5 passwords, but disallow using them
> for authentication.


Again, maybe a release that complains real loudly but still allows it?


>  4.  In v21, disallow upgrading with MD5 passwords.


You mean having pg_upgrade refuse to go on? Or maybe have it empty the
passwords out?

Cheers,
Greg

Re: sunsetting md5 password support

Jonathan S. Katz <jkatz@postgresql.org> — 2024-10-09T20:44:43Z

On 10/9/24 3:55 PM, Nathan Bossart wrote:
> In this message, I propose a multi-year, incremental approach to remove MD5
> password support from Postgres.

+100; thanks for a concrete proposal. Cutting out the "well-understood" 
problems bit.>
> Given there is a battle-tested alternative to MD5, I propose we take the
> following steps.  I am not wedded to the exact details, but I feel that
> this would be a reasonably conservative path forward.
> 
>   1.  In v18, continue to support MD5 passwords, but place several notes in
>       the documentation and release notes that unambiguously indicate that
>       MD5 password support is deprecated and will be removed in a future
>       release.

+1. Should we also add something in the logs? I've mixed feelings on 
this, as this could end up leaking information about what auth methods 
are used. But - maybe we can pick and choose what we log on, e.g. if 
"md5" is set as an auth method in pg_hba.conf, we complain at 
pg_hba.conf load/reload time that this is being deprecated.

>   2.  In v19, allow upgrading with MD5 passwords and allow authenticating
>       with them, but disallow creating new ones (i.e., restrict/remove
>       password_encryption and don't allow setting pre-hashed MD5 passwords).
> 
>   3.  In v20, allow upgrading with MD5 passwords, but disallow using them
>       for authentication.  Users would only be able to update these
>       passwords to SCRAM-SHA-256 after upgrading

>   4.  In v21, disallow upgrading with MD5 passwords.  At this point, there
>       should be no remaining MD5 password support in Postgres.

I wonder if we can compress this down into the v20 release. With v18 
(2025), we've already had a year of warning (and I think as soon as we 
commit to a plan, we start broadcasting it, so maybe more than a year). 
With v19 (2026), we've started adding the inconveniences, and there's a 
last chance to flip the password. With v20 (2027), we can then block 
upgrades until they're rehashed. That's effectively 3 years from today - 
we can also say it's within the 10 years since SCRAM was introduced, 
which somewhat aligns with other deprecation timelines :)

Given the problems with the md5 method, I think we can be carefully 
aggressive here with the deprecation, particularly given there's overall 
wide support for SCRAM.

(The larger question, which I will pose at least to think on, is how do 
we handle any future password method deprecations, e.g. say 
SCRAM-SHA-512 comes out and we want to remove SCRAM-SHA-256? Not an 
issue today, but we'd likely want to have a similar process in place).

Jonathan

[1] https://wiki.postgresql.org/wiki/List_of_drivers

Re: sunsetting md5 password support

Jacob Champion <jacob.champion@enterprisedb.com> — 2024-10-09T22:14:03Z

On Wed, Oct 9, 2024 at 1:44 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> On 10/9/24 3:55 PM, Nathan Bossart wrote:
> >   1.  In v18, continue to support MD5 passwords, but place several notes in
> >       the documentation and release notes that unambiguously indicate that
> >       MD5 password support is deprecated and will be removed in a future
> >       release.
>
> +1. Should we also add something in the logs?

I also think we should start logging warnings as soon as we agree to
deprecate MD5.

> I've mixed feelings on
> this, as this could end up leaking information about what auth methods
> are used.

Leak it to whom? The client and server both know MD5 is being used.

> >   2.  In v19, allow upgrading with MD5 passwords and allow authenticating
> >       with them, but disallow creating new ones (i.e., restrict/remove
> >       password_encryption and don't allow setting pre-hashed MD5 passwords).
> >
> >   3.  In v20, allow upgrading with MD5 passwords, but disallow using them
> >       for authentication.  Users would only be able to update these
> >       passwords to SCRAM-SHA-256 after upgrading
>
> >   4.  In v21, disallow upgrading with MD5 passwords.  At this point, there
> >       should be no remaining MD5 password support in Postgres.
>
> I wonder if we can compress this down into the v20 release.

I'd like an accelerated schedule for this too. Your three-step
"complain, restrict, disallow", with strict pg_upgrade failure as part
of step 3, seems right to me.

> (The larger question, which I will pose at least to think on, is how do
> we handle any future password method deprecations, e.g. say
> SCRAM-SHA-512 comes out and we want to remove SCRAM-SHA-256? Not an
> issue today, but we'd likely want to have a similar process in place).

In general I like the three-step method for the server side. The
client side needs to be considered separately, though, like Jelte
pointed out; we have wire compatibility to maintain.

(For the exact example you provided, I think we'd only need a similar
process if the -256 variant turns out to be broken. Otherwise, the
cost of maintaining -256 and -512 together is probably going to be
very close to the cost of maintaining -512 alone, thanks to the past
work generalizing the hashing code. And there may be
security/performance tradeoffs for DBAs to make.)

Thanks,
--Jacob

Re: sunsetting md5 password support

Heikki Linnakangas <hlinnaka@iki.fi> — 2024-10-09T23:11:53Z

On 09/10/2024 22:55, Nathan Bossart wrote:
> In this message, I propose a multi-year, incremental approach to remove MD5
> password support from Postgres.

+1

>   2.  In v19, allow upgrading with MD5 passwords and allow authenticating
>       with them, but disallow creating new ones (i.e., restrict/remove
>       password_encryption and don't allow setting pre-hashed MD5 passwords).

This is a bit weird state. What exactly is "upgrading"? I guess you mean 
pg_upgrade, but lots of people use pg_dump & restore or logical 
replication or something else entirely for upgrading. That's 
indistinguishable from setting a pre-hashed MD5 password.

I think it's bad if you cannot pg_dump & restore your database.

>   3.  In v20, allow upgrading with MD5 passwords, but disallow using them
>       for authentication.  Users would only be able to update these
>       passwords to SCRAM-SHA-256 after upgrading.

This step makes more sense. Notably, if we disallow using the passwords 
for authentication, there would be little harm in still allowing them to 
be dumped & restored.

It seems pointless though. What's the point of "upgrading" with the MD5 
passwords, if you can't use them? You might as well set all the MD5 
passwords to null.

My feeling is that it would be less confusing to users to just disallow 
md5 passwords in one release. I'm not sure these intermediate steps are 
really doing anyone any favors.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

Re: sunsetting md5 password support

Christoph Moench-Tegeder <cmt@burggraben.net> — 2024-10-10T12:27:04Z

## Heikki Linnakangas (hlinnaka@iki.fi):

> This is a bit weird state. What exactly is "upgrading"? I guess you
> mean pg_upgrade, but lots of people use pg_dump & restore or logical
> replication or something else entirely for upgrading. That's
> indistinguishable from setting a pre-hashed MD5 password.

Password hashes are only in the "globals" dump (pg_dumpall -r/-g),
not in standard pg_dump (and I don't see anything about passwords
in the binary-upgrade mode of pg_dump).
Finally it might be a good thing that we separated data and roles.
Maybe that even is a plan for pg_upgrade: understand md5-password
when they appear in pg_authid, but do not apply special treatment
in CREATE ROLE/ALTER ROLE, thus preventing the setting of md5
password as pre-hashed passwords.

Regards,
Christoph

-- 
Spare Space.

Re: sunsetting md5 password support

Andrew Dunstan <andrew@dunslane.net> — 2024-10-10T12:40:18Z

On 2024-10-09 We 7:11 PM, Heikki Linnakangas wrote:
> On 09/10/2024 22:55, Nathan Bossart wrote:
>> In this message, I propose a multi-year, incremental approach to 
>> remove MD5
>> password support from Postgres.
>
> +1
>
>>   2.  In v19, allow upgrading with MD5 passwords and allow 
>> authenticating
>>       with them, but disallow creating new ones (i.e., restrict/remove
>>       password_encryption and don't allow setting pre-hashed MD5 
>> passwords).
>
> This is a bit weird state. What exactly is "upgrading"? I guess you 
> mean pg_upgrade, but lots of people use pg_dump & restore or logical 
> replication or something else entirely for upgrading. That's 
> indistinguishable from setting a pre-hashed MD5 password.
>
> I think it's bad if you cannot pg_dump & restore your database.


Hmm, yeah. It would be easy enough to prevent MD5 passwords in things 
like CREATE ROLE / ALTER ROLE, but harder to check for MD5 if there are 
direct updates to pg_authid. Maybe we need to teach pg_dumpall a way to 
do that as a workaround?


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Re: sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-10-10T20:13:30Z

On Thu, Oct 10, 2024 at 02:11:53AM +0300, Heikki Linnakangas wrote:
> My feeling is that it would be less confusing to users to just disallow md5
> passwords in one release. I'm not sure these intermediate steps are really
> doing anyone any favors.

As I'm reading the various responses in this thread, I do find myself
leaning in this direction.  My intent with the incremental approach was to
provide gentle reminders to migrate for a few years before removing support
completely, but I suppose there will always be a subset of users that will
wait until we actually follow through.  If we went this route, we could
still do step 1 (add deprecation notices), but there would just be one more
step along the lines of "after X years, remove all support."  (Or maybe we
would remove server support after X years and then remove libpq support
after Y more years.)

In general, it seems like folks are generally onboard with removing MD5
password support.  For v18, the only thing I'm hoping to accomplish is to
get the deprecation notices added, so I will start writing a patch for
that.  Perhaps we should also consider adding WARNINGs whenever folks use
MD5 passwords in any fashion (with a corresponding GUC to turn those off).

-- 
nathan

Re: sunsetting md5 password support

Bruce Momjian <bruce@momjian.us> — 2024-10-10T21:03:37Z

On Wed, Oct  9, 2024 at 10:30:15PM +0200, Jelte Fennema-Nio wrote:
> On Wed, 9 Oct 2024 at 21:55, Nathan Bossart <nathandbossart@gmail.com> wrote:
> > In this message, I propose a multi-year, incremental approach to remove MD5
> > password support from Postgres.
> 
> +many for the general idea
> 
> I think it makes sense to also remove the "password" authentication
> option while we're at it (this can currently be used with SCRAM stored
> passwords).

I remember "password" as being recommended for SSL connections where
there is no risk of the password contents being seen.

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

  When a patient asks the doctor, "Am I going to die?", he means 
  "Am I going to die soon?"

Re: sunsetting md5 password support

Heikki Linnakangas <hlinnaka@iki.fi> — 2024-10-10T21:45:19Z

On 11/10/2024 00:03, Bruce Momjian wrote:
> On Wed, Oct  9, 2024 at 10:30:15PM +0200, Jelte Fennema-Nio wrote:
>> On Wed, 9 Oct 2024 at 21:55, Nathan Bossart <nathandbossart@gmail.com> wrote:
>>> In this message, I propose a multi-year, incremental approach to remove MD5
>>> password support from Postgres.
>>
>> +many for the general idea
>>
>> I think it makes sense to also remove the "password" authentication
>> option while we're at it (this can currently be used with SCRAM stored
>> passwords).
> 
> I remember "password" as being recommended for SSL connections where
> there is no risk of the password contents being seen.

I wouldn't recommend it if SCRAM is available, but yeah, with TLS and 
sslmode=verify-full, it's secure enough.

Note that some authentication methods like LDAP and Radius use 
"password" authentication on the wire.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

Re: sunsetting md5 password support

Jelte Fennema-Nio <postgres@jeltef.nl> — 2024-10-10T21:59:10Z

On Thu, 10 Oct 2024 at 23:45, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> I wouldn't recommend it if SCRAM is available, but yeah, with TLS and
> sslmode=verify-full, it's secure enough.

Agreed, I'd definitely still recommend SCRAM over password. A big
downside of "password" auth over TLS is that plaintext passwords get
to the server, so a coredump would contain these passwords.

Also, I wanted to call out that SCRAM still needs sslmode=verify-full
to be fully secure. With the SCRAM hash of the server, together with a
MITM between client and server, an attacker can impersonate the client
without the client or server realizing. PgBouncer actually does this:
https://www.pgbouncer.org/config.html#limitations

Re: sunsetting md5 password support

Jesper Pedersen <jesper.pedersen@comcast.net> — 2024-10-10T22:00:20Z

On 10/10/24 5:45 PM, Heikki Linnakangas wrote:
> On 11/10/2024 00:03, Bruce Momjian wrote:
>> On Wed, Oct  9, 2024 at 10:30:15PM +0200, Jelte Fennema-Nio wrote:
>>> On Wed, 9 Oct 2024 at 21:55, Nathan Bossart 
>>> <nathandbossart@gmail.com> wrote:
>>>> In this message, I propose a multi-year, incremental approach to 
>>>> remove MD5
>>>> password support from Postgres.
>>>
>>> +many for the general idea
>>>
>>> I think it makes sense to also remove the "password" authentication
>>> option while we're at it (this can currently be used with SCRAM stored
>>> passwords).
>>
>> I remember "password" as being recommended for SSL connections where
>> there is no risk of the password contents being seen.
> 
> I wouldn't recommend it if SCRAM is available, but yeah, with TLS and 
> sslmode=verify-full, it's secure enough.
> 
> Note that some authentication methods like LDAP and Radius use 
> "password" authentication on the wire.
> 

Please, deprecate - aka remove - old methods.

All client libraries have caught up, and if they havn't then it their 
issue not Core.

+1.

Best regards,
  Jesper

Re: sunsetting md5 password support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-10-10T22:28:26Z

Andrew Dunstan <andrew@dunslane.net> writes:
> Hmm, yeah. It would be easy enough to prevent MD5 passwords in things 
> like CREATE ROLE / ALTER ROLE, but harder to check for MD5 if there are 
> direct updates to pg_authid. Maybe we need to teach pg_dumpall a way to 
> do that as a workaround?

That seems like a pretty awful idea.  Having dump scripts that
perform direct updates on pg_authid would lock us into supporting
the current physical representation (ie that pg_authid is in fact
a table with such-and-such columns) forever.  Not to mention that
no such script could be restored with anything less than full
superuser privileges.  And in return we're getting what exactly?

On the whole I agree with Heikki's comment that we should just
do it (disallow MD5, full stop) whenever we feel that enough
time has passed.  These intermediate states are mostly going to
add headaches.  Maybe we could do something with an intermediate
release that just emits warnings, without any feature changes.

			regards, tom lane

Re: sunsetting md5 password support

Daniel Gustafsson <daniel@yesql.se> — 2024-10-10T22:34:01Z

> On 11 Oct 2024, at 00:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> On the whole I agree with Heikki's comment that we should just
> do it (disallow MD5, full stop) whenever we feel that enough
> time has passed.  These intermediate states are mostly going to
> add headaches.  Maybe we could do something with an intermediate
> release that just emits warnings, without any feature changes.

+1, warnings and ample documentation for how to perform the migration (and why,
I'm sure it will come as a surprise to *many*) is likely our best investment.
That coupled with a well communicated point in time for when MD5 goes away with
notices in all release notes up until that point.

--
Daniel Gustafsson

Re: sunsetting md5 password support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-10-10T22:39:35Z

Jesper Pedersen <jesper.pedersen@comcast.net> writes:
> On 10/10/24 5:45 PM, Heikki Linnakangas wrote:
>> Note that some authentication methods like LDAP and Radius use 
>> "password" authentication on the wire.

> Please, deprecate - aka remove - old methods.
> All client libraries have caught up, and if they havn't then it their 
> issue not Core.

It's not the libraries that are the problem.  It's the users that
want to use these auth methods --- perhaps even are required to
by dubiously-well-thought-out corporate policies.

			regards, tom lane

Re: sunsetting md5 password support

Laurenz Albe <laurenz.albe@cybertec.at> — 2024-10-11T06:50:49Z

On Thu, 2024-10-10 at 18:39 -0400, Tom Lane wrote:
> Jesper Pedersen <jesper.pedersen@comcast.net> writes:
> > On 10/10/24 5:45 PM, Heikki Linnakangas wrote:
> > > Note that some authentication methods like LDAP and Radius use 
> > > "password" authentication on the wire.
> 
> > Please, deprecate - aka remove - old methods.
> > All client libraries have caught up, and if they havn't then it their 
> > issue not Core.
> 
> It's not the libraries that are the problem.  It's the users that
> want to use these auth methods --- perhaps even are required to
> by dubiously-well-thought-out corporate policies.

A voice from the field: I know at least one application out there
(that is used by more than one customer) that implemented the line
protocol by itself, back in the days when "crypt" authentication still
existed.  So they support "crypt" and "password", and now that
PostgreSQL has removed "crypt", the users are stuck with "password"...

Actually, that may be a good reason to deprecate "password", because
then the vendor might get motivated to remedy that malady.  On the other
hand, you can expect some protest...

Yours,
Laurenz Albe

Re: sunsetting md5 password support

Andrew Dunstan <andrew@dunslane.net> — 2024-10-11T13:47:58Z

On 2024-10-10 Th 6:28 PM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> Hmm, yeah. It would be easy enough to prevent MD5 passwords in things
>> like CREATE ROLE / ALTER ROLE, but harder to check for MD5 if there are
>> direct updates to pg_authid. Maybe we need to teach pg_dumpall a way to
>> do that as a workaround?
> That seems like a pretty awful idea.  Having dump scripts that
> perform direct updates on pg_authid would lock us into supporting
> the current physical representation (ie that pg_authid is in fact
> a table with such-and-such columns) forever.  Not to mention that
> no such script could be restored with anything less than full
> superuser privileges.  And in return we're getting what exactly?


Well, I think if we keep a sort of half way house where we continue to 
allow existing md5 passwords we'd have to do some ugly things. So ...


>
> On the whole I agree with Heikki's comment that we should just
> do it (disallow MD5, full stop) whenever we feel that enough
> time has passed.  These intermediate states are mostly going to
> add headaches.  Maybe we could do something with an intermediate
> release that just emits warnings, without any feature changes.
>
> 			


I also agree with this.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Re: sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-10-11T21:36:27Z

On Fri, Oct 11, 2024 at 09:47:58AM -0400, Andrew Dunstan wrote:
> On 2024-10-10 Th 6:28 PM, Tom Lane wrote:
>> On the whole I agree with Heikki's comment that we should just
>> do it (disallow MD5, full stop) whenever we feel that enough
>> time has passed.  These intermediate states are mostly going to
>> add headaches.  Maybe we could do something with an intermediate
>> release that just emits warnings, without any feature changes.
> 
> I also agree with this.

Here is a first attempt at a patch for marking MD5 passwords as deprecated.
It's quite bare-bones at the moment, so I anticipate future revisions will
add more content.  Besides sprinkling several deprecation notices
throughout the documentation, this patch teaches CREATE ROLE and ALTER ROLE
to emit warnings when setting MD5 passwords.  A new GUC named
md5_password_warnings can be set to "off" to disable these warnings.  I
considered adding even more warnings (e.g., when authenticating), but I
felt that would be far too noisy.

-- 
nathan

Re: sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-10-16T15:30:11Z

On Fri, Oct 11, 2024 at 04:36:27PM -0500, Nathan Bossart wrote:
> Here is a first attempt at a patch for marking MD5 passwords as deprecated.
> It's quite bare-bones at the moment, so I anticipate future revisions will
> add more content.  Besides sprinkling several deprecation notices
> throughout the documentation, this patch teaches CREATE ROLE and ALTER ROLE
> to emit warnings when setting MD5 passwords.  A new GUC named
> md5_password_warnings can be set to "off" to disable these warnings.  I
> considered adding even more warnings (e.g., when authenticating), but I
> felt that would be far too noisy.

In v2, I've added an entry for the new md5_password_warnings GUC to the
documentation, and I've simplified the passwordcheck test changes a bit.

-- 
nathan

Re: sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-10-26T15:55:44Z
```
rebased

-- 
nathan
```

Re: sunsetting md5 password support

Greg Sabino Mullane <htamfids@gmail.com> — 2024-10-28T20:21:29Z

On Sat, Oct 26, 2024 at 11:55 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> rebased
>

Patch applied without issue and looks good to me.

Cheers,
Greg

Re: sunsetting md5 password support

Jim Nasby <jnasby@upgrade.com> — 2024-10-28T21:10:29Z

> On Oct 28, 2024, at 3:21 PM, Greg Sabino Mullane <htamfids@gmail.com> wrote:
> 
> On Sat, Oct 26, 2024 at 11:55 AM Nathan Bossart <nathandbossart@gmail.com <mailto:nathandbossart@gmail.com>> wrote:
>> rebased
> 
> Patch applied without issue and looks good to me.

Patch itself looks good, but it does leave me wondering if cleartext should also be deprecated?

Might also be worth mentioning deprecation in pg_hba.conf.

Re: sunsetting md5 password support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-10-28T21:24:02Z

Jim Nasby <jnasby@upgrade.com> writes:
> Patch itself looks good, but it does leave me wondering if cleartext should also be deprecated?

Not much point unless we also deprecate all of the other auth methods
that require cleartext password transmission, which from a quick
scan include PAM, BSD, LDAP, and RADIUS.  Seems unlikely to fly.

In any case, I don't think this is about password security per se.
It's more about deprecating a method that might look like it's
secure but isn't.  In the case of the cleartext-password methods,
it's obvious that you'd better use SSL or GSS encryption if you
want your password hidden from network tapping.

I don't recall how in-your-face we are about that point, but
certainly the docs need to be up front about it, and probably
make the point explicitly with respect to the four methods
listed above.

			regards, tom lane

Re: sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-10-29T01:06:07Z

On Mon, Oct 28, 2024 at 04:10:29PM -0500, Jim Nasby wrote:
> Patch itself looks good, but it does leave me wondering if cleartext
> should also be deprecated?

I see that Tom has already chimed in on this point.  In any case, this is
probably a topic for another thread.

> Might also be worth mentioning deprecation in pg_hba.conf.

Yeah.  I vaguely recall waffling on whether to add one there, and for
whatever reason, I decided against it.  I've added it in v4.

-- 
nathan

Re: sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-11-19T23:05:15Z

I've gotten a couple of reviews on this patch, and the thread has been
quiet for a while now, so I'm considering committing this soon.  There is
still plenty of time to make any adjustments that are suggested
post-commit.

-- 
nathan

Re: sunsetting md5 password support

Greg Sabino Mullane <htamfids@gmail.com> — 2024-11-20T00:29:27Z

I just took a fresh look at / compiled this patch, and it all works as
advertised. My one minor nit is this hint:

HINT:  Refer to the PostgreSQL documentation for details about migrating to
another password type.

We don't really have that in the docs, as near as I can tell, the closest
is 20.5 which says "make all users set new passwords, and change the
authentication method specifications in pg_hba.conf to scram-sha-256."
Maybe that's enough?

Cheers,
Greg

Re: sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-11-20T01:55:04Z

On Tue, Nov 19, 2024 at 07:29:27PM -0500, Greg Sabino Mullane wrote:
> I just took a fresh look at / compiled this patch, and it all works as
> advertised. My one minor nit is this hint:
> 
> HINT:  Refer to the PostgreSQL documentation for details about migrating to
> another password type.
> 
> We don't really have that in the docs, as near as I can tell, the closest
> is 20.5 which says "make all users set new passwords, and change the
> authentication method specifications in pg_hba.conf to scram-sha-256."
> Maybe that's enough?

That was my initial thinking.  I think we have a few other options:

* Expand the documentation.  Perhaps we could add a step-by-step guide for
  migrating to SCRAM-SHA-256 since more users will need to do so when MD5
  password support is removed.
* Remove the hint.  It's arguably doing little more than pointing out the
  obvious, and it doesn't actually tell users where in the documentation to
  look for this information, anyway.
* Both of the above.

WDYT?

-- 
nathan

Re: sunsetting md5 password support

Greg Sabino Mullane <htamfids@gmail.com> — 2024-11-20T15:56:11Z

On Tue, Nov 19, 2024 at 8:55 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> * Expand the documentation.  Perhaps we could add a step-by-step guide
> for migrating to SCRAM-SHA-256 since more users will need to do so when
> MD5 password support is removed.
> * Remove the hint.  It's arguably doing little more than pointing out the
> obvious, and it doesn't actually tell users where in the documentation
> to look for this information, anyway.
>

I think both ideally, but maybe just the hint removal for this patch?

On the other hand, "change your password and update pg_hba.conf" is pretty
much all you need, so not sure how detailed we want to get. :)

Cheers,
Greg

Re: sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-11-20T16:33:39Z

On Wed, Nov 20, 2024 at 10:56:11AM -0500, Greg Sabino Mullane wrote:
> On Tue, Nov 19, 2024 at 8:55 PM Nathan Bossart <nathandbossart@gmail.com>
> wrote:
> 
>> * Expand the documentation.  Perhaps we could add a step-by-step guide
>> for migrating to SCRAM-SHA-256 since more users will need to do so when
>> MD5 password support is removed.
>> * Remove the hint.  It's arguably doing little more than pointing out the
>> obvious, and it doesn't actually tell users where in the documentation
>> to look for this information, anyway.
>>
> 
> I think both ideally, but maybe just the hint removal for this patch?
> 
> On the other hand, "change your password and update pg_hba.conf" is pretty
> much all you need, so not sure how detailed we want to get. :)

After thinking about this some more, I'm actually finding myself leaning
towards leaving the hint and potentially adding more detail to the
documentation as a follow-up patch.  While the hint arguably points out the
obvious, it should at least nudge users in the right direction instead of
just telling them to stop using MD5 passwords.  I've always found it
incredibly frustrating when something is marked deprecated but there's zero
information about what to do instead.

I also see a few existing cases where we refer users to the documentation,
so it's not without precedent.

-- 
nathan

Re: sunsetting md5 password support

Greg Sabino Mullane <htamfids@gmail.com> — 2024-11-21T01:17:07Z

On Wed, Nov 20, 2024 at 11:33 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> After thinking about this some more, I'm actually finding myself leaning
> towards leaving the hint and potentially adding more detail to the
> documentation as a follow-up patch.

Sounds good to me. I think my hesitation was more that the hint was
overpromising help, so big +1 to more detail and keeping it.

Cheers,
Greg

Re: sunsetting md5 password support

Nathan Bossart <nathandbossart@gmail.com> — 2024-12-02T19:33:57Z

On Wed, Nov 20, 2024 at 08:17:07PM -0500, Greg Sabino Mullane wrote:
> Sounds good to me. I think my hesitation was more that the hint was
> overpromising help, so big +1 to more detail and keeping it.

Committed.  If anyone wants to try putting together a patch that expands
the "migrating to SCRAM" section of the docs before I get to it, please be
my guest.

-- 
nathan