Thread: Before/After Trigger User Switching

============================================================================
                       POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Aasmund Godal
Your email address      : postgresql@aasmund.com


System Configuration
 ---------------------
 Architecture (example: Intel Pentium)         : AMD Athlon XP

 Operating System (example: Linux 2.0.26 ELF)  : OpenBSD 3.1 Stable

 PostgreSQL version (example: PostgreSQL-7.3.4):   PostgreSQL-7.3.4

 Compiler used (example:  gcc 2.95.2)          : gcc version 2.95.3 20010125
(prerelease)


Please enter a FULL description of your problem:
 ------------------------------------------------


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?



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
 ----------------------------------------------------------------------




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)





If you know how this problem might be fixed, list the solution below:
 ---------------------------------------------------------------------