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

On Wed, Aug 10, 2022 at 6:53 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> My code example ended up quite big—so I'll show it to you all only if you
> ask. But it's easy to describe. My script does this:
>

Then maybe you should spend some time making a smaller code example that
still shows the desired behavior but can be easily read and executed by
others. In particular, your description of simply returning NULL for all
triggers seems suspect. If only two of the eight triggers show the problem
then the example only needs two triggers to show the presence of the
unexpected current_role and to get clarity why it is that. All the stuff
that is working as expected is just noise; that is the stuff that can be
summed up with words on a first pass.

>
> I read the section "Triggers on Data Changes" (
> https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER)
> But there's no hit on the page for any of "security", "invoker", or
> "definer". And I couldn't find wording without these terms that addresses
> what I describe here.
>
>
As the behavior you are pointing out has nothing to do with pl/pgsql
specifically, but rather the runtime environment of triggers in the server,
it is not surprising the lack of discussion of this topic in that part of
the documentation.

David J.