Subject: Re: initdb recommendations
To: "Jonathan S. Katz" <jkatz@postgresql.org>,
 Stephen Frost <sfrost@snowman.net>
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>
 <1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi>
 <0a244411-d9c6-0081-8089-9e4b27fce19a@postgresql.org>
From: Heikki Linnakangas <hlinnaka@iki.fi>
Message-ID: <dc66c471-f3e4-a984-b66b-d962d52f59ea@iki.fi>
Date: Fri, 24 May 2019 17:26:01 +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: <0a244411-d9c6-0081-8089-9e4b27fce19a@postgresql.org>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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