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 1hUAmm-0007to-J0 for pgsql-hackers@arkaria.postgresql.org; Fri, 24 May 2019 14:03:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hUAml-0005PA-6w for pgsql-hackers@arkaria.postgresql.org; Fri, 24 May 2019 14:03:03 +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 1hUAmk-0005Lw-S7 for pgsql-hackers@lists.postgresql.org; Fri, 24 May 2019 14:03:02 +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 1hUAmi-0002Ls-8v for pgsql-hackers@postgresql.org; Fri, 24 May 2019 14:03:02 +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=XhMrBlmHJosbfNRieCyfZkeedqkLePeYG3jYHtP+PcU=; b=OtwMoE2vtqGVtXWC4yzuWI1i8K YccerY3jAUfPj2aoUqxMmcHuUiUq2258PzTePD7YoV7JPHlEgLRtsqvCXHLWBA0vsPUuxgqrLD+U2 JoWBwiJebEX1MJbQFtPyeNvLXIbWotYbiojNUgoSiImUGrXzC2AL8VHCbW2NZMUXOxNexBpGGRM68 1osgktS0mCJuGM1JgFGITFnC6B0rWLe0VOUL/trtG1gnHzhZAPyMkbrT97kHt5QOLr8xrf7gmFThx 1y7vy0W6HtQLpd0C/hJibv7YUQBdPic7eSVkJmS5ohmMkraX1AMh4R1tkEF8ZCu7wUJ1ZlxMlPB/2 q9bWtpmQ==; 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 1hUAmf-0002oP-6P; Fri, 24 May 2019 14:02:59 +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> 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: <0a244411-d9c6-0081-8089-9e4b27fce19a@postgresql.org> Date: Fri, 24 May 2019 10:02:53 -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: <1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi> Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="i0NC98gXFfn8tZzQ9ZEwk9O9phG8hHxGC" 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) --i0NC98gXFfn8tZzQ9ZEwk9O9phG8hHxGC Content-Type: multipart/mixed; boundary="ZCK8Fbg4ggzvvuImdP6zpD0RVwuLIGJiu"; 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: <0a244411-d9c6-0081-8089-9e4b27fce19a@postgresql.org> 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> In-Reply-To: <1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi> --ZCK8Fbg4ggzvvuImdP6zpD0RVwuLIGJiu Content-Type: text/plain; charset=windows-1252 Content-Language: en-US Content-Transfer-Encoding: quoted-printable 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. >=20 > 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 =3D=3D uaMD5 && pwtype =3D=3D PASSWORD_TYPE_MD= 5) auth_result =3D CheckMD5Auth(port, shadow_pass, logdetail); else auth_result =3D 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. >=20 > The migration path is: >=20 > 1. Use "md5" in pg_hba.conf, and put password_encryption=3D'scram-sha-2= 56' > in postgresql.conf. >=20 > 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)" >=20 > 3. Replace "md5" with "scram-sha-256" in pg_hba.conf. >=20 > Step 3 is kind of optional; once all users have a SCRAM verifier instea= d > 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 b= y > 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 --ZCK8Fbg4ggzvvuImdP6zpD0RVwuLIGJiu-- --i0NC98gXFfn8tZzQ9ZEwk9O9phG8hHxGC Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAEBCAAdFiEE+oS2la8r95ogZD/x8QSccp8cZScFAlzn+Y0ACgkQ8QSccp8c ZSf3Fw/9FC9WJXVo0BRrUUnBUUPgpeEKuHmtX/rkjvaHhzUFAG3w+tlWe4+MjINy alcjDxl0+9cVtNp0GIS5DyJgoQ3LVuGreIGbZWHtfEZmYiaVCLq8uekSkIGUETwL UToI4kKnNlk3WGuVU46rSGsWAa4cy67EKizZgV+U0kOY4cgHXojj4Xq8hLlaI2CL 5E+SJi/PUf7jSOPl/uZaQoZUsEvfVue483bh2cI6XPhO8UTzzcUuyLH232NTBkuS sgOTVc7ziCh1qeNig76Qt01V31VtFcfhCx5CJCTJt0fLOAHfeTYMFLKLIHfZ0Uq8 9OAQjocb97B9M5xXkfOh8L8Hnh2DclEj7a5KTMedNgxvjY5Ppo5/CxN/GcJ6xqo+ tzyhU+O+75M63/Kj5Kt7FUF3q9vgIDDkyLXCEXihqCZCKlF437mDvbU1gML0O/ER GNTf7irKexDMcXNHkyCzSXTUTH5jF6LQx3XpOua1A6LBeQVO9IssLMOSl2L3xbhN P4eCkXApngzh1+zLfY/gAo95uK9TGO3fMk6sPXm/MA4UQuAiKd6umIncDSXXU2H5 xOpKA+4vbtwp3cnI6v9gJe2oD2ryaP4uz79vC6zI3gH6aU1m7+ZTP7lfHz+phy23 motqmzo2BpPYZM7PE0LZ/GAeB6KFsjeNX82O/KW0CSfIwMONp4Q= =06qg -----END PGP SIGNATURE----- --i0NC98gXFfn8tZzQ9ZEwk9O9phG8hHxGC--