Re: initdb recommendations

On 5/24/19 9:49 AM, Heikki Linnakangas 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)

Tested manually and verified in code, it does do that check:

/*
* If 'md5' authentication is allowed, decide whether to perform 'md5' or
* 'scram-sha-256' authentication based on the type of password the user
* has. If it's an MD5 hash, we must do MD5 authentication, and if it's a
* SCRAM verifier, we must do SCRAM authentication.
*
* If MD5 authentication is not allowed, always use SCRAM. If the user
* had an MD5 password, CheckSCRAMAuth() will fail.
*/
if (port->hba->auth_method == uaMD5 && pwtype == PASSWORD_TYPE_MD5)
auth_result = CheckMD5Auth(port, shadow_pass, logdetail);
else
auth_result = CheckSCRAMAuth(port, shadow_pass, logdetail);

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

And "a superuser can verify this has occurred by inspecting the
pg_authid table (appropriate SQL)"

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

Verified this is true.

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

Jonathan

[1] https://www.postgresql.org/docs/current/auth-password.html