Re: initdb recommendations

On 24/05/2019 17:02, Jonathan S. Katz wrote:
> On 5/24/19 9:49 AM, Heikki Linnakangas wrote:
>> 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.
>
> Cool. Thanks for the explanation.
>
> I do think we should document said upgrade path, my best guess being
> around here[1].
>
> [1] https://www.postgresql.org/docs/current/auth-password.html

You mean, like this? From the bottom of that page :-)

> To upgrade an existing installation from md5 to scram-sha-256, after
> having ensured that all client libraries in use are new enough to
> support SCRAM, set password_encryption = 'scram-sha-256' in
> postgresql.conf, make all users set new passwords, and change the
> authentication method specifications in pg_hba.conf to scram-sha-256.

It would be nice to expand that a little bit, though:

* How do you verify if all client libraries support SCRAM? Would be good
to mention the minimum libpq version here, at least. Can we give more
explicit instructions? It would be nice if there was a way to write an
entry to the log, whenever an older client connects. Not sure how you'd
do that..

* How does one "make all users to set new passwords"? Related to that,
how do you check if all users have reset their password to SCRAM? Give
the exact SQL needed to check that.

- Heikki