ACL enhancements (was Re: [HACKERS] Re: [PATCHES] NO-CREATE-TABLE and NO-LOCK-TABLE)

Karel Zak - Zakkr writes:

> No. How often you LOCKing table? If you work in transaction block and DB
> design is good (or very simple), you not need very often the LOCK.

I agree with providing access restrictions for locking tables in ShareLock
and above, perhaps via a GRANT LOCK ON TABLE command. But just saying
"Don't use LOCK" isn't going to cut it, it must be done throughout *all*
commands that do any locking, otherwise it's just inconsistent.

You have a point that these higher level locks aren't really anybody's
business other than the table owner, superusers, and those explicitly
granted access to them. But saying that you can run your database without
locks is false because even plain UPDATE gets a row exclusive lock.

> I a little speculated about it and IHO is real possible make CRATE ROLE,
> CREATE PROFILE and global pg_acl table and extend GRANT (function,alter..).
> See example Oracle8 documentation (example on: http://mravenec.jcu.cz/oracle)
> it is more readable than SQL standards :-)

I don't have any real problems with reading SQL standards. I'd rather do
that than submit to some vendor's ideas. Having said that, I'll still read
the above, especially because profiles are not in SQL.

I have given some more thought to the design of the pg_acl table (which
should not be global if it wants to be SQL compliant). I realize that the
size of my proposed 'one row per user/object/privilege' can grow rather
huge (20 users, 100 tables/things -> probably ca. 5000 rows) but I see
this as the best way some of the things (column access, grant options,
roles) can be implemented in the first place and it will be much easier to
verify the implementation because you can read it right out of SQL.

I think caching can be done pretty effectively, too, since ACL items
rarely change once they're set up. I'm inclined to ask other people's
opinions on this item. Other than that, I think we have a winner
here. Time to bring this up the the rest of the folks and draw up a
project page ...

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