Re: grant select on all tables

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