Before/After Trigger User Switching - Mailing list pgsql-sql

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: wwwat_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