Postgres Pro
Downloads
Home   >   mailing lists

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario - Mailing list pgsql-general

From David G. Johnston
Subject Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
Date
Msg-id CAKFQuwZsOpj7eQM=W2FpGo3LeoBaRv7o5GuMBDbTY94t9s2vdA@mail.gmail.com
Whole thread Raw
In response to Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario  (Bryn Llewellyn <bryn@yugabyte.com>)
Responses Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
List pgsql-general
Tree view
On Wed, Aug 10, 2022 at 6:53 PM Bryn Llewellyn <bryn@yugabyte.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.

pgsql-general by date:

Previous
From: Bryn Llewellyn
Date:
Subject: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
Next
From: Bryn Llewellyn
Date:
Subject: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario