[postgis-users] auto GRANT on new table

[Mark Cave-Ayland]

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