Re: WIP: SCRAM authentication

On 08/09/2015 07:19 PM, Michael Paquier wrote:
>> ... during my exchange with Michael, I was thinking about the bug
>> > potential of taking the password field and multiplexing it in some way,
>> > which is significant. There is a definite risk of "making this too
>> > complicated" and we'll need to contrast that against ease-of-migration,
>> > because complicated mechanisms tend to be less secure due to user error.

> Sure. That's why I am all in for adding a compatibility GUC or similar
> that enforces the removal of old verifier types after marking those as
> deprecated for a couple of years as there's surely a significant risk
> to keep old passwords around or bad pg_hba entries. Still we need IMO
> a way for a user to save multiple verifiers generated from a client to
> manage carefully the password verifier aging, deprecations and support
> removal.

That still falls under the heading of "possibly too complicated" though.

As I see it, there's two potential migration paths:

1. Allow multiple verifiers for each login role (Heikki's plan).

2. Set verifier type per login role.

(2) has the advantage of not requiring a bunch of new scaffolding
(although it will require a little) and thus being less likely to
introduce new bugs. It also doesn't require adding new catalog
structures which are really only needed for the migration period, and
after which will become a wart (i.e. having multiple verifiers per login
role).

In real migration terms, though, (2) has some major drawbacks in terms
of making migration much harder.

a) it won't work for Heroku and other 1-login-per-database hosting.

b) moving to multiple roles from single roles per app is a painful
process currently.

For (a), one could argue that these are good candidates for "all at
once" migrations, and that moving to SCRAM will depend on the host
supporting it. Someone from Heroku could speak up here.

For (b), there are a lot of things we could do to make migrating to a
multiple-role infra much easier for users, which would continue to be
useful even after the migration to SCRAM is history:

* remove the role requirement for ALTER DEFAULT PRIVILEGES, and ...

* add ALTER ROLE ___ ALTER DEFAULT OWNER, a command which sets the
default owner of newly created objects by that login role to a different
role of which they are a member. Alternatively, add a way to make a
default SET ROLE whenever a login role logs in.

These two changes, or changes like them that serve the same purpose,
would allow us to prescribe the following migration path for most users:

1. add a new login role which is a member of the old login role and uses
SCRAM;

2. set the defaults for that role so that its objects and permissions
belong to the parent role;

3. move all applications to using SCRAM and the new role;

4. disable logins on the old, parent role.

It's currently (2) which is painfully difficult, and could be made less
so via the two features recommended above.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com