Create a table and a deferrable constraint trigger:
CREATE TABLE tab (i integer);
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();
Create a role and allow it INSERT on the table:
CREATE ROLE duff;
GRANT INSERT ON tab TO duff;
Now become that role and try some inserts:
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.
Yours,
Laurenz Albe
