[postgis-users] auto GRANT on new table

Kevin Neufeld kneufeld at refractions.net
Fri Feb 22 10:25:22 PST 2008

Hi Maciek,

As far as I know, you can't add triggers to system tables.  
Consequently, I don't know of anyway to automatically GRANT rights to a 
newly created table, except for a nightly run cron job you could set up.

To answer your second question, yes, you can prevent other "editors" 
from modifying your data concurrently.  This can be accomplished by 
performing an explicit LOCKs on the entire table or rows of the table 
you plan on editing.  I often do this for grid computing projects where 
many users modify the same table possibly at the same time, each 
requiring an static view of the project's status.  I use the postgres's 
table locking mechanism like semaphores, manually specifying the start 
and end of my editing transaction. It works like a charm.

For further reading...


Maciej Sieczka wrote:
> Hello,
> 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?
> 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?
> I'm still quite new in Postgres...
> Using Postgres 8.2.6, PostGIS 1.3.2 on Ubuntu Gusty.
> Thanks for any help!
> Maciek
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

More information about the postgis-users mailing list