Thread: grant select on all tables
Hi, I need to have default privileges like "GRANT SELECT ON ALL TABLES" for a particular user. Looking at the docs & mailing list archives there's no easy way to do this. I seem to have to run a grant statement every time a new table is created, so I thought of running the grant automatically as a trigger on the pg_tables table. I got as far as creating a function to help me do the grant part: create or replace function grantAccess(text, text) returns text as ' DECLARE grantStmt text; BEGIN grantStmt := \'grant select on \'||$1||\' to \'||$2; EXECUTE grantStmt; RETURN grantStmt; END;' language plpgsql returns null on null input; I wanted to run the function in a trigger on the pg_tables table, but I can't cos it's a view. So I tried setting it on the pg_class table, but I can't do this as it's a system catalog. Can anyone help? Can anyone see a better/easier way of doing it? Cheers Andrew
On 10/4/05 6:56 AM, "Andrew Borley" <BORLEY@uk.ibm.com> wrote: > Hi, > > I need to have default privileges like "GRANT SELECT ON > ALL TABLES" for a particular user. Looking at the docs & > mailing list archives there's no easy way to do this. I > seem to have to run a grant statement every time a new > table is created, so I thought of running the grant > automatically as a trigger on the pg_tables table. > > I got as far as creating a function to help me do the > grant part: > > create or replace function grantAccess(text, text) > returns text as ' > DECLARE grantStmt text; > BEGIN > grantStmt := \'grant select on \'||$1||\' to \'||$2; > EXECUTE grantStmt; > RETURN grantStmt; > END;' > language plpgsql returns null on null input; > > I wanted to run the function in a trigger on the > pg_tables table, but I can't cos it's a view. So I tried > setting it on the pg_class table, but I can't do this as > it's a system catalog. > > Can anyone help? > Can anyone see a better/easier way of doing it? Try looking at: http://pgedit.com/node/20 for some ideas. Sean
pgsql-novice-owner@postgresql.org wrote on 04/10/2005 13:25:44: > On 10/4/05 6:56 AM, "Andrew Borley" <BORLEY@uk.ibm.com> wrote: > > > Hi, > > > > I need to have default privileges like "GRANT SELECT ON > > ALL TABLES" for a particular user. Looking at the docs & > > mailing list archives there's no easy way to do this. I > > seem to have to run a grant statement every time a new > > table is created, so I thought of running the grant > > automatically as a trigger on the pg_tables table. > > > > I got as far as creating a function to help me do the > > grant part: > > > > create or replace function grantAccess(text, text) > > returns text as ' > > DECLARE grantStmt text; > > BEGIN > > grantStmt := \'grant select on \'||$1||\' to \'||$2; > > EXECUTE grantStmt; > > RETURN grantStmt; > > END;' > > language plpgsql returns null on null input; > > > > I wanted to run the function in a trigger on the > > pg_tables table, but I can't cos it's a view. So I tried > > setting it on the pg_class table, but I can't do this as > > it's a system catalog. > > > > Can anyone help? > > Can anyone see a better/easier way of doing it? > > Try looking at: > > http://pgedit.com/node/20 > > for some ideas. > > Sean > Thanks for the link, Sean. There are some useful-looking functions but I'm still unsure as to how I would get any of them to automatically run. Can you set triggers on system catalog tables? Are there any alternative ways of doing this? Cheers Andrew
On Tue, Oct 04, 2005 at 16:00:38 +0100, Andrew Borley <BORLEY@uk.ibm.com> wrote: > > Can you set triggers on system catalog tables? No. > Are there any alternative ways of doing this? It might make things easier to use groups so that you can give access to a group whenever creating any table and then put people in that group that are supposed to have that access. If you are worried about people forgetting to do this, you could write a script that checks for tables that don't have access from that group and that adds it if needed. You could run the script daily as part of a cron job.