Re: Trigger execution role

On 2/16/21 3:59 PM, Isaac Morland wrote:
> On Fri, 12 Feb 2021 at 12:58, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Isaac Morland <isaac(dot)morland(at)gmail(dot)com
> <mailto: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.

One fairly obvious example is where the trigger is inserting audit data.
It needs to log the name of the user running the triggering statement
rather than the table owner.

TBH, I've used triggers very extensively over the years for a wide
variety of purposes and not found this to be a great issue.

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

Well, that's true of any function no matter how it's invoked. If the
function is malicious it will do damage. If you suspect the database to
contain malicious trigger code, maybe disabling user triggers is in order.

Anyway, just speculating, but maybe there is a case for allowing running
a trigger as the table owner, as part of the trigger creation. Certainly
we're a very long way past the time when we could reasonably change the
default.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com