[postgis-users] auto GRANT on new table

[Maciej Sieczka]

Mark Cave-Ayland pisze:
> On Friday 22 February 2008 06:58:59 Maciej Sieczka wrote:

Hi Mark!

>> 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?

The owner is set to the role who created the table:

base# \dt schema1.table1
               List of relations
    Schema   |  Name   | Type  |    Owner
------------+---------+-------+--------------
  schema1    | table1  | table | user1
(1 row)


No explicit ACLs are set (which means ACL arwdxt for the owner and none 
for everybody else AFAIK):

base=# \dp schema1.table1
        Access privileges for database "database"
    Schema   |  Name   | Type  | Access privileges
------------+---------+-------+-------------------
  schema1    | table1  | table |
(1 row)


According to [1] these are OK, default settings, by design. I need to 
modify this default behaviour, somehow, so that the ACL was set to arwdx 
for the group "editors", and to r for "viewers", at the moment when the 
table is created. But how? Anybody?

> 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.

I'd rather not. I'd like to preserve the original table's owner name,
to keep the track of who created which table. But anyway -
if I go for it, how would I make the given SET ROLE instruction be
executed instantly when the user connects to the database; technicaly?

>> 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.

Thanks for the pointer! Now on to reading and breaking the database (more).

[1]http://www.postgresql.org/docs/8.2/static/sql-grant.html