Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUBai-0001ah-RM for pgsql-hackers@arkaria.postgresql.org; Fri, 24 May 2019 14:54:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hUBah-0000Fa-Kq for pgsql-hackers@arkaria.postgresql.org; Fri, 24 May 2019 14:54:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUBah-0000FS-BV for pgsql-hackers@lists.postgresql.org; Fri, 24 May 2019 14:54:39 +0000 Received: from meldrar.postgresql.org ([2a02:c0:301:0:ffff::31]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUBaa-0003UE-9a for pgsql-hackers@postgresql.org; Fri, 24 May 2019 14:54:38 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Content-Type:In-Reply-To:MIME-Version:Date: Message-ID:From:References:Cc:To:Subject:Sender:Reply-To: Content-Transfer-Encoding:Content-ID:Content-Description; bh=/KMTG7gL2geaZkxzi6DqyjMM+YwKkMPzUvFFPntsr60=; b=XxuTDLaYCEww2kEM5U79y/meJJ Qwi9z10yEuTEwvDzAX/iFjAHZn+PbU1T73bs+khunpv1ddO0UkO6OeSi3NCqu4bvTW9bzQr0oCrtz mukBHx7YWjFtNliYoCwGSb+4RZnyhvZasg6XpKSA+N9U8F63H3Y3j2n9iJHs8PYhqqfOWINf/f8qf Dgh9KQkoK2P/Ke/TWQcWBDL1MKsOAVyGmLKa0+EodaTQbCbTR8QO+i0laHDVztqTI4TzjZ0KVP1tD 9nQz3nesfOENVLuu4QZ6WmXWnc5zYRBLytHDkbuRkbgQ5YsmUPihDqWtHnBXeEYO9EXV0CGLLEkrN rGmLoo1Q==; Received: from pool-96-250-83-19.nycmny.fios.verizon.net ([96.250.83.19] helo=Ph33r-Retina.home) by meldrar.postgresql.org with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUBaX-0005R9-GX; Fri, 24 May 2019 14:54:32 +0000 Subject: Re: initdb recommendations To: Heikki Linnakangas , Stephen Frost Cc: Magnus Hagander , Joe Conway , Tom Lane , Peter Eisentraut , pgsql-hackers , Noah Misch References: <9f46f065-ca80-f637-8c5d-a9877bcd5eab@postgresql.org> <10093.1558664907@sss.pgh.pa.us> <20190524023009.GL2480@tamriel.snowman.net> <20190524121306.GS2480@tamriel.snowman.net> <17041a05-0e28-f2b1-bf0c-fe375af66b45@joeconway.com> <20190524121904.GU2480@tamriel.snowman.net> <20190524123317.GW2480@tamriel.snowman.net> <20190524130123.GY2480@tamriel.snowman.net> <1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi> <0a244411-d9c6-0081-8089-9e4b27fce19a@postgresql.org> From: "Jonathan S. Katz" Openpgp: preference=signencrypt Autocrypt: addr=jkatz@postgresql.org; keydata= mQINBFtShwABEAC7PNHDUOTYuifpUCk23KqfxdQQkn4nkoxOXRK0+rAj36FiwqGB4TJFuOVZ sDFAEiSlC8Jt4y5Cs7B5tetT8JNd2cs6zp/udMJJDz9d65O9PDpdlMgAmIiTzpLlSdx8FG56 DTksaDv1d8j3cTJPSE4/fWSxqzA7o3Y9UuL7atZPrzfImgpRKs0of7elIHwOa8GucjyhYqcR h60wFBJc2KXqQdDYRTZy43DSnY/0VNc0omiH355fustvpm+m5HjD3w7qZyfN3fpKJpnX1LCF f3MnPHaDGITIYGRCBXvf0UqUtD6OEVWPv2C2gyqWMIpWmZTOgDufltKyIByKBoS9x0PlFkij 04X3KODCngt+N8Ssc9OICc6QSxhjoP48PYPdmiTmkrGuf0LX084wj1xeo1NX7XxZK39F6dTJ DhsIiW0sNS0xMxQHLHG9VLbPjx3SANQBh6BuryPz5ZupW9/TIDmkvprtU/oXfKgtfYm3fxmk EctxbWrEPsFTFPyuMqQu6l+xyQv0s1VLZfjNWaua6H1/gGoIt6kRnn5qMXDVVpijuWkHbv7G ngaQMd258UrrOEHnnjzhQ7jxMWV9D+emxbAtlIxnYvCWlV4IwAQhEHfvudqYaIY3hNWrvQ6H GB2KXoTZYN9g5djm14/5nj1IU5zOcovkjJnKhoo9iStnpFF2cwARAQABtCdKb25hdGhhbiBT LiBLYXR6IDxqa2F0ekBwb3N0Z3Jlc3FsLm9yZz6JAlQEEwEIAD4CGwMFCQeGH4AFCwkIBwIG FQoJCAsCBBYCAwECHgECF4AWIQT6hLaVryv3miBkP/HxBJxynxxlJwUCW1KJJQAKCRDxBJxy nxxlJwjrD/kBgqsW4QpNpTFw7ifRokZV08CCX4huPBJQ91rrv+UEWlEcotFBHVkYyHnpzARl tcZxhJ9CbFxjniH9cOTty5T/O1yolbOHtZSW8Z8aWV6BVEbjMb+BFxSSLm7RnvJdzQbGCZq2 ZZvfVpB6z3EHYph4KDdVKvMFjoLskxmdS1DE0tE3zTxvoQsi24Q+HOS07kUjs6fsu/WICMfz mgO++AWG9Y0CvN0mm4TkujESzyKM9E5irD+leEMIcddl51Aa2c/VMfBXQbRmpHIgUFTmuHQD CnQih+9i3OJAksDg66SP8a7yiXv5mwvyDi1EfTGVKYR2j+pwyjwnC3oIbvDMmB3uTn2JIjnT iZKPVtAcAylXjubFltihQgNyuShdP4W+kBwZizhUFqUVL8Anx+KoytYmJPfMRFLGuK4obXKq a2ZS3k9KB+H+isOx2nFJOsc7V360Zp1DVaNmuiK10TT6QndShSPaqkJqFtCb6r92rZ9sZM/L 3vtCI4Rrl3Pt1MgtENXupS8gZpJnAYS0j5A1PAZ09r6ANoaeMHspF+5J5fOHeEvqphXr36mm a83Vl1t4orPb0+QmmijmlpseDU63M88Aw5p3c4qj7t8Qr2EZ5zrn7/sFn5wOfbs8Nymxafif QCnlV2vg9p0m7vSk/yLJ4PFZvs52FgqAGRCdRn0s2EC99bkCDQRbUocAARAAv8ho/toQ9DG3 j4f9h9n1aRHr2FlviN2Utpy6L8+dfDggO0geilmkGQOolZ2E60gGfye/kUtF9W3NByO4hxDR 9u6qbOXcdqnuA+cc68EfqlWFJrVtYFxt0h4ElWYOYnIezKthriWch/FY70FGrxs3z8UHOHq5 0wBW433eTvZm90WixBiXEt2v1DgW4Vr3ymfO7Aap/IYyPuE4JzgudAuAl0HKPyNEHWHG1dAb jX1RiCw9gknIDWQOF0B4UAaJctWGVcnZ3A2ULwNGMa1P9ZJlBWf1vcj01aiHMU0yQ7JjJiSp vfm9eM0uSLwRdDrJjyl5ZZqVumjdv2SMNQ8GvYRbEMys3GGDSt9zXgfCSUnPnJfYxjzBHRI6 x44Wfsx8S6hWxepOogCJJ/g67Bk9mY8YV4klWIXDJVOL5jnBC09DbsZG81JaE2QxB8Y7W36Z Mroi9XMxg3s805hQAQUvdG/poU8hN8BWdrnTm/+4eQQp7gDY1ePDmGM6bJC+OHOSnFtR/f+7 0zpKJ10cc7cBygGnl1yR3KjhFyAWUFvP4ZGziKCcpMwXZfe9PGuyA/YOubMphxIn3YsK2wrd faKZYX2GMZCZhMMcvx9IpQrxIJgU+VlwXu/O+Lk10VIPcxPJJwmpdI6HzcS8ZgG6IMcC444X XTuLaP8j2mgcMvYak3ScCykAEQEAAYkCPAQYAQgAJhYhBPqEtpWvK/eaIGQ/8fEEnHKfHGUn BQJbUocAAhsMBQkHhh+AAAoJEPEEnHKfHGUnReMP/RA2UhGQj+G4uBshkRLjpRysabdPqgQB dEBk6wYbio88Wg/2/hgY7UzmDDEwX3sZfQDcrI6+vIobI8uqstZID+WgAAa1JLfChMyVQnSy 0zfWMOABXscc5tGuvFRZvJklTissMFjXUwaREEKp4ZikTvJ/62MCjSdtrUhnPLvoTsHTKRKD ichE+b5A54alwsubTijw12O6N22r5IjZiiSZV0u7dsShyKw+7wCSax9fuBoE43NMYf+dnjMK nerAQYUcZWYMnk+EC8RaqYAxv6XZ2tKx1AkGGktwkQIBwrz9IlDSvJ3LWJ2UIIuLRTdngNgg GIL4zzuUa2F56FqskQIuYMaNETk6LYfalBDQ6TVLAcgCPQxp4k4i/PRsZ3lZ2ZhRHRYciOvm kp+I6EfHwllQpYrWs1thluBGqlJSVJgKl0IOFvKLsQ6KKfqzAwh6FxrO5qajp4viNIgtWoFw O5Bp0jgFTbH2OrMWIRfUdUCH1Djbuo65svhNj7FNsQVYzHDI+Nd1I/LOBoPc3UorRMF2M4JV kUR7skOHWUbPTSNUr7qc14NSMY3PKjGeVGAsBVHBPvmRx/Ss2tW/TpJWpxg4pmquFQSXuaYh Yf4FN8Sxy320pcr+FqN8AhZkYJcNY82OTtR5VEKRC/mYcyq61qXIKqngydn79bJjGxHipxyF FmEC Message-ID: Date: Fri, 24 May 2019 10:54:24 -0400 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:60.0) Gecko/20100101 Thunderbird/60.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="jiyFTphMJl6a5lJmQc2H3mgpkne3qrMsq" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --jiyFTphMJl6a5lJmQc2H3mgpkne3qrMsq Content-Type: multipart/mixed; boundary="7yXgH9jQmLbtiMWa2ReX3EHCHYymdVu3R"; protected-headers="v1" From: "Jonathan S. Katz" To: Heikki Linnakangas , Stephen Frost Cc: Magnus Hagander , Joe Conway , Tom Lane , Peter Eisentraut , pgsql-hackers , Noah Misch Message-ID: Subject: Re: initdb recommendations References: <9f46f065-ca80-f637-8c5d-a9877bcd5eab@postgresql.org> <10093.1558664907@sss.pgh.pa.us> <20190524023009.GL2480@tamriel.snowman.net> <20190524121306.GS2480@tamriel.snowman.net> <17041a05-0e28-f2b1-bf0c-fe375af66b45@joeconway.com> <20190524121904.GU2480@tamriel.snowman.net> <20190524123317.GW2480@tamriel.snowman.net> <20190524130123.GY2480@tamriel.snowman.net> <1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi> <0a244411-d9c6-0081-8089-9e4b27fce19a@postgresql.org> In-Reply-To: --7yXgH9jQmLbtiMWa2ReX3EHCHYymdVu3R Content-Type: text/plain; charset=windows-1252 Content-Language: en-US Content-Transfer-Encoding: quoted-printable 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 >=20 > You mean, like this? From the bottom of that page :-) =2E..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 =3D '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. >=20 > It would be nice to expand that a little bit, though: >=20 > * How do you verify if all client libraries support SCRAM? Would be goo= d > 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 --7yXgH9jQmLbtiMWa2ReX3EHCHYymdVu3R-- --jiyFTphMJl6a5lJmQc2H3mgpkne3qrMsq Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAEBCAAdFiEE+oS2la8r95ogZD/x8QSccp8cZScFAlzoBaAACgkQ8QSccp8c ZSdpKA/+PzbKKDIsrIk/lqfaBZCvRMIoxRrUveTMVFe2s3fkS6UZ4fjhIlPMPPPT 65jpDd0v1cHnDkatGjVZtCzkPte7r8ltatqih6CKDcMLTDyWfH/pfpxewCfZIHKo iBnY6OjS2CndR7m+sefWizhLyCfdTAXKiAEZfJjYhYHv3WmHqacCIxDJK667K5JD DQ1ljW9uP4vAj0sgRIhT5v8eb5QGql3zIdjzymFDqoxo6Ds/R4fC50MHXfyH/21B Q/5rJ4DqED+41UNzFfI2f+Q0En0BpeyqPQpiHZmAiz5UgTDN3jRO6pZlFsDfGy+E 5SjoRGrVqQLptY9Brj1HJDrNshnnl6a2Y+tjgoaZnjEftR8sLTIilAdekepUnyag TJEqBNz5k9LSnylr5UXWeZCh06lcZ7T8U/BV7zlACq9nAvh0z1AOuVTUmtIF1XI/ CPDB0RcQ8QZCY3JRHxJDbePjEyM7w0SUhOaNkG58Q0JwjElcmyLhm407M1Coerel X9r6vQDG5gHbJ7NlV9CBbSAYojCysXkeKnYlLlDYZ24sigm4YZNMSsyvnlPXIF5h QjcI6zn1Ryzos7OdR/IzuVXQBWnbDkD8Yv8ZIV7Cg2MVxDoCWWoVSthQYdOxBTK0 4/7Y3KD5BnXSCcvshOT+rxrryvPl7scqYkAhshGfzys8JSX3Zx8= =rXQs -----END PGP SIGNATURE----- --jiyFTphMJl6a5lJmQc2H3mgpkne3qrMsq--