Hello,
I have asked the question on novice ML but it seems there is not much
traffic there. I hope nobody minds I'm re-asking here, on a much more
active list. The problem is as follows:
There are 2 role groups in my cluster: "editors" and "viewers". In each
group there are several users.
In the DB, members of "editors" are allowed to create new tables in one
schema. My problem is that only the very user who created the table is
allowed to view and edit it, by default.
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?
If the function+trigger approach is optimal, can Anybody share a working
example? I have a muddy idea it would be something like this pseudo-code:
CREATE TRIGGER trig_acl
AFTER INSERT
ON pg_catalog.pg_class FOR EACH ROW
EXECUTE PROCEDURE fnct_acl();
CREATE FUNCTION fnct_acl () RETURNS opaque AS '
DECLARE
table_name TEXT #how do I fetch the table name???
BEGIN
GRANT ALL ON myschema.table_name TO editors;
END;
' LANGUAGE 'plpgsql';
What should I modify to make it real code?
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?
Thank you for any pointers!
Regards,
Maciek
P.S.
I searched the archives and only found this [1] thread related, but it
does not provide an obvious (for me) solution.
[1]http://www.nabble.com/grant-select-on-all-tables-to1029307.html#a1029307
