Re: grant select on all tables

pgsql-novice-owner(at)postgresql(dot)org wrote on 04/10/2005 13:25:44:

> On 10/4/05 6:56 AM, "Andrew Borley" <BORLEY(at)uk(dot)ibm(dot)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