Re: Password identifiers, protocol aging and SCRAM protocol

On 09/26/2016 09:02 AM, Michael Paquier wrote:
> On Mon, Sep 26, 2016 at 2:15 AM, David Steele <david(at)pgmasters(dot)net> wrote:
>> However, it doesn't look like they can be used in conjunction since the
>> pg_hba.conf entry must specify either m5 or scram (though the database
>> can easily contain a mixture). This would probably make a migration
>> very unpleasant.
>
> Yep, it uses a given auth-method once user and database match. This is
> partially related to the problem to support multiple password
> verifiers per users, which was submitted last CF but got rejected
> because of a lack of interest, and removed to simplify this patch. You
> need as well to think about other things like password and protocol
> aging. But well, it is a problem that we don't have to tackle with
> this patch...
>
>> Is there any chance of a mixed mode that will allow new passwords to be
>> set as scram while still honoring the old md5 passwords? Or does that
>> cause too many complications with the protocol?
>
> Hm. That looks complicated to me. This sounds to me like a retry logic
> if for multiple authentication methods, and a different feature. What
> you'd be looking for here is a connection parameter to specify a list
> of protocols and try them all, no?

It would be possible to have a "md5-or-scram" authentication method in
pg_hba.conf, such that the server would look up the pg_authid row of the
user when it receives startup message, and send an MD5 or SCRAM
challenge depending on which one the user's password is encrypted with.
It has one drawback though: it allows an unauthenticated user to probe
if there is a role with a given name in the system, because if a user
doesn't exist, we'd have to still send an MD5 or SCRAM challenge, or a
"user does not exist" error without a challenge. If we send a SCRAM
challenge for a non-existent user, and the attacker knows that most
users still have a MD5 password, that reveals that the username doesn't
most likely doesn't exist.

Hmm. The server could send a SCRAM challenge first, and if the client
gives an incorrect response, or the username doesn't exist, or the
user's password is actually MD5-encrypted, the server could then send an
MD5 challenge. It would add one round-trip to the authentication of MD5
passwords, but that seems acceptable.

We can do this as a follow-up patch though. Let's try to keep this patch
series small.

- Heikki