Re: WIP: SCRAM authentication - Mailing list pgsql-hackers

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.

Supporting multiple verifiers for a single role has IMO clear advantages:
- help transition to new protocols and decommission of old protocols
without the need to create alternative roles in the backend when
switching from one or the other.
- move on to a more complex password aging system. The patch currently
submitted allows only one verifier per type and per role so this is
not a complete system. Still, the new catalog table pg_auth_verifiers
could be later easily extended based on other aging properties that we
consider adapted to reach this goal.

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

Thoughts?
-- 
Michael