Re: initdb recommendations

On 5/24/19 10:26 AM, Heikki Linnakangas wrote:
> 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 :-)

...yes ;) I think what I'm saying is that it should be its own section.

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

Yeah, this one is hard, because a lot of that depends on how the client
deals with not supporting SCRAM. Typically the server sends over
AuthenticationSASL and the client raises an error. All the server will
see is the connection closed, but it could be for any reason.

For example, I tested this with an unpatched asyncpg and noted similar
behavior. I'm not sure there's anything we can do given we don't know
that the client does not support SCRAM ahead of time.

I think the best we can do is mention minimums and, if we're ok with it,
link to the drivers wiki page so people can see which min. versions of
their preferred connection library support it.

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

Yeah this is a big one. I already hinted at the latter point, but also
explaining how to change passwords is helpful too (and I feel can also
cause quite a debate as well. Within psql it's a straightforward choice.
Outside of it, to do it safely you have to do a bit of extra work).

Jonathan