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