Thread: AW: AW: Proposal for enhancements of privilege system
> > Of course the grant revoke is the same. But administrative tools > > usually allow you to dump schema, all rights, triggers ... for an > > object and thus need access to the system tables containing the > > grants. > > That's what you use the information schema views for. Ok. > Also, of course, > we're light years away from having anything like a portable pg_dump. Hmm ? I am not talking about pg_dump, I am talking about some graphical tool that shows the table structure and grants. > > > Imho this is an area that is extremly sensitive to performance, the > > rights have to be checked for each access. > > But using some sort of arrays is going to make it slower in > any case since > you can't use indexes on those. Again Hmm ? Are you going to do select * from <authtable> where pri="select" or some such ? Usually you look up a users rights for a specific table, and that needs to be fast. Andreas
Zeugswetter Andreas SB writes: > Again Hmm ? Are you going to do select * from <authtable> where pri="select" > or some such ? Usually you look up a users rights for a specific table, > and that needs to be fast. Exactly, that's why I have to do it like this. To interface a system catalog to the shared cache you need a primary key, which would be (object, user, action) in my proposal. With that setup I can easily make queries of the sort "does user X have select right on table Y" as fast as possible, no slower than, say, looking up an attribute definition in pg_attribute. With several privileges per row you make the table unnecessarily sparse, you make interfacing to the catalog cache a nightmare, and you create all sorts of funny implementation problems (for example, revoking a privilege might be an update or a delete, depending on whether it was the last privilege revoked). -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> > Again Hmm ? Are you going to do select * from <authtable> where pri="select"
> > or some such ? Usually you look up a users rights for a specific table,
> > and that needs to be fast.
>
> Exactly, that's why I have to do it like this. To interface a system
> catalog to the shared cache you need a primary key, which would be
> (object, user, action) in my proposal. With that setup I can easily make
> queries of the sort "does user X have select right on table Y" as fast as
> possible, no slower than, say, looking up an attribute definition in
> pg_attribute.
Ok, I see that you will somtimes want to do a select like that, only I do
not see the reason why this has to be the primary target for speed.
Remember that for each row in the db you have >30 bytes of overhead
(I forgot the exact number) plus table_oid + user_oid thus if a user has
all permissions on a table, that will take 300 bytes.
I also think that a key of object + {user|group} is imho selective enough,
you don't want a key whose only info is a boolean.
Andreas
"Zeugswetter Andreas" <andreas.zeugswetter@telecom.at> writes:
>> Exactly, that's why I have to do it like this. To interface a system
>> catalog to the shared cache you need a primary key, which would be
>> (object, user, action) in my proposal. With that setup I can easily make
>> queries of the sort "does user X have select right on table Y" as fast as
>> possible, no slower than, say, looking up an attribute definition in
>> pg_attribute.
> Ok, I see that you will somtimes want to do a select like that, only I do
> not see the reason why this has to be the primary target for speed.
> Remember that for each row in the db you have >30 bytes of overhead
> (I forgot the exact number) plus table_oid + user_oid thus if a user has
> all permissions on a table, that will take 300 bytes.
> I also think that a key of object + {user|group} is imho selective enough,
> you don't want a key whose only info is a boolean.
I tend to agree with Andreas on this: 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. (In the cache, that translates to entries not living very long
before they fall off the LRU list.)
regards, tom lane
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