Isaac Morland <isaac.morland@gmail.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).
