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.
