sunsetting md5 password support

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.

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

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

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

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)

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

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

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

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

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)

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

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

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

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

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

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

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

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

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