[postgis-users] auto GRANT on new table

Maciej Sieczka tutey at o2.pl
Thu Feb 21 22:58:59 PST 2008


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!


More information about the postgis-users mailing list