Re: WIP: SCRAM authentication

On Wed, Aug 19, 2015 at 5:30 AM, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Tue, Aug 18, 2015 at 7:19 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> Sorry, that's a completely bogus argument. We do not "need" to
>> prevent people from upgrading if they haven't moved off of MD5
>> authentication; that's just an arbitrary - and IMHO extremely
>> user-hostile - policy decision. We have a legitimate need, to move
>> the project forward, to introduce a better system for password
>> authentication. Ripping out the old one is not a real need. I'm sure
>> at some point it will seem like antiquated cruft that nobody uses any
>> more, but that will not be in "a year or two" or anything close to
>> that.
>
> I would imagine a GUC like enable_legacy_authentication=true which we
> would just start defaulting to false after a few years and perhaps
> consider removing five years after that. pg_upgrade could check if
> there are any users which require it to be set to true and warn users
> that they must enable it but should check the documentation so they
> understand the impact.

Yep, that's one of the ideas mentioned upstread. This parameter should
actually be filled with a list of verifiers that we consider
out-of-support, deprecated, well things that users should be warned
about. One solution may be to log in warnings when parsing pg_hba.conf
should a deprecated method be used.

>> OK, that's an interesting argument. If SCRAM supports multiple
>> password verifiers, and we support SCRAM, then I guess we should
>> probably do that, too. I still don't like it all that much, though.
>> I think it's absolutely inevitable that people are going to end up
>> with an account with 3 or more different passwords that can all be
>> used to log into it, and that won't be good. How do other systems
>> avoid this pitfall?
>
> Fwiw having multiple passwords would make automated credential
> rotations *so* much easier. Heroku has a really baroque solution to
> this problem in Postgres involving creating new child roles and
> swapping them around. My team in Google wasted many man hours dealing
> with fallout from the quarterly password rotations.
>
> (I wish we could just drop the account management and authentication
> system entirely and dump the whole work on a system designed for this
> particular problem. It's a hard problem that's far outside our core
> features and Postgres is never going to be a good system for anyone
> let alone everyone when there are many different types of users.)

This makes me think that we may need actually a finer grammar than
what the patch is proposing:
ADD PASSWORD VERIFIERS (method = 'value' [, ...] ) [ OPTIONS
(validUntil = 'timestamp') ]
DROP PASSWORD VERIFIERS (method [, ...])
PASSWORD VERIFIERS (method = 'value' [, ...]) [OPTIONS validUntil =
'timestamp'] -- replace all the existing ones

Reaching to the following catalog for pg_auth_verifiers:
Oid roleoid;
char method;
text value;
timestamp valueUntil;

I am not sure if we would want to be able to have multiple verifier
values for the same method, but feel free to comment.
Regards,
--
Michael