when u connect to the database type:
\h GRANT
and you will get all the Grant options:
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
This will grant the privileges on all tables under the database ....
HTH
Gene <genekhart@gmail.com> wrote:
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;
Get your own
web address for just $1.99/1st yr. We'll help.
Yahoo! Small Business.