On Fri, Jan 07, 2005 at 12:30:16AM -0800, santosh dwivedi wrote:
> i want to grant select permission to a group on table that will
> be created by me in future. so i used trigger and a function
Why use a trigger? Why not just issue a GRANT statement when you
create the table? What problem are you trying to solve?
> CREATE FUNCTION permitselect () RETURNS opaque AS '
What version of PostgreSQL are you using? In 7.3 and later triggers
should return TRIGGER, not OPAQUE.
> DECLARE
You don't need a DECLARE section if you don't declare anything.
> BEGIN
> GRANT SELECT ON NEW to GROUP wp;
NEW is the row being inserted or updated; GRANT needs a table name.
Are you trying to grant permissions on a table named in one of the
row's fields? Maybe you want something like this:
EXECUTE ''GRANT SELECT ON '' || quote_ident(NEW.tablename) || '' TO GROUP wp'';
(If you're using 8.0 then you could dollar-quote the function body
and avoid the need to escape the single quotes.)
> CREATE TRIGGER permit_select
> AFTER INSERT OR UPDATE
> ON queries FOR EACH ROW
> EXECUTE PROCEDURE permitselect();
>
> queries is table where meta data of tables created by me is stored.
> so new table created will be listed in table queries.
What kind of metadata are you storing? Hopefully you're not
duplicating anything that's already in the system catalogs.
> but on execution it gives error:
> NEW used in non-rule query
> Error occurred while executing PL/pgSQL function permit
The GRANT statement needs to reference a table name, not a row being
inserted or updated. If the row contains a field that names the
table, then use NEW.fieldname. Also, you'll need to use EXECUTE
as shown above (see "Executing Dynamic Commands" in the PL/pgSQL
documentation).
> Can any body help me how i can write a trigger for granting
> permission to select a table as soon it is created
Apparently you're already doing an INSERT after CREATE TABLE -- why
not issue a GRANT statement as well? Why use a trigger?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
