On Tue, Jul 14, 2026 at 3:52 PM Nico Williams <[email protected]> wrote: > It lets them run their code as you because trigger procedures run as the > current_user, right? Maybe there should be some cases where SECURITY > DEFINER is implied, or where not having it is an error. E.g., if I > create a tigger or foreign key on a table I don't own but do have the > corresponding grant, then maybe my triggers' trigger procedures should > have to be SECURITY DEFINER. The problem is that cascading this > property may not be possible, and anyways it's silly for all the reasons > you give, so a documentation fix seems like the best/only thing to do.
Unfortunately, in this case and in many others, forcing SECURITY DEFINER just moves the problem around. Suppose Owen has a table and grants TRIGGER permissions to Terri. Terri runs CREATE TRIGGER, using as the trigger function one owned by Frank. Finally, Iris inserts into a row into Owen's table, firing the trigger created by Terri, and thus executing the function owned by Frank. If the function is SECURITY INVOKER, it runs as Iris, else it runs as Frank. Owen is still recorded as the owner of the table, but Owen's privileges aren't used anywhere. Terri's involvement isn't even recorded: the trigger has no owner separate from the table. So making the function SECURITY DEFINER just changes the current role from one role that isn't Terri to another role that also isn't Terri, which doesn't seem to accomplish anything useful from a security perspective. But even if we could make the current role Terri -- say if Terri points the trigger at Terri's own function rather than at Frank's function, and we go ahead and force SECURITY DEFINER -- so what? We don't know that Terri is malicious, and it might well be that the newly-created trigger won't even work properly if run with Terri's permissions. But it's worse than that: what if the bad actor is actually Iris? In that case, Iris fires a trigger that runs as Iris and can only do things that Iris can already do. If we forced the trigger to run as Terri, it must now secure search_path and otherwise take great care not to allow the caller to steal Terri's permissions. If Owen and Terri are the good guys, this kind of change represents a substantial security *regression*. I do have some ideas about how to provide better protection against this sort of thing -- see the thread on "sandboxing untrusted code" -- but I don't have it all figured out yet, and I don't want to divert this thread into trying to hash that out. For now, I am content to commit this patch, which I have now done. I chose to back-patch, which is arguable, as it could be argued that the lack of this information does not constitute a factual error, and documentation improvements are typically committed only to the master branch. However, in view of the fact that the security team has received a number of reports related to this behavior, I felt a back-patch was warranted. If anyone strongly disagrees, please speak up, because there are other, similar cases which I also want to tackle, and it will be useful to know whether back-patching is welcome or unwelcome in these types of situations. -- Robert Haas EDB: http://www.enterprisedb.com
