On 12/16/2016 05:48 PM, Robert Haas wrote:
On Thu, Dec 15, 2016 at 8:40 AM, Stephen Frost <sfr...@snowman.net> wrote:
* Heikki Linnakangas (hlinn...@iki.fi) wrote:
On 12/14/2016 04:57 PM, Stephen Frost wrote:
* Peter Eisentraut (peter.eisentr...@2ndquadrant.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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to