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 720723A455F for ; Fri, 7 Jan 2005 09:50:26 +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 13224-04 for ; Fri, 7 Jan 2005 09:50:06 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 9F7713A4587 for ; Fri, 7 Jan 2005 09:50:06 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j079o1TP043923 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Fri, 7 Jan 2005 02:50:05 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j079o11C081580; Fri, 7 Jan 2005 02:50:01 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j079o1XY081579; Fri, 7 Jan 2005 02:50:01 -0700 (MST) (envelope-from mfuhr) Date: Fri, 7 Jan 2005 02:50:01 -0700 From: Michael Fuhr To: santosh dwivedi Cc: pgsql-admin@postgresql.org Subject: Re: how to grant select on table using trigger Message-ID: <20050107095001.GA81396@winnie.fuhr.org> References: <20050107083016.89344.qmail@web52108.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050107083016.89344.qmail@web52108.mail.yahoo.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/41 X-Sequence-Number: 15900 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/