Re: WIP: SCRAM authentication

Michael,

* Michael Paquier (michael.paquier@gmail.com) wrote:
> On Mon, Feb 15, 2016 at 9:17 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > That said, per various discussions, we'd really want a more-or-less
> > fully formed patch to land prior to the last CF, for this to have any
> > chance.  Perhaps that means it's not going to happen, which would be
> > unfortunate, but it's not beyond the possible, in my view, at least.
>
> Well, I could send a rebased patch with the new things proposed
> upthread, and with things split in as many patches as I can get out,
> roughly:
> 1) One patch for the catalog split
> 2) One for the GUC param controlling recommended protocols
> 3) One for the pg_upgrade function cleaning up automatically the
> entries of unsupported protocols
> 4) SCRAM on top of the rest, which is at more or less 75% something
> that Heikki produced.

That sounds like a pretty reasonable split, to me at least.

> >> > 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.
> >
> > I'm on Michael's side here.  I don't believe it makes sense to try and
> > maintain the exact structure of pg_authid.  We are certainly happy to
> > whack around the other catalogs, and I'm unimpressed with my prior
> > efforts to provide backwards-compatible catalog (see pg_user, et al) for
> > just a few releases- we seem unable to get rid of them now, even though
> > we should have years ago, really.  Indeed, I'd be just as happy if we
> > got rid of them during this work..
>
> We'd need as well to switch pg_shadow to have an array of elements
> made of protocol:identifier instead of a single password field. There
> can be only one valid identifier per protocol, user and valid_until
> for a single point in time, and I can't believe that we should
> recommend only one authentication protocol per single major version of
> Postgres.

Ugh, that sounds pretty grotty to me.

Applications which consider these fields will need to be updated, one
way or the other, and I'd much rather they be updated to work with
reasonable structures rather than something we've hacked together in
some faint hope that it'd be useful.  An array in pg_shadow for a field
which used to be a text field does *not* sound like a simpler solution
to me, and I'd rather simply do away with those views entirely, or at
least nuke the fields which are at issue, than try to come up with
something between wholesale change and no change that ends up being
worse than both.

Thanks!

Stephen