X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id F32F1D1B9AF for ; Wed, 20 Aug 2003 16:34:26 -0300 (ADT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 69910-05 for ; Wed, 20 Aug 2003 16:34:18 -0300 (ADT) Received: from one.kreator.com (one.kreator.com [213.187.162.222]) by svr1.postgresql.org (Postfix) with SMTP id 46FA7D1BA18 for ; Wed, 20 Aug 2003 16:34:15 -0300 (ADT) Received: (qmail 15196 invoked by uid 1000); 20 Aug 2003 19:34:15 -0000 Message-ID: <20030820193415.23473.qmail@one.kreator.com> References: <20030820171250.15333.qmail@one.kreator.com> In-Reply-To: <20030820171250.15333.qmail@one.kreator.com> From: "Aasmund Midttun Godal" To: "Aasmund Midttun Godal" Cc: pgsql-sql@postgresql.org Subject: Re: Before/After Trigger User Switching Date: Wed, 20 Aug 2003 21:34:15 +0200 Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/235 X-Sequence-Number: 14851 I have upgraded to 7.3.4 and the problem persists. Aasmund Midttun Godal writes: > When an operation is done throug a SECURITY DEFINER style function and > causes a trigger the current_user depends on whether it is executed BEFORE > or AFTER. I currently run 7.3.2. I believe the BEFORE behavior is correct > while the AFTER behavior is wrong. A bug? already fixed? > > regards, > > Aasmund. > > > CREATE TABLE a (b text); > > > > CREATE OR REPLACE FUNCTION public.ut () RETURNS TRIGGER SECURITY INVOKER > LANGUAGE 'plpgsql' AS' > DECLARE > t TEXT; > t2 TEXT; > BEGIN > t := current_user; > t2 := session_user; > RAISE NOTICE ''% Current: % Session: %'', TG_WHEN, t, t2; > RETURN NEW; > END > '; > > CREATE TRIGGER "ut_trig_before" BEFORE UPDATE OR INSERT OR DELETE > ON a FOR EACH ROW EXECUTE PROCEDURE public.ut(); > > CREATE TRIGGER "ut_trig_after" AFTER UPDATE OR INSERT OR DELETE > ON a FOR EACH ROW EXECUTE PROCEDURE public.ut(); > > > > CREATE OR REPLACE FUNCTION public.at_test () RETURNS TEXT SECURITY DEFINER > LANGUAGE 'sql' AS' > INSERT INTO a VALUES (current_user); > SELECT session_user::TEXT || ''/'' ||current_user::TEXT; > '; > > > > db=> SELECT at_test(); > NOTICE: BEFORE Current: godal Session: www > NOTICE: AFTER Current: www Session: www > at_test > ----------- > www/godal > (1 row) > > > > > Aasmund Midttun Godal > > aasmund@godal.com - http://godal.com > +47 40 45 20 46 - Leiv Tronstadsvei 2, NO-7051 Trondheim > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) Aasmund Midttun Godal aasmund@godal.com - http://godal.com +47 40 45 20 46 - Leiv Tronstadsvei 2, NO-7051 Trondheim