grant select on all tables of schema or database

From: Gene <genekhart(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: grant select on all tables of schema or database
Date: 2006-09-28 04:21:40
Message-ID: 430d92a20609272121j2639ba7enf687c0be7523074a@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

It seems like it should be a very easy problem to solve I just need one role
to have select privileges on all the tables of a particular schema or
database including any new tables that are created since they are created
programmatically daily. I've combed google and the docs to no avail. Do I
need to write pg/plsql scripts just to so something like that?? I believe on
other dbms you can just say grant all on schema.* or something to that
effect. The script i found below would be ok if tables weren't being created
constantly.

using version 8.1.4

thanks,
Gene

----
CREATE OR REPLACE FUNCTION pgx_grant(text, text, text)
RETURNS int4 AS
$BODY$
DECLARE
priv ALIAS FOR $1;
patt ALIAS FOR $2;
user ALIAS FOR $3;
obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class
WHERE relname LIKE patt || '%' AND relkind in ('r','v','S') LOOP
EXECUTE 'GRANT ' || priv || ' ON ' || obj.relname || ' TO ' || user;
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION pgx_grant(text, text, text) OWNER TO root;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John Sidney-Woollett 2006-09-28 06:09:43 Re: cyclical redundancy checksum algorithm(s)?
Previous Message blackjadelin 2006-09-28 04:02:47 Re: cannt setup postgresql database for my opennms