Richard Huxton pisze:
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.
Too bad, but thanks much for clarifying this.
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.
Users connect to the DB with different clients - eg. OO.org Base. I
can't change, easily, how it creates a table (withouth modyfing the
source code of Base, or maybe the postgresql-sdbc plugin). It just
issues a "CREATE TABLE". So would MS Access, Kexi etc., which might come
into play later. Solving the issue on the server side would be most
robust. Really not doable?
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.
I guess you assume I can modify the client software, which isn't a
feasible option for me. Can I solve the issue on the server side -
detect the table is open and lock it then, unlock once closed?
Maciek
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
