Subject: Re: initdb recommendations
To: Stephen Frost <sfrost@snowman.net>,
 "Jonathan S. Katz" <jkatz@postgresql.org>
Cc: Magnus Hagander <magnus@hagander.net>, Joe Conway <mail@joeconway.com>,
 Tom Lane <tgl@sss.pgh.pa.us>,
 Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>, Noah Misch <noah@leadboat.com>
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>
From: Heikki Linnakangas <hlinnaka@iki.fi>
Message-ID: <1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi>
Date: Fri, 24 May 2019 16:49:30 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.6.1
MIME-Version: 1.0
In-Reply-To: <20190524130123.GY2480@tamriel.snowman.net>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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):

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

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.

- Heikki