grant select on all tables

From: andrew(at)borley-hall(dot)eclipse(dot)co(dot)uk
To: pgsql-novice(at)postgresql(dot)org
Subject: grant select on all tables
Date: 2005-10-03 14:49:20
Message-ID: 1128350960.434144f0732c4@webmail.eclipse.net.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Browse pgsql-novice by date

  From Date Subject
Next Message operationsengineer1 2005-10-03 16:05:18 dump database question - remote db accessed via vpn
Previous Message Bruce Momjian 2005-10-03 13:47:39 Re: Transaction within plpgsql