Re: WIP: SCRAM authentication

Hi Michael and Heikki,

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?

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

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

Agreed.

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

Yes, with some careful design the pg_auth_verifiers table could support
multiple passwords using a single verifier in the future. I think the
major changes would be to ALTER ROLE WITH PASSWORD so coming up with an
extensible syntax is important.

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.

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

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

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

--
-David
david(at)pgmasters(dot)net