On Sat, Jun 8, 2024 at 10:13 PM Isaac Morland <isaac.morl...@gmail.com> wrote:
> 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. Can you expand on this a bit? When a trigger executes should the execution environment match: - The execution environment of the trigger owner at the time of trigger creation? - The execution environment of the function owner at the time of function creation? - An execution environment built from the trigger owner's default configuration parameters? - Something else? While I am convinced that privileges should be checked using the trigger owner's role, I'm less convinced of other configuration parameters. For the search_path example, that can be resolved by either fully qualifying object names or setting the search_path in the function itself. Similar approaches can be taken with other configuration parameters. I also worry that it would be a source of confusion that the execution environment of triggers come from the trigger/function owner, but the execution environment of function calls come from the caller. > 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. This is perhaps a contrived example, but here's one. Suppose I create a trigger that raises a notice that includes the current timestamp. I would probably want to use the timezone of the caller, not the trigger owner. Thanks, Joe Koshakow