X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E10F73A4284 for ; Fri, 7 Jan 2005 10:34:03 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22905-05 for ; Fri, 7 Jan 2005 10:33:49 +0000 (GMT) Received: from web52103.mail.yahoo.com (web52103.mail.yahoo.com [206.190.39.72]) by svr1.postgresql.org (Postfix) with SMTP id 8C0DE3A4591 for ; Fri, 7 Jan 2005 10:33:50 +0000 (GMT) Received: (qmail 19569 invoked by uid 60001); 7 Jan 2005 10:33:50 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=nC49xwbIL9jiR5uXG0lzQJ5zjZQS6F9+JkEong+akj5nDYLPkpoMEv696AILhWkUfpOGQRHc3BFNu6C3zxojlOPwhe2ujv4lVQ6HI4kL4uUUrHoonHjY8vZhmCaMLLoLySGnqMB7uF4x/dU+uTB8Qio8/iX3lJnZhD6uPPeGGvo= ; Message-ID: <20050107103350.19567.qmail@web52103.mail.yahoo.com> Received: from [218.111.252.82] by web52103.mail.yahoo.com via HTTP; Fri, 07 Jan 2005 02:33:50 PST Date: Fri, 7 Jan 2005 02:33:50 -0800 (PST) From: santosh dwivedi Subject: Re: how to grant select on table using trigger To: Michael Fuhr Cc: pgsql-admin@postgresql.org In-Reply-To: <20050107095001.GA81396@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-312745978-1105094030=:18472" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.489 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_10_20, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200501/42 X-Sequence-Number: 15901 --0-312745978-1105094030=:18472 Content-Type: text/plain; charset=us-ascii Hi i want to use trigger because my one of application connects to databse and creates tables in database dynamically , other user want to acces it . as when application creates table in databse it does not GRANT select to other users. thats why i thought to grant permission by trigger. i know the newly created table name is listed in one Table called queries. can you pls write sample code so that i can grant permission on tables created by application thanks in advance santosh dwivedi Michael Fuhr wrote: 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/ --------------------------------- Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. --0-312745978-1105094030=:18472 Content-Type: text/html; charset=us-ascii
Hi
i want to use trigger because my one of application connects to databse and creates tables in database dynamically , other user want to acces it . as when application creates table in databse it does not GRANT select to other users. thats why i thought to grant permission by trigger.
 
i know the newly created table name is listed in one Table called queries. can you pls write sample code so that i can grant permission on tables created by application
thanks in advance
santosh dwivedi  

Michael Fuhr <mike@fuhr.org> wrote:
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 "Execu ting 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/


Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone. --0-312745978-1105094030=:18472--