On Thu, Aug 11, 2022 at 9:28 AM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:
Karsten.Hilbert@xxxxxxx wrote:bryn@xxxxxxxxxxxx wrote:I'll be happy to make a smaller example. It will, however, need to create… After all, how would I know which of the eight to skip while I don't know the intended rules for the current_role?
You'd certainly start out with all eight but then whittle down to what still exhibits the problem and post that.
Do you know where I can read a statement of the intended rules here?
You are correct that the behavior here is not explicitly documented (at least not in the two sections I would expect to find it in - the SQL Reference and the general Triggers section).
My expectation is that the trigger owner is the context in which the trigger function is executed. Table owners can grant the ability to other roles to create triggers on the owned tables should the trigger owner and table owner require different roles. Triggers are like views in this regard.
I don't expect cascade update/cascade delete to be a factor here at all, other than making the process easier to perform. This extends from the prior expectation.
I expect a security definer function's contents to be executed as the role of the owner of that function. Because that is what that clause means and there isn't an obvious reason to ignore it here.
I expect those because that is how the system usually works, not because I can point to a place where all that is said. I would for sure expect deviations to be mentioned, and would find explicit documentation to be reasonable to add if someone pushes forward such a change.
David J.
