Re: Wrong security context for deferred triggers?

On Sat, 8 Jun 2024 at 17:37, Joseph Koshakow <koshy44(at)gmail(dot)com> wrote:

> However, I do worry that this is too much of a breaking change and
> fundamentally changes how triggers work. Another draw back is that if
> the trigger owner loses the required privileges, then no one can modify
> to the table.
>

I worry about breaking changes too. The more I think about it, though, the
more I think the existing behaviour doesn’t make sense.

Speaking as a table owner, when I set a trigger on it, I expect that when
the specified actions occur my trigger will fire and will do what I
specify, without regard to the execution environment of the caller
(search_path in particular); and my trigger should be able to do anything
that I can do. For the canonical case of a logging table the trigger has to
be able to do stuff the caller can't do. I don't expect to be able to do
stuff that the caller can do.

Speaking as someone making an update on a table, I don't expect to have it
fail because my execution environment (search_path in particular) is wrong
for the trigger implementation, and I consider it a security violation if
the table owner is able to do stuff as me as a result, especially if I am
an administrator making an update as superuser.

In effect, I want the action which fires the trigger to be like a system
call, and the trigger, plus the underlying action, to be like what the OS
does in response to the system call.

I'm not sure how to evaluate what problems with existing implementations
would be caused by changing what role executes triggers, but I think it's
pretty clear the existing behaviour is the wrong choice in every other way
than backward compatibility. I welcome examples to the contrary, where the
existing behaviour is not just OK but actually wanted.