Re: document the dangers of granting TRIGGER or REFERENCES

On Mon, Jul 20, 2026 at 02:13:04PM -0400, Robert Haas wrote:
> 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
> [...]

Oh, but what I wrote and had in mind is that the TRIGGER _PROCEDURE_
would run as the PROCEDURE, though really it should be the TRIGGER
owner. And any functions called by that would run as the same user if
they themselves were SECURITY INVOKER.

At least this way anything that the TRIGGER does is done with its
owner's permissions. Though still, as noted, you get a stack of
user/role names, starting that of the one that started the top level
command. If you need to do authorization (_you will_) you'll want to
know all of those names.

Nico
--