On Friday 22 February 2008 06:58:59 Maciej Sieczka wrote: Hi Maciej,
> My first post here. Hi All! > > Sorry if the issue was already discussed. I have searched the net and > your archives for several hours not finding an answer. > > There are 2 role groups in my cluster: "editors" and "viewers". In each > group there are several users. > > In the DB, "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 > edit it. I'd like to enable other "editors" to edit the table too, > without having to manually GRANT rights every time a new table is created. > > What should I do to automatically grant SELECT, INSERT, UPDATE, DELETE, > REFERENCES on a table for all "editors", and SELECT for all "viewers", > automatically when the table is created? What do the permissions look like when the table has been created? I suspect that you may need to issue an explicit "SET ROLE" statement for editors just after you connect to the database. See http://www.postgresql.org/docs/8.2/interactive/sql-set-role.html for more information. > From reading so far I suppose I could create a function which calls an > appropriate GRANT, and trigger it when a new record is added to > "pg_tables". Is that a way to go? Is there an easier one? > > An extra, but desired functionality, would be if I could also prevent > other "editors" from modifying the data *if* it is being currently being > edited by some user. Is that feasible at all? If you're using a new version of PostgreSQL as you are, the best way to go is probably advisory locks - see http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html#ADVISORY-LOCKS for more information. ATB, Mark. -- Mark Cave-Ayland Sirius Corporation - The Open Source Experts http://www.siriusit.co.uk T: +44 870 608 0063 _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
