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;
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 |