Re: WIP: SCRAM authentication

Michael,

* Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
> On Sat, Feb 13, 2016 at 3:05 AM, David Steele <david(at)pgmasters(dot)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..

There's a lot of good reason to get SCRAM added as a protocol,
considering our current password-based implementation is rather..
lacking. Regarding the specific comment about the timing, that rule is
specifically to prevent large patches from landing in the last CF
without any prior discussion or review, as I recall, so I'm not sure it
really applies here as there's been quite a bit of discussion and review
already.

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.

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

Thanks!

Stephen