Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

On Thu, Aug 11, 2022 at 9:28 AM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

>
> *Karsten(dot)Hilbert(at)gmx(dot)net <Karsten(dot)Hilbert(at)gmx(dot)net> wrote:*
>
> *bryn(at)yugabyte(dot)com <bryn(at)yugabyte(dot)com> 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.