Re: initdb recommendations

Greetings,

* Heikki Linnakangas (hlinnaka(at)iki(dot)fi) wrote:
> On 24/05/2019 16:01, Stephen Frost wrote:
> >What I was really getting at though was the ability to have multiple
> >authenticator tokens active concurrently (eg: md5 AND SCRAM), with an
> >ability to use either one (idk, md5_or_scram auth method?), and then
> >automatically set both on password change until everything is using
> >SCRAM and then remove all MD5 stuff.
>
> Umm, that's what "md5" already does. Per documentation
> (https://www.postgresql.org/docs/current/auth-password.html):

I remembered that we did something here but hadn't gone and looked at
it recently, so sorry for misremembering. Perhaps all the more reason
for detailed migration documentation.

> > To ease transition from the md5 method to the newer SCRAM method, if
> > md5 is specified as a method in pg_hba.conf but the user's password on
> > the server is encrypted for SCRAM (see below), then SCRAM-based
> > authentication will automatically be chosen instead.
>
> The migration path is:
>
> 1. Use "md5" in pg_hba.conf, and put password_encryption='scram-sha-256' in
> postgresql.conf.
>
> 2. Wait until all users have reset their passwords, so that all users have a
> SCRAM-SHA-256 verifier.

Wait though- once a password is changed then they *have* to use SCRAM
for auth from that point on, right? That's great if you can be sure
that everything you're connecting from supports it, but that isn't going
to necessairly be the case. I think this is what I recall being unhappy
about and what I was trying to remember about what we did.

We also haven't got a way to tell very easily when a given md5 (or
scram, for that matter...) authenticator was last used, making it hard
to see if it's still actually being used or not. Nor is there a very
nice way to see when all users have reset their passwords to scram
without inspecting the password hash itself...

> 3. Replace "md5" with "scram-sha-256" in pg_hba.conf.
>
> Step 3 is kind of optional; once all users have a SCRAM verifier instead of
> an MD5 hash, they will all use SCRAM even without changing pg_hba.conf. It
> just prevents MD5 authentication in case a user forces a new MD5 hash into
> the system e.g. by changing password_encryption, or by setting an MD5
> password explicitly with ALTER USER.

Yes, which you'd certainly want to do, so I don't consider it to be
optional. Further, we should really have a way for an admin to say
"never allow storing an md5 password again" which I don't think we do.

Thanks,

Stephen