Re: how to auto GRANT custom ACL on a new table?

Maciej Sieczka wrote:
> I need to modify this default PostgreSQL's behaviour, so that the ACL on
> a new table in this schema is set to SELECT, INSERT, UPDATE, DELETE,
> REFERENCES for "editors", and SELECT for "viewers", without having to 
> manually GRANT rights each time a new table is created. I can't control 
> this setting from the client software as these are various programs, and 
> even if I could it'd be still better anyway to have it controlled in one 
> single place on the server side. But how?
> 
>  From reading so far I *suppose* I should create a function which calls
> an appropriate GRANT, and trigger it when a new record is added to
> "pg_class". Is this doable?

Afraid not. You can't add a trigger to a system table.

The only real solution is to have your own function do both - create the 
table and then grant relevant permissions. Only allow client 
applications permissions to execute the function, not create tables 
directly.

> An extra, but desired functionality, would be if I could also prevent 
> other "editors" from modifying the table *if* it is being currently 
> being edited by somebody. Is that feasible at all?

Sure, just issue LOCK TABLE ... at the start of the transaction.

-- 
   Richard Huxton
   Archonet Ltd