Subject: Re: Possible to store invalid SCRAM-SHA-256 Passwords
To: Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org,
 Stephen Frost <sfrost@snowman.net>
References: <4240a41f-a957-fd7d-7c24-31683f259b9d@postgresql.org>
 <20190422015051.GA3433@paquier.xyz> <3033.1555940554@sss.pgh.pa.us>
 <5a93333c-dde6-ee52-f870-4dccfc50eb6f@postgresql.org>
 <30284.1555972921@sss.pgh.pa.us>
 <15140775-08c3-45bd-6dab-fdcd290d1bf0@postgresql.org>
 <20190423010142.GE2712@paquier.xyz> <10698.1555981849@sss.pgh.pa.us>
 <265876b8-1a72-89cd-bdcf-e641d93e166c@postgresql.org>
 <20190423021018.GF2712@paquier.xyz> <20190423065701.GL2712@paquier.xyz>
From: "Jonathan S. Katz" <jkatz@postgresql.org>
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: <43882bd7-a002-dd50-bf9a-401560285485@postgresql.org>
Date: Tue, 23 Apr 2019 10:19:30 -0400
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:60.0)
 Gecko/20100101 Thunderbird/60.6.1
MIME-Version: 1.0
In-Reply-To: <20190423065701.GL2712@paquier.xyz>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="05uwmu8PbGoRWHjuIXlqIwD7d6pwQToPx"
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--05uwmu8PbGoRWHjuIXlqIwD7d6pwQToPx
Content-Type: multipart/mixed; boundary="C0ddppYOzlMRhjSxvSWFgWDEs2Ff4W3VN";
 protected-headers="v1"
From: "Jonathan S. Katz" <jkatz@postgresql.org>
To: Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org,
 Stephen Frost <sfrost@snowman.net>
Message-ID: <43882bd7-a002-dd50-bf9a-401560285485@postgresql.org>
Subject: Re: Possible to store invalid SCRAM-SHA-256 Passwords
References: <4240a41f-a957-fd7d-7c24-31683f259b9d@postgresql.org>
 <20190422015051.GA3433@paquier.xyz> <3033.1555940554@sss.pgh.pa.us>
 <5a93333c-dde6-ee52-f870-4dccfc50eb6f@postgresql.org>
 <30284.1555972921@sss.pgh.pa.us>
 <15140775-08c3-45bd-6dab-fdcd290d1bf0@postgresql.org>
 <20190423010142.GE2712@paquier.xyz> <10698.1555981849@sss.pgh.pa.us>
 <265876b8-1a72-89cd-bdcf-e641d93e166c@postgresql.org>
 <20190423021018.GF2712@paquier.xyz> <20190423065701.GL2712@paquier.xyz>
In-Reply-To: <20190423065701.GL2712@paquier.xyz>

--C0ddppYOzlMRhjSxvSWFgWDEs2Ff4W3VN
Content-Type: text/plain; charset=windows-1252
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable

On 4/23/19 2:57 AM, Michael Paquier wrote:
> On Tue, Apr 23, 2019 at 11:10:18AM +0900, Michael Paquier wrote:
>> That's a hard morning...  Yes you are right and I can see the failure.=

>> By the way, grouping everything in one patch looks more adapted to me
>> as this tightens all the checks for the different verifier types.
>=20
> The afternoon has been better.  I have double-checked your patch and
> committed it down to v10.

Thanks!

> Now, there are two things which may need
> extra handling:
> - Do we add a note in the release notes about that with a SQL query
> checking the state of pg_authid?

Hmm...well, let's sound it out:

- If you have an invalid SCRAM-SHA-256 password (e.g.
SCRAM-SHA-256$1234), you would have been unable to log in anyway, so in
all likelihood you would either have had an admin reset your password,
or you gave up.
- If you had a md5 hash with bogus characters in it, it'd be the above
as well

So likely it's been resolved in some way: the user has been issued a new
password or has given up on PostgreSQL

With that said, we could do something like:

"To determine if this release affects an of your users ability to log in
using either the SCRAM-SHA-256 on MD5 password based methods, you can
run the following query:

<query that finds the now invalid hashes>

We advise that you reset the passwords for these users.
"

> - In ~9.6 we include in md5.h a macro which does not care about hex
> characters in the MD5 hash.  I think that we should fix that as well,
> or perhaps that's not worth caring per the lack of complaints?
> Attached is what would be needed.

+1 for fixing so its consistent (at least from a behavior standpoint).

I confirmed that it's in 9.5 & 9.4 as well.

Thanks,

Jonathan


--C0ddppYOzlMRhjSxvSWFgWDEs2Ff4W3VN--

--05uwmu8PbGoRWHjuIXlqIwD7d6pwQToPx
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCAAdFiEE+oS2la8r95ogZD/x8QSccp8cZScFAly/HvIACgkQ8QSccp8c
ZSf7Kg//YK8UqTNxcypd02dET63B+wVd4fgeMy+aTjkG1ZzG0b/hCEpJiJ2N8+Ab
Bl74WiQvCahYs3qIo/6sSfp4kC9cxkwtLMYi3vyoUcyJ+IJnjIXeS9dPaHyzKzJH
gkbVYqUA4za15GvzM4Z48UqQxUX75mu6YqKClakUeUnpPfbDr4pMZYH85OzG1wyA
3wyDKU4WIbPEfzIv2F14zS4HKG1lIaeyHV3JT1jZ48a6dMNS22O9Tb8y53WUvlqn
pCkelBY330r4RPGPkkghIJnd3c6P6VxnvRKmhiZyk4z/j2gFCU1C5GOb3OwSeeoD
Poqe8+/nptDLHuFrgLXeFTt6jv07M6lK3wZ5J/W6vzR9yPb7lQNat1HRHtiis+xC
xzBqDSqYaU6UjYAXAh/YtKQZHz6hFD+cl9IzLLVxI2F0HinWO6kTVwZKB1/M4QRK
PjSrZuQGe0r95YHvWoQXBtAZh9GNsiznfo5x3qDsqArqAHBx4V+Ti5oRCBNzbO7Z
bL1dIJ9cUdGyyzN0eKovd+x/iXGITSR6gttUMeWxfsPC4JsP5US7Rn/eO4s9YmjV
KPeq1Vf7Nz7JEdPgcXNaERXAZeWxS5Ve0rkC5lob1kulGYUF3amGIV7QaiTnfNPN
9iSkftb5CHq+H65IprY53HvYC4xXXWCHEHl2YBeBp1deRIc5OUw=
=CLo0
-----END PGP SIGNATURE-----

--05uwmu8PbGoRWHjuIXlqIwD7d6pwQToPx--