From: | Najib Abi Fadel <nabifadel(at)yahoo(dot)com> |
---|---|
To: | gene(at)sotech(dot)us, pgsql-general(at)postgresql(dot)org |
Subject: | Re: grant select on all tables of schema or database |
Date: | 2006-09-28 06:15:27 |
Message-ID: | 20060928061527.75803.qmail@web38312.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Vodep | 2006-09-28 07:26:15 | Full Text fuzzy search |
Previous Message | smartdude | 2006-09-28 06:12:04 | Strange pg_ctl behavior: postmaster shuts down on shell interrupt |