Trigger execution role
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.
It seems to me that for a lot of the suggested uses of triggers this is not
the desired behaviour. For example, in the canonical "logging table"
example, we want to be able to allow users to make changes to the base
table, but we don't want them to be able to insert to the log table except
indirectly by causing the trigger to fire. Even in cases where a trigger is
just checking whether the update is permissible, it seems to me that it
might be useful to refer to information not accessible to the user doing
the changes.
Has there been any discussion of this before? I know Postgres has had
triggers for a long time but at the same time I don't see how anybody could
do a lot of work with triggers without finding this to be a problem at some
point. I haven't found any discussion of this in the documentation.
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.
regards, tom lane
On Fri, 12 Feb 2021 at 12:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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 userwho
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).
On 2/16/21 3:59 PM, Isaac Morland wrote:
On Fri, 12 Feb 2021 at 12:58, Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:Isaac Morland <isaac.morland@gmail.com
<mailto: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 contentsIn 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