Re: document the dangers of granting TRIGGER or REFERENCES

On Tue, Jul 14, 2026 at 3:52 PM Nico Williams <nico(at)cryptonector(dot)com> 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