Re: Can we stop defaulting to 'ident'? - Mailing list pgsql-pkg-yum

Greetings,

* Stephen Frost (sfrost@snowman.net) wrote:
> * Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
> > On 2020-05-20 16:57, Stephen Frost wrote:
> > >* Peter Eisentraut (peter.eisentraut@2ndquadrant.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?

This still more-or-less applies, but I missed a step when I was testing
this out (or, rather, I applied the right config change.. to the wrong
cluster, and that makes all the difference).

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

This is, frustratingly, backwards from what we actually decided to do
(which drives me crazy and which I complained about multiple times on
list and yet what we ended up with, but hey, that's part of being
me...).

If you leave it as 'md5' in pg_hba.conf, then *that* will do either md5,
or scram.  If you have 'scram-sha-256' in pg_hba.conf and only an 'md5'
password then it breaks.

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

Of course, since users are probably just going to copy over their
pg_hba.conf from their old cluster with pg_upgrade, this probably won't
end up mattering.

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

On the Debian side, the appropriate approach would be to keep the
existing pg_hba.conf, unless all of the passwords are already scram
(something the pg_upgradecluster could check) and, in that case, move to
scram.  Perhaps also move to scram if there aren't any passwords set in
the cluster.

In all of these cases, we'd need the password_encryption default set to
scram first, so that when users change their password they'll get
upgraded to scram, but for old users to connect the hba has to have md5.

For new installs, I'm still of the opinion that we go with the defaults
of scram, so these considerations are only for how to properly address
the upgrade path.  Ideally there'd be a way to configure to use scram in
pg_hba.conf but still allow md5, but we haven't got that today.

Thanks,

Stephen