Pgsql developers:
Based on a question posed me by David Fetter of Bricolage, I realized that we
have what looks to me to be a serious inconsistency in our permissions model.
Please ignore me if this has already been proposed and acted on.
SELECT, UPDATE, DELETE, RULE, TRIGGER can all be GRANTed. However, ALTER /
DROP cannot be granted ... they belong only to the table owner and the
superuser, who then have no restrictions on what they can do with the table.
In a database system with many command-line users, it is quite possible that
an admin would want to GRANT some users the ability to ALTER some tables in
the public schema, without either DROPing them or granting permission on
*all* tables.
Therefore I propose the following two additional permissions on TABLEs, VIEWs,
and FUNCTIONs:
GRANT ALTER ON object TO user : allows the user to run ALTER or REPLACE
statements on the object;
GRANT DROP ON object TO user : allows the user to DROP the object (obviously a
permission that could only be used once).
Accompanying these should be an extension of triggers to allow logging, etc.,
of such activity. Namely:
CREATE TRIGGER tg_name {BEFORE|AFTER} ALTER ON table
CREATE TRIGGER tg_name BEFORE DROP ON table
CREATE
These would allow more sophisticated action to be taken on the execution of
DDL statements. DROP triggers would be BEFORE only, for obvious reasons.
--
Josh Berkus
Aglio Database Solutions
San Francisco