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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
