Re: Can we stop defaulting to 'ident'?

Greetings,

* Peter Eisentraut (peter(dot)eisentraut(at)2ndquadrant(dot)com) wrote:
> On 2020-05-20 16:57, Stephen Frost wrote:
> >* Peter Eisentraut (peter(dot)eisentraut(at)2ndquadrant(dot)com) wrote:
> >>Sorry, I should have been more clear. The upstream default of the GUC
> >>parameter "password_encryption" is md5.
> >
> >Which, really, is pretty broken when we're going to be having our
> >packagers setting up pg_hba.conf to use scram- at the *very* least it's
> >ridiculously misleading because we're going to have SCRAM in pg_hba.conf
> >but passwords actually stored as md5 and therefore we won't be getting
> >the benefits from SCRAM auth (though it should still work, of course,
> >since the SCRAM mode will fall back to working with an md5 password).
>
> Devrim's commit to pgrpms did include a change to the default setting of
> password_encryption, so it appears to be correct as far as it goes.

That's good- we'd want that to be done on the Debian packages too then,
though ideally we'd get the source default to be changed.

> But this leads to other questions, like, what should pg_upgrade do?

Same as it always has- make the user deal with anything they need to
regarding postgresql.conf? Why would anything change with pg_upgrade?
Even if the user doesn't change anything, everything would still work-
existing passwords with md5 would work with scram set in
password_encryption and scram in pg_hba.conf, and they'd be nicely
automatically upgraded when they change their password if they use a
password-change mechanism that works with SCRAM (and if they use one
that sets md5 passwords, that'd still work too). Yes, there's some
small potential risk that they change their password using psql's
\password and they have some client library that doesn't work with scram
yet using the same account and they'll have failed logins after running
\password, but that's certainly possible from just typo'ing the password
or something else and hopefully they'll be able to remedy that pretty
quickly.

Obviously the same goes for pg_hba.conf- pg_upgrade doesn't do anything
for that either, so users have to adjust it or copy over their old one
to the new cluster. Basically, users are likely pretty used to dealing
with having to fix up their configs as part of putting together a new
cluster before or after pg_upgrade on distributions that don't have
tooling around this.

And that's part of it- all of this depends entirely on what distribution
is being used and what tools are being used to do the upgrade anyway.
Debian has pg_upgradecluster that already copies over and updates things
in the postgresql.conf, so on Debian, I expect that we'll see that part
of such an upgrade might check pg_hba.conf for 'md5' entries and if it
finds any, setting password_encryption to 'md5' for those upgraded
clusters if it's not already set to something else in postgresql.conf,
to ensure there isn't any issue on those upgrades. Maybe it could be
more intelligent and notify the user that they're getting upgraded to
SCRAM and adjust pg_hba.conf to change md5 -> scram and leave
postgresql.conf alone, but ultimately that's probably going to be up to
what Christoph is comfortable with.

Unfortunately, I don't think any of that has made it over to RHEL yet
though.

Maybe we could talk about changing pg_upgrade to actually do something
about postgresql.conf, so that we can have commonality across
distributions, but that's certainly a different discussion.

> These discussions should be had, but perhaps not on the RPM packaging list
> the night before the release.

For my 2c, I don't think it'd be the worst thing to change a default
setting post beta1, so while I agree we should discuss this over on
-hackers, I don't think it's particularly pressing. Might be
interesting to see what happens with the beta1 release with Devrim's
changes (and ideally similar in Debian), in fact, to see if we actually
get any complaints or push-back from it.

Thanks,

Stephen