pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBefore/After Trigger User Switching
5+ messages / 2 participants
[nested] [flat]
* Before/After Trigger User Switching
@ 2003-08-20 17:12 Aasmund Midttun Godal <postgresql@aasmund.com>
0 siblings, 2 replies; 5+ messages in thread
From: Aasmund Midttun Godal @ 2003-08-20 17:12 UTC (permalink / raw)
To: 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: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Before/After Trigger User Switching
@ 2003-08-20 19:34 Aasmund Midttun Godal <postgresql@aasmund.com>
parent: Aasmund Midttun Godal <postgresql@aasmund.com>
1 sibling, 0 replies; 5+ messages in thread
From: Aasmund Midttun Godal @ 2003-08-20 19:34 UTC (permalink / raw)
To: Aasmund Midttun Godal <postgresql@aasmund.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Before/After Trigger User Switching
@ 2003-08-20 20:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aasmund Midttun Godal <postgresql@aasmund.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2003-08-20 20:12 UTC (permalink / raw)
To: Aasmund Midttun Godal <postgresql@aasmund.com>; +Cc: pgsql-sql
"Aasmund Midttun Godal" <postgresql@aasmund.com> 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?
An AFTER trigger is not fired until the interactive statement is about
to complete --- ie, after the SECURITY DEFINER function has returned.
There has been previous discussion (inconclusive) about changing the
time of invocation of AFTER triggers, but given the current timing this
is the behavior I'd expect.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Before/After Trigger User Switching
@ 2003-08-20 20:33 Aasmund Midttun Godal <postgresql@aasmund.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Aasmund Midttun Godal @ 2003-08-20 20:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Aasmund Midttun Godal <postgresql@aasmund.com>; pgsql-sql
Thank you for your quick reply!
I understand your point of view, however the fact remains that you want the
action to be done as though it was the DEFINER user that did it, and that
has not changed even thoug the function itself has finished?
regards,
aasmund.
Tom Lane writes:
> "Aasmund Midttun Godal" <postgresql@aasmund.com> 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?
>
> An AFTER trigger is not fired until the interactive statement is about
> to complete --- ie, after the SECURITY DEFINER function has returned.
> There has been previous discussion (inconclusive) about changing the
> time of invocation of AFTER triggers, but given the current timing this
> is the behavior I'd expect.
>
> regards, tom lane
Aasmund Midttun Godal
aasmund@godal.com - http://godal.com
+47 40 45 20 46 - Leiv Tronstadsvei 2, NO-7051 Trondheim
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Before/After Trigger User Switching
@ 2003-08-20 20:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aasmund Midttun Godal <postgresql@aasmund.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-08-20 20:53 UTC (permalink / raw)
To: Aasmund Midttun Godal <postgresql@aasmund.com>; +Cc: pgsql-sql
"Aasmund Midttun Godal" <postgresql@aasmund.com> writes:
> Thank you for your quick reply!
> I understand your point of view, however the fact remains that you want the
> action to be done as though it was the DEFINER user that did it, and that
> has not changed even thoug the function itself has finished?
<shrug> ... if there's any bug here, I'd argue that it's that we don't
force trigger functions to run as the owner of the table they're on.
The privileges of the user that did the INSERT or whatever are the wrong
thing in any case, I'd say.
Which suggests a workaround for the moment: your trigger function should
be a SECURITY DEFINER.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-08-20 20:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-20 17:12 Before/After Trigger User Switching Aasmund Midttun Godal <postgresql@aasmund.com>
2003-08-20 19:34 ` Aasmund Midttun Godal <postgresql@aasmund.com>
2003-08-20 20:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-20 20:33 ` Aasmund Midttun Godal <postgresql@aasmund.com>
2003-08-20 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox