Thread: how to auto GRANT rights on a new table?

Hello,

Greetings Everyone! This is my first post to your list.

I have a novice problem as follows:


There are 2 role groups in my cluster: "editors" and "viewers". In each
group there are several users.

In the DB, members of "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 need to modify this PostgreSQL's default behaviour, so that the ACL on
a new table in this schema is set to arwdx for the group "editors", and
to r for "viewers", without having to manually GRANT rights each time a
new table is created. I don't know how.

 From reading so far I suppose I should 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?

If the function+trgger approach is optimal, can Anybody show me how they
should look and how to deploy them? I have a muddy idea they would
something like this pseudo-code:

CREATE TRIGGER trig_acl
   AFTER INSERT
   ON pg_catalog.pg_tables WHERE schemaname='dss_postgis' FOR EACH ROW
   EXECUTE PROCEDURE fnct_acl();

CREATE FUNCTION fnct_acl () RETURNS opaque AS '
   DECLARE
     table_name TEXT #how do I fetch the table name???
   BEGIN
     GRANT ALL ON myschema.table_name TO editors;
   END;
' LANGUAGE 'plpgsql';

What should I modify to make it real code?


An extra, but desired functionality, would be if I could also
prevent other "editors" from modifying the table *if* it is being
currently being edited by some user. Is that feasible at all?

Thank you for any pointers!

Regards,
Maciek

P.S.
I searched the archives and only found this [1] thread related, but it
does not provide an obvious (for me) solution.

[1]http://www.nabble.com/grant-select-on-all-tables-to1029307.html#a1029307

Maciej Sieczka pisze:

> Greetings Everyone! This is my first post to your list.

Forgot the specs: Postgres 8.2.6, Ubuntu Gusty.

Best,
Maciek

Obe, Regina pisze:
> I had posted this earlier to postgis group, but apparently it didn't
> go thru.

It seems so. My further posts there didn't go through either.

> I take it that you have no control over how these people are creating
>  tables.  e.g they are not creating it via some custom application of
>  yours where the solution would be obvious.

These are PostGIS tables created with QGIS Python plugin, which I could
modify to set desired ACL, if there's no other way. However, there are
also non-spatial tables created with OO.org Base, of which I don't have
that control. Moreover, other client software creating tables in future
might come to play. Thus, it would be most robust to solve the issue on
the server side, not to have to manage the software on the client side
in this regard at all.

> Unfortunately you can't put a trigger on pg_tables because that is
> not a table but a read-only view.

I see. How about a trigger on pg_class table directly? Would it be doable?

> The way I usually handle this issue is to just have a plpgsql stored
> function that gets called by some hourly process running that grants
> permissions to tables that don't already have them. e.g. using
> something like pgAgent or crontab.  Your function would look
> something like

Thank you very much for your example. I'd still prefer a solution which
sets ACL instantly when the table is created though. Really no way?

Maciek