Re: [HACKERS] scram and \password - Mailing list pgsql-hackers

On Thu, Mar 16, 2017 at 10:52 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> On 03/14/2017 11:14 PM, Tom Lane wrote:
>>
>> In short, I don't think that argument refutes my position that "md5"
>> in pg_hba.conf should be understood as allowing SCRAM passwords too.
>
>
> Yeah, let's do that. Here's a patch.

At least this has the merit of making \password simpler from psql
without a kind of --method option: if the backend is 9.6 or older,
just generate a MD5-hash, and SCRAM-hash for newer versions.
PQencryptPassword still needs to be extended so as it accepts a hash
method though.

> I had some terminology trouble with the docs. What do you call a user that
> has "md5XXXXX" in pgauthid.rolpassword? What about someone with a SCRAM
> verifier? I used the terms "those users that have an MD5 hash set in the
> system catalog", and "users that have set their password as a SCRAM
> verifier", but it feels awkward.

MD5-hashed values are verifiers as well, the use of the term
"password" looks weird to me.

> The behavior when a user doesn't exist, or doesn't have a valid password, is
> a bit subtle. Previously, with 'md5' authentication, we would send the
> client an MD5 challenge, and fail with "invalid password" error after
> receiving the response. And with 'scram' authentication, we would perform a
> dummy authentication exchange, with a made-up salt. This is to avoid
> revealing to an unauthenticated client whether or not the user existed.
>
> With this patch, the dummy authentication logic for 'md5' is a bit more
> complicated. I made it look at the password_encryption GUC, and send the
> client a dummy MD5 or SCRAM challenge based on that. The idea is that most
> users presumably have a password of that type, so we use that method for the
> dummy authentication, to make it look as "normal" as possible. It's not
> perfect, if password_encryption is set to 'scram', and you probe for a user
> that has an MD5 password set, you can tell that it's a valid user from the
> fact that the server sends an MD5 challenge.

No objections to that. If password_encryption is set off or plain, it
is definitely better to switch to scram as this patch does.

> In practice, I'm not sure how good this dummy authentication thing really is
> anyway. Even on older versions, I'd wager a guess that if you tried hard
> enough, you could tell if a user exists or not based on timing, for example.
> So I think this is good enough. But it's worth noting and discussing.

Regression tests are proving to be useful here (it would be nice to
get those committed first!). I am noticing that this patch breaks
connection for users with cleartext or md5-hashed verifier when
"password" is used in pg_hba.conf. The case where a user has a
scram-hashed verifier when "md5" is used in the matching hba entry
works though. The missing piece is visibly in CheckPWChallengeAuth(),
which should also be used with uaPassword.

-# Most users use SCRAM authentication, but some users use older clients
-# that don't support SCRAM authentication, and need to be able to log
-# in using MD5 authentication. Such users are put in the @md5users
-# group, everyone else must use SCRAM.
+# Require SCRAM authentication for most users, but make an exception
+# for user 'mike', who uses an older client that doesn't support SCRAM
+# authentication.## TYPE  DATABASE        USER            ADDRESS                 METHOD
-host    all             @md5users       .example.com            md5
+host    all             mike            .example.com            md5
Why not still using @md5users?
-- 
Michael