Re: AW: AW: Proposal for enhancements of privilege system

Tom Lane writes:

> having a separate tuple for each individual kind of access right will
> consume an unreasonable amount of space --- both on disk and in the
> syscache, if a cache is used for this table.

That's a valid concern, but unfortunately things aren't that easy. For
each access right you also have to store what user granted that privilege
and whether it's grantable, and for SELECT also whether it includes the
"hierarchy option" (has to do with table inheritance somehow).

Say you store all privileges in an array, then you'd either need to encode
all 3 1/2 pieces of information into one single data type and make an
array thereof (like `array of record privtype; privgrantor;
privgrantable'), which doesn't really make things easier, or you have
three arrays per tuple, which makes things worse. Also querying arrays is
painful.

So the alternative is to have separate columns per privilege, like

pg_privilege ( priobj, prigrantee,priupdate, priupdateisgrantable, priupdategrantor,priselect, priselectisgrantable,
priselectgrantor,... /* delete, insert, references */
 
)

The originally proposed schema would be 14 bytes data plus overhead. This
new idea would cost 38 bytes of data. As I understand, the overhead is 40
bytes. So the break-even point for this new scheme is when users have on
average at least 1.4 privileges (78/54) granted to them on one object.
Considering that such objects as types and functions will in any case have
at most one privilege (USAGE or EXECUTE, resp.), that there are groups (or
roles), that column level privileges will probably tend to have sparse
tuples of this kind, and that object owners are short-circuited in any
case, then it is not at all clear whether that figure will be reached.


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden