Re: Proposal for enhancements of privilege system

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> [ pretty good proposal ]

Just a couple of trivial comments ---

> Some arguments for user sysids I have heard in the past were that some
> people want to keep them the same as the Unix uid.

There may once have been a reason for that, but it's probably buried in
ancient Berkeley-specific admin practices. I sure can't see any good
reason to keep the extra number around now. As you say, it should be
OK to use the pg_shadow row OID to identify users.

BTW I believe most of the "owner" columns in the system tables are
declared as "int4" because they hold sysids ... don't forget to change
'em to be "Oid" when you do this.

> Another reason why an oid based arrangement would be nicer is that if
> we did the same thing for groups why could refer to both users and
> groups through one attribute. See `Implementation' below.

"findoidjoins" will probably get unhappy with you if you do that.
Which is maybe not a big deal, but...

> "prigrantee" is obviously the user to
> which you grant the privilege or a group.
> We could put 0 for "public".

I'd be inclined to provide an additional field that explicitly encodes
"grantee is user", "grantee is group", or "grantee is public". That
way you don't need to do a join to find out what you are looking at.

Really, having an OID column that might reference either users or groups
is the SQL equivalent of a type pun. An alternative representation that
would avoid that would be two OID columns, one to use if user and one
to use if group (if they're both 0 then it's grant to public).

regards, tom lane