Re: Wrong security context for deferred triggers?

On Mon, 6 Nov 2023 at 11:58, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> 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.
>

This looks to me like another reason that triggers should run as the
trigger owner. Which role owns the trigger won’t change as a result of
constraints being deferred or not, or any role setting done during the
transaction, including that relating to security definer functions.

Right now triggers can’t do anything that those who can
INSERT/UPDATE/DELETE (i.e., cause the trigger to fire) can’t do, which in
particular breaks the almost canonical example of using triggers to log
changes — I can’t do it without also allowing users to make spurious log
entries.

Also if I cause a trigger to fire, I’ve just given the trigger owner the
opportunity to run arbitrary code as me.

> 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.
>

This problem is also fixed by running triggers as their owners: there
should be a dependency between an object and its owner. So the
trigger-executing role can’t be dropped without dropping the trigger.