Re: WIP: SCRAM authentication

On Mon, Feb 15, 2016 at 9:17 AM, Stephen Frost <sfrost@snowman.net> wrote:
> Michael,
>
> * Michael Paquier (michael.paquier@gmail.com) wrote:
>> On Sat, Feb 13, 2016 at 3:05 AM, David Steele <david@pgmasters.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.

Honestly I don't know what to answer to that.

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

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