From: Jonathan S. Katz <jkatz@postgresql.org>
To: Heikki Linnakangas <hlinnaka@iki.fi>
To: Stephen Frost <sfrost@snowman.net>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Joe Conway <mail@joeconway.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Noah Misch <noah@leadboat.com>
Subject: Re: initdb recommendations
Date: Fri, 24 May 2019 10:02:53 -0400
Message-ID: <0a244411-d9c6-0081-8089-9e4b27fce19a@postgresql.org> (raw)
In-Reply-To: <1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi>
References: <9f46f065-ca80-f637-8c5d-a9877bcd5eab@postgresql.org>
<10093.1558664907@sss.pgh.pa.us>
<20190524023009.GL2480@tamriel.snowman.net>
<df011ad3-f1a3-149e-3489-2d68e6ea21a7@joeconway.com>
<20190524121306.GS2480@tamriel.snowman.net>
<17041a05-0e28-f2b1-bf0c-fe375af66b45@joeconway.com>
<20190524121904.GU2480@tamriel.snowman.net>
<CABUevExV2YKa8cFRmFzf=cGriqhoHqwd9ev=2hWdq1kieqaV6Q@mail.gmail.com>
<20190524123317.GW2480@tamriel.snowman.net>
<ded009db-b651-1d35-0ec6-282af16068a4@postgresql.org>
<20190524130123.GY2480@tamriel.snowman.net>
<1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi>
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.htmlAttachments:
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: jkatz@postgresql.org, hlinnaka@iki.fi, sfrost@snowman.net, magnus@hagander.net, mail@joeconway.com, tgl@sss.pgh.pa.us, peter.eisentraut@2ndquadrant.com, pgsql-hackers@postgresql.org, noah@leadboat.com
Subject: Re: initdb recommendations
In-Reply-To: <0a244411-d9c6-0081-8089-9e4b27fce19a@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox