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