On Mon, 2023-11-06 at 14:23 +0100, Laurenz Albe wrote: > CREATE FUNCTION trig() RETURNS trigger > LANGUAGE plpgsql AS > $$BEGIN > RAISE NOTICE 'current_user = %', current_user; > RETURN NEW; > END;$$; > > CREATE CONSTRAINT TRIGGER trig AFTER INSERT ON tab > DEFERRABLE INITIALLY IMMEDIATE > FOR EACH ROW EXECUTE FUNCTION trig(); > > SET ROLE duff; > > BEGIN; > > INSERT INTO tab VALUES (1); > NOTICE: current_user = duff > > That looks ok; the current user is "duff". > > SET CONSTRAINTS ALL DEFERRED; > > INSERT INTO tab VALUES (2); > > Become a superuser again and commit: > > RESET ROLE; > > COMMIT; > NOTICE: current_user = postgres > > > So a deferred constraint trigger does not run with the same security context > as an immediate trigger. This is somewhat nasty in combination with > SECURITY DEFINER functions: if that function performs an operation, and that > operation triggers a deferred trigger, that trigger will run in the wrong > security context. > > This behavior looks buggy to me. What do you think? > I cannot imagine that it is a security problem, though.
I just realized one problem with running a deferred constraint trigger as the triggering role: that role might have been dropped by the time the trigger executes. But then we could still error out. Yours, Laurenz Albe
