
> > 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

Tue, 30 May 2000 15:39:28 +0200
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: AW: Proposal for enhancements of privilege system
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



Fri, 2 Jun 2000 02:37:12 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: AW: AW: Proposal for enhancements of privilege system
> > 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


Sun, 4 Jun 2000 13:33:53 +0200
"Zeugswetter Andreas" <andreas.zeugswetter@telecom.at>
Re: AW: AW: Proposal for enhancements of privilege system
"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

Sun, 04 Jun 2000 14:47:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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


Tue, 6 Jun 2000 03:36:06 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: AW: AW: Proposal for enhancements of privilege system