Re: Trigger execution role

On Fri, 12 Feb 2021 at 12:58, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Isaac Morland <isaac(dot)morland(at)gmail(dot)com> writes:
> > I was trying to use triggers, and ran into something I hadn't realized
> > until now: triggers run, not as the owner of the table, but as the user
> who
> > is doing the insert/update/delete.
>
> If you don't want that, you can make the trigger function SECURITY
> DEFINER. If we forced such behavior, there'd be no way to get the
> other behavior.
>

I did think about SECURITY DEFINER, but that has at least a couple of
significant downsides:

- can't re-use the same generic trigger function for different table
owners; would need to duplicate the function and use the one whose owner
matches the table
- other users could make the function a trigger for their tables and then
invoke it unexpectedly (i.e., in a scenario I didn’t anticipate)
- have to grant EXECUTE on the function to the same users that need
permission to change the table contents

In what scenarios is it needed for the trigger to run as the role doing the
INSERT/UPDATE/DELETE? There are lots of scenarios where it doesn't matter —
I can think of any number of constraint enforcement triggers that just
compute a boolean and which could run as either — but I find it a lot
easier to imagine a scenario where the table owner wants to do something
when an INSERT/UPDATE/DELETE occurs than one in which the table owner wants
to make sure the role changing the table does something.

Additionally, with the present behaviour, what happens when I change a
table's contents is completely unpredictable. A table could have a trigger
on it which drops all my tables, to take an extreme example. If “I” am
postgres then this could be problematic: it’s not safe for a privileged
user to make changes to the contents of another role’s tables unless they
are first verified to have no triggers on them (or, in theory, that the
triggers are harmless, but I’ve been playing enough Baba is You lately to
consider any judgement that the triggers are harmless to be worthless
without a formally verified proof of same).