Re: pg_authid.rolpassword format (was Re: Password identifiers, protocol aging and SCRAM protocol)

On 12/16/2016 05:48 PM, Robert Haas wrote:
> On Thu, Dec 15, 2016 at 8:40 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> * Heikki Linnakangas (hlinnaka(at)iki(dot)fi) wrote:
>>> On 12/14/2016 04:57 PM, Stephen Frost wrote:
>>>> * Peter Eisentraut (peter(dot)eisentraut(at)2ndquadrant(dot)com) wrote:
>>>>> On 12/14/16 5:15 AM, Michael Paquier wrote:
>>>>>> I would be tempted to suggest adding the verifier type as a new column
>>>>>> of pg_authid
>>>>>
>>>>> Yes please.
>>>>
>>>> This discussion seems to continue to come up and I don't entirely
>>>> understand why we keep trying to shove more things into pg_authid, or
>>>> worse, into rolpassword.
>>>
>>> I understand the relational beauty of having a separate column for
>>> the verifier type, but I don't think it would be practical.
>>
>> I disagree.
>
> Me, too. I think the idea of moving everything into a separate table
> that allows multiple verifiers is probably not a good thing to do just
> right now, because that introduces a bunch of additional issues above
> and beyond what we need to do to get SCRAM implemented. There are
> administration and policy decisions to be made there that we should
> not conflate with SCRAM proper.
>
> However, Heikki's proposal seems to be that it's reasonable to force
> rolpassword to be of the form 'type:verifier' in all cases but not
> reasonable to have separate columns for type and verifier. Eh?

I fear we'll just have to agree to disagree here, but I'll try to
explain myself one more time.

Even if you have a separate "verifier type" column, it's not fully
normalized, because there's still a dependency between the verifier and
verifier type columns. You will always need to look at the verifier type
to make sense of the verifier itself.

It's more convenient to carry the type information with the verifier
itself, in backend code, in pg_dump, etc. Sure, you could have a
separate "transfer" text format that has the prefix, and strip it out
when the datum enters the system. But it is even simpler to have only
one format, with the prefix, and use that everywhere.

It might make sense to add a separate column, to e.g. make it easier to
e.g. query for users that have an MD5 verifier. You could do "WHERE
rolverifiertype = 'md5'", instead of "WHERE rolpassword LIKE 'md5%'".
It's not a big difference, though. But even if we did that, I would
still love to have the type information *also* included with the
verifier itself, for convenience. And if we include it in the verifier
itself, adding a separate type column seems more trouble than it's worth.

For comparison, imagine that we added a column to pg_authid for a
picture of the user, stored as a bytea. The picture can be in JPEG or
PNG format. Looking at the first few bytes of the image, you can tell
which one it is. Would it make sense to add a separate "type" column, to
tell what format the image is in? I think it would be more convenient
and robust to rely on the first bytes of the image data instead.

- Heikki