Message-ID: <20030820171250.15333.qmail@one.kreator.com>
From: "Aasmund Midttun Godal" <postgresql@aasmund.com>
To: pgsql-sql@postgresql.org
Subject: Before/After Trigger User Switching
Date: Wed, 20 Aug 2003 19:12:49 +0200
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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