Re: WIP: SCRAM authentication

On Sat, Feb 13, 2016 at 3:05 AM, David Steele <david@pgmasters.net> wrote:
> On 11/16/15 8:53 AM, Michael Paquier wrote:
>> On Sat, Sep 5, 2015 at 9:31 AM, Bruce Momjian wrote:
>>> On Fri, Sep  4, 2015 at 04:51:33PM -0400, Stephen Frost wrote:
>>>>> Coming in late, but can you explain how multiple passwords allow for
>>>>> easier automated credential rotation?  If you have five applications
>>>>> with stored passwords, I imagine you can't change them all at once, so
>>>>> with multiples you could change it on one, then go to the others and
>>>>> change it there, and finally, remove the old password.  Is that the
>>>>> process?  I am not realizing that without multiple plasswords, this is a
>>>>> hard problem.
>>>> That's exactly the process if multiple passwords can be used.  If
>>>> there's only one account and one password supported then you have to
>>>> change all the systems all at once and that certainly can be a hard
>>>> problem.
>>>>
>>>> One way to deal with this is to have a bunch of different accounts, but
>>>> that's certainly not simple either and can get quite painful.
>>> OK, for me, if we can explain the benefit for users, it seems worth
>>> doing just to allow that.
>> Reviving an old thread for a patch still registered in this commit
>> fest to make the arguing move on.
>
> I was wondering if this patch was going to be submitted for the 2016-03 CF?

For 9.6 I am afraid this is too late, per the rule that there cannot
be huge patches for the last CF of a development cycle. But I have
plans for this set of features afterwards with the first CF of 9.7 and
I was planning to talk about it at PgCon's unconference if I can get
there to gather some feedback. There is still cruel need for it on my
side..

> If so I am interesting in testing/reviewing or doing any other work that
> would be helpful.

Thanks.

> In addition, I would prefer to maintain the current structure of the
> pg_authid table and use the rolpassword and rolvaliduntil columns to
> store only the md5 verifier which will also be stored in
> pg_auth_verifiers.  This would provide a smoother migration path with
> the idea that rolpassword and rolvaliduntil will be removed from
> pg_authid in a future version of Postgres.

Actually, I am of the opinion that both rolpassword and rolvaliduntil
should be directly part of pg_auth_verifiers. Being able to handle
multiple verifiers for the same protocol is a feature that is being
asked for with a given password handling policy (was pinged again
about that in Moscow last week). Rolling in new verifiers needs now
extra roles to be created.

>> There are clear concerns about protocol aging and how to facilitate
>> the life of users to switch to a new system. Hence, I think that the
>> patch could include the following:
>> - A compatibility GUC allowed_password_verifiers defaulting to a list
>> of verifier protocols that we think are safe. This would be added in
>> the patch adding infrastructure for multiple verifiers, with default
>> to md5. In the patch adding SCRAM, the value of this parameter is
>> changed to md5,scram. If a user create a password verifier with a
>> protocol not listed in this parameter we return to him a WARNING.
>> ERROR may be too much but opinions are welcome.
>
> It seems like an error would be better here.

Noted.

>> - A superuser cleanup function for pg_auth_verifiers aimed at being
>> used by pg_upgrade to do needed cleanup of this catalog based on the
>> previous GUC to remove outdated verifiers. Optionally, we could have
>> an argument for a list of protocols to clean up.
>> Using both things we could leverage the upgrade experience and
>> transition between systems. Say even if at some point we decide to
>> decommission SCRAM we could reuse the same infrastructure to move on
>> to a new major version.
>
> Yes - and although the eventual migration process may not need to be
> worked out it its entirety we should have a very good idea of what it's
> going to look like as that will inform some of the decisions that need
> to be made now.

Thinking about that again a combination of a GUC and an interface
dedicated to pg_upgrade sounds the saner way of going here.

> Please let me know if there's anything I can do to expedite this patch.

I am planning to work on a new patch following the ideas I have sent
upthread, after the last CF of 9.6 is wrapped up. This thread is high
on my priority list.
-- 
Michael