Re: Possible to store invalid SCRAM-SHA-256 Passwords

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