[postgis-users] auto GRANT on new table

[Obe, Regina]

Maciej,

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.

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

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


CREATE OR REPLACE FUNCTION cp_grantpermission(param_schema varchar,
param_group varchar)
  RETURNS void AS
$$
DECLARE sqlgrant varchar;
BEGIN 
sqlgrant := array_to_string(ARRAY(SELECT 'GRANT SELECT ON TABLE ' ||
t.table_schema || '.' || t.table_name || ' TO ' || param_group || ';'
FROM information_schema.tables t 
	WHERE t.table_schema = param_schema 
	AND NOT EXISTS(SELECT p.table_name FROM
information_schema.table_privileges p 
		WHERE p.table_schema = t.table_schema AND p.table_name =
t.table_name AND p.privilege_type ='SELECT' 
		AND p.grantee = param_group)
UNION
SELECT 'GRANT UPDATE ON TABLE ' || t.table_schema || '.' || t.table_name
|| ' TO ' || param_group || ';' FROM information_schema.tables t 
	WHERE t.table_schema = param_schema AND
	NOT EXISTS(SELECT p.table_name FROM
information_schema.table_privileges p 
		WHERE p.table_schema = t.table_schema AND p.table_name =
t.table_name AND p.privilege_type ='UPDATE'
		AND p.grantee = param_group)
UNION
SELECT 'GRANT DELETE ON TABLE ' || t.table_schema || '.' || t.table_name
|| ' TO ' || param_group || ';' FROM information_schema.tables t 
	WHERE t.table_schema = param_schema  AND
	NOT EXISTS(SELECT p.table_name FROM
information_schema.table_privileges p 
		WHERE p.table_schema = t.table_schema AND p.table_name =
t.table_name AND p.privilege_type ='DELETE'
		AND p.grantee = param_group)
UNION
SELECT 'GRANT INSERT ON TABLE ' || t.table_schema || '.' || t.table_name
|| ' TO ' || param_group || ';' FROM information_schema.tables t 
	WHERE t.table_schema = param_schema AND
	NOT EXISTS(SELECT p.table_name FROM
information_schema.table_privileges p 
		WHERE p.table_schema = t.table_schema AND p.table_name =
t.table_name AND p.privilege_type ='INSERT'
		AND p.grantee = param_group)), E'\r');

IF sqlgrant > '' THEN
	EXECUTE(sqlgrant); 
END IF;
END;
$$
  LANGUAGE 'plpgsql' VOLATILE;

And then you would call it by doing this

SELECT cp_grantpermission('editing', 'editors');


If My answer and Mark's prior answer do not suffice, 
 
you might have better luck asking this question on
http://archives.postgresql.org/pgsql-general/ since it really has
nothing to do with PostGIS.

Hope that helps,
Regina


 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Maciej Sieczka
Sent: Friday, February 22, 2008 1:59 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] auto GRANT on new table

Hello,

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!

Maciek

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.