Re: Wrong security context for deferred triggers?

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