Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Bryn Llewellyn
Date:
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:

1. It creates three non-superuser roles: "data", "code", and "client".

2. It creates a text-book masters-and-details table pair with owner "data".

Each table has the obvious PK column. The "details" table has the obvious FK column. And each table has a payload column "v". The FK constraint is defined thus:

  constraint details_fk foreign key(mk)
    references masters(mk)
    match full
    on delete cascade
    on update restrict
    initially deferred

3. It creates a DELETE trigger at each timing point on each table.

That's eight in all: [before|after] * [row|statement] * [masters|details].

The trigger functions have the same names as the triggers. And each name pair reflects the three degrees of freedom—for example "before_statement_masters".

Each trigger function simply does a "raise info" to report its name, the return value from "current_role",  (and, in the "row" case, the value of "old.v"). And then it does "return null".

The trigger functions are owned by "data" and are explicitly marked "security invoker". (The results are the same without this marking—as expected.)

(In my real use case, the trigger functions are marked "security definer". But I did this test in order to understand the rules.)

"data" grants appropriate privileges to "code" to let its functions succeed.

4. It creates three "security definer" procedures with owner "code"

"cr_m_and_ds()" inserts specified "details" rows and then their specified "masters" row. (That's why the FK constraint is "initially deferred". The use-case from which this test is derived needs this.)

"del_m()" deletes a specified "masters" row—which implies the cascade-delete of its details.

"del_ds()" deletes all the "details" rows for a specified master.

"code" grants "execute" on these to "client". ("client " owns no objects.)

5. "client" invokes "code.cr_m_and_ds()".

It's called to insert a single "masters" row with "masters.v" set to 'Mary' and a single "details" row with "details.v" set to 'shampoo'.

6. "client" invokes "code.del_m()" on 'Mary'.

Here's what I see:

before_statement_masters: [code]
before_row_masters: [code] Mary
before_statement_details: [data]
before_row_details: [data] shampoo
after_row_masters: [code] Mary
after_statement_masters: [code]
after_row_details: [code] shampoo
after_statement_details: [code]

(I stripped the noisy "psql:0.sql:32: INFO:" preamble for each output line by hand.)

I was surprised that the value from "current_role" is *not* the table owner, "data", in all cases. (This is how triggers behave in Oracle database.) Rather, it's mainly (but not always) "code". I could be persuaded that, in the cascade-delete case, the invoking role is the owner of the "masters" table rather than the role, "code" that performs the "delete" from "masters"—but that would maybe be a stretch. Anyway, if this is the intention, why is it like this only for the "before" timing points for the triggers on "details"?

7. Starting with the same 'Mary'-'shampoo' pair, client invokes "code.del_ds()" on 'Mary'

With the same set-up, and using this instead of "del_m()", this is the result:

before_statement_details: [code]
before_row_details: [code] shampoo
after_row_details: [code] shampoo
after_statement_details: [code]

Here the value for "current_role" from each trigger is the same. These results are in line with the common case in the first test.

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.

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

From
"David G. Johnston"
Date:
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.

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

From
Bryn Llewellyn
Date:
david.g.johnston@gmail.com wrote:

…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.
 
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.

You said "simply returning NULL for all triggers seems suspect.". Yes, it would be! I took my approach from "The usual idiom in DELETE triggers is to return OLD." in the "Overview of Trigger Behavior" section. And, indeed, I do say "return old" in the code of each of my eight triggers. I'm embarrassed to say that I simply did a typo in my email account.

I'll be happy to make a smaller example. It will, however, need to create users, a database, schemas, and some number of triggers. Because the triggers, their functions, and everything else about them follow a pattern, I can use "format()" and dynamic SQL to generate them. I'll still need those three "security definer" procedures to make the table changes that I explained. And the code to call these procedures to implement the test. So the result won't be exactly small. But, while I'm generating the triggers, I may just as well generate all eight. After all, how would I know which of the eight to skip while I don't know the intended rules for the current_role?

Am I missing something about how this list is meant to work? Email attachments don't make it to the archive for posts to this list (https://www.postgresql.org/list/pgsql-general/). Is there a reliable place where I can post a code .zip so that readers of the list can download it? I asked ages ago about a GitHub-style scheme for filing and tracking PG issues. but I was told that this email list, and its cousins, is the system that you (all) prefer.

Meanwhile, I'd hoped that what I said would prompt a simple statement of what rules are intended. You implied that it's elsewhere in the doc than what I thought would be the place to look. A cross-reference to that section, from the "Triggers on Data Changes" section (and other sections like "CREATE TRIGGER") would help.

Could you please point me to where the statement of rules that I'm seeking is made? Or, failing that, simply tell me what the intended rules are?

Should I read your "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." to mean that the intended role is that the current_role in a "security invoker" trigger function is the role that does the trigger-firing DML on the table?

B.t.w., here's what I had tried before sending the email that started this thread. (I didn't want to bore you all with this long story.)

I looked in the "CREATE TRIGGER" section. I found this:

« To create or replace a trigger on a table, the user must have the TRIGGER privilege on the table. The user must also have EXECUTE privilege on the trigger function. »

I suppose that this covers the case where a pre-existing trigger function has a different owner than will the to-be-created trigger that wants to use it.

This rule is certainly in the general space where the answer to my question might be found. But this doc extract doesn't answer it explicitly. However, it does seem to imply that the invoking role for a trigger function will be the owner of the trigger that uses it. And in my example, this was the table owner. (In other words, the Oracle Database rule.) But this is not what I see in six out of the eight of my tests.

There are no hits on the "CREATE TRIGGER" page for "current_role" or "current_user" (with or without the underscore). Neither are there any hits if I say "effective user" or "effective role". I don't know what else to try.

I looked in  the "CREATE FUNCTION" section. It does, of course, explain the "security invoker" and "security definer" notions. But doesn't say anything there to inform my question. (There are only two hits for the word "trigger" on the page. And neither is relevant for my question.)

I tried "Overview of Trigger Behavior" (https://www.postgresql.org/docs/current/trigger-definition.html). But again found nothing.

Of course, I tried Google, and the PG doc's own search, for all the wordings that I could dream up along the lines of « PostgreSQL current role when security invoker trigger function fires ». But I got nothing helpful.

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

From
Karsten Hilbert
Date:
I'll be happy to make a smaller example. It will, however, need to create users, a database, schemas, and some number
oftriggers. Because the triggers, their functions, and everything else about them follow a pattern, I can use
"format()"and dynamic SQL to generate them. I'll still need those three "security definer" procedures to make the table
changesthat I explained. And the code to call these procedures to implement the test. So the result won't be exactly
small.But, while I'm generating the triggers, I may just as well generate all eight. After all, how would I know which
ofthe 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.
= Karsten
 

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

From
Bryn Llewellyn
Date:
Karsten.Hilbert@gmx.net 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? I appreciate that one is doomed who tries to deduce the rules that govern a software system's behavior by using just empirical testing. (And reading source code hoping to deduce the behavior that the programmer intended is hardly better.)

I used the subject "surprising results" to mean "Results that surprise me, Bryn". The results might well not surprise somebody who knows the rules. Several cases that I've asked about before on this list were surprising for me because I was too dim-witted to find where, in the PG docs, the rules were stated. And in those cases, I was delighted to be pointed to the appropriate doc and to receive some helpful instruction. That's what I'm hoping for here.

Notice that I didn't consider "for insert" or "for update" triggers. But you can contrive a cascade effect with these, too. For example, table "t1" might have a trigger that inserts or updates a row in table "t2" for a purpose like maintaining a change history. And "t2" might, in turn, have a trigger for who-knows-what purpose (maybe to enforce a write-once-read-many regime for the values in certain columns).

This is why I'd very much like to start by studying a clear statement of the intention in scenarios in the same general class as the one that I showed.

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

From
"David G. Johnston"
Date:
On Thu, Aug 11, 2022 at 9:28 AM Bryn Llewellyn <bryn@yugabyte.com> wrote:
Karsten.Hilbert@gmx.net 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.

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

From
Bryn Llewellyn
Date:
david.g.johnston@gmail.com wrote:

You are correct that the behavior here is not explicitly documented [where] I would expect to find it.

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 [all this] 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.

Good. We're converging. Thanks, David. I think that this  is a fair summary:

1. The PG doc very likely has no clear statement, anywhere, of the rules that govern the behavior in the class of trigger scenarios under discussion.

2. An expectation of what the rules are has emerged:

> the invoking role for a trigger's function is the role[*] that owns the trigger.

Thereafter, the privilege domain in which the function executes is governed by the ordinary, separable, rules about "security definer" versus "security invoker".

I mentioned this from the "CREATE TRIGGER" section:

> To create or replace a trigger on a table, the user must have the TRIGGER privilege on the table. The user must also have EXECUTE privilege on the trigger function.

It gives a strong hint that David's expectations are sound.

3. The outcomes with the eight triggers that I tested show straight buggy behavior in six cases. Moreover, because David said "don't expect cascade… to [matter]", the outcomes in the other two cases might show correct behavior only by accident.

It seems to me, therefore, that a carefully constructed, "single click", reproducible testcase is needed. I have this on my laptop. But, of course, I need to refine it a bit and review it thoroughly. It ended up in several .sql scripts called by a master script. This naturally implies a .zip file as the delivery vehicle.

Nobody has told me how an outsider like me can deliver such a .zip file, together with its typographically nuanced external documentation, to readers of plsql-general. So this is what I'll do:

I'll create a placeholder GitHub issue in "yugabyte/yugabyte-db" and send you the URL. Anybody can access this repo, read the account of the issues, and download an attached .zip of a testcase. I'll mention in my account that the behavior that I observe in YugabyteDB reproduces exactly in PG 14.4, that the YugabyteDB issue is filed for tracking purposes, and that I'll update the account with more information from the PG folks in due course.

Please bear with me. It might be a few days before I'm able to send you all the promised URL.
____________________

[*] The PG doc favors using "role" in sentences like this rather than "user"—even though a role might have child rows in an indefinitely deep hierarchy. I trust that this point is fully separable from what matters in the present triggers scenario.

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

From
Julien Rouhaud
Date:
On Thu, Aug 11, 2022 at 04:20:20PM -0700, Bryn Llewellyn wrote:
>
> Nobody has told me how an outsider like me can deliver such a .zip file,
> together with its typographically nuanced external documentation, to readers
> of plsql-general. So this is what I'll do:

You mentioned previously that "Email attachments don't make it to the archive
for posts to this list", but they should.  It seems that you're using apple
mail, which is famous for having such problems, see [1] for instance.

Using a different MUA, or configuring apple mail to correctly put attachment as
attachment will solve this problem.

[1]: https://www.postgresql.org/message-id/CABUevEwEw35g7n3peoqmpWraQuRvounck7puDUWU-S-%3DyfsoEA%40mail.gmail.com

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

From
Bryn Llewellyn
Date:
> rjuju123@gmail.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>> Nobody has told me how an outsider like me can deliver such a .zip file, together with its typographically nuanced
externaldocumentation… 
>
> You mentioned previously that "Email attachments don't make it to the archive for posts to this list", but they
should.It seems that you're using apple mail, which is famous for having such problems, see [1] for instance. 
>
> Using a different MUA, or configuring apple mail to correctly put attachment as attachment will solve this problem.
>
> [1]: https://www.postgresql.org/message-id/CABUevEwEw35g7n3peoqmpWraQuRvounck7puDUWU-S-%3DyfsoEA%40mail.gmail.com


Hmm… I use a modern MacBook with the always current macOS Big Sur. (One day I'll pluck up courage and get to Monterey.)
Iuse the native "Mail.app" email client (a.k.a. Mail User Agent) at whatever version comes with the native macOS
upgradeprocess. I use this to send attachments all the time to no end of friends and colleagues—without issue. So how
canthere be anything wrong with how my "Mail.app" is configured? Is the configuration a pairwise notion so that I need
dedicatedsettings to send to the pgsql-general list? 

Yugabyte uses an email service from Google. I suppose that I could use their ordinary browser-based interface to send
emailsto the pgsql-general list. It would certainly be good to fix this for future exchanges. But for now, I'll stick
tomy plan. This will make it easy for me to draw this issue to the attention of colleagues and to give me a place where
Iadd updates about progress on the issue. 

Moreover, GitHub allows Markdown formatting. And the ability to format even a shortish essay with ordinary modern
deviceslike heading levels, bullet lists, italics, and especially code blocks makes an enormous difference to
readability.

The conventions that this list's archive imposes (only plain text, quoted content indicated with successively deep
chevron-stylemarks, explicit URLs twice as long as your arm, and baked-in hard line breaks at about a dozen words)
makescomprehension quite hard—and structuring an account well-nigh impossible.

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

From
"David G. Johnston"
Date:
On Thu, Aug 11, 2022 at 8:41 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
The conventions that this list's archive imposes (only plain text, quoted content indicated with successively deep chevron-style marks, explicit URLs twice as long as your arm, and baked-in hard line breaks at about a dozen words) makes comprehension quite hard—and structuring an account well-nigh impossible.

It encourages brevity so we consider it a feature ;)  Usually, but not here, the complaint is that it seems to do that too well...

David J.
p.s. just for testing I've included the original email as both zip and text here.

Attachment

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

From
Bryn Llewellyn
Date:
david.g.johnston@gmail.com wrote:


Good. I can see the attachments here too:


And I can download via the links with no problem. I'll aim to work out what's wrong my end as soon as I can.
Attachment

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

From
Bryn Llewellyn
Date:
bryn@yugabyte.com wrote:

david.g.johnston@gmail.com wrote:

You are correct that the behavior here is not explicitly documented [where] I would expect to find it.

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 [all this] not because I can point to a place where all that is said.

Good. We're converging. Thanks, David… So this is what I'll do:

I'll create a placeholder GitHub issue in "yugabyte/yugabyte-db" and send you the URL. Anybody can access this repo, read the account of the issues, and download an attached .zip of a testcase. I'll mention in my account that the behavior that I observe in YugabyteDB reproduces exactly in PG 14.4, that the YugabyteDB issue is filed for tracking purposes, and that I'll update the account with more information from the PG folks in due course.

I've submitted the promised GitHub issue. It has an attached .zip of a self-contained, one-touch testcase. Here it is:


Everything points to a bug in that, no matter what the intended behavior is, the same rules should apply in all scenarios—but they don't. Further, I agree with David's:

I don't expect cascade update/cascade delete to be a factor here at all.

My testcase tries a gazillion variations (including what happens with a function-based constraint and when DML to one table "cascades" to another because of trigger action). After all, in the presence of one bug where something unexpected happens in one scenario, who knows what unexpected outcomes might be seen in other comparable scenarios?

Here's my summary of my findings, copied from the issues report:

«
It very much does seems that the intended rule is this:

- The value that current_role will report in a security invoker trigger function for a DML trigger is the role that does the DML to the table that fires the trigger.

It seems, too, that this testcase has revealed a long-standing bug—present at least as far back as PostgreSQL Version 11:

- The value that current_role will report in a security invoker trigger function for a DML trigger on a "details" table will be the owner of that table when its rows are cascade-deleted as a consequence of deleting its parent "masters" row. However, this buggy outcome is seen only for "before delete" triggers, both at “statement" level and at "row" level.

- The bug has an obvious downstream consequence: any operation on other tables that are done by such a trigger function that sees the wrong current_role will be executed by that wrong role—and so on down the line.
»

W.r.t. David's 

My expectation is that the trigger owner is the context in which the trigger function is executed.

This can't be right because a trigger doesn't have an owner. You can see this from the "pg_trigger" table. It has its own "oid" and a "tgrelid". But it has no FK to "pg_roles" or to "pg_namespace”. In other words, a trigger isn't a first-class schema object. Rather, from the user's P.o.V., it’s uniquely identified by the table to which it's attached. In this sense, it's like a constraint (and especially like one that's based on a function). Each of these two, trigger and function-based-constraint, is a device that associates a "call-back" function with a table so that the function is invoked in response to DML to the table.

The "pg_constraint" table, too, has no FK to "pg_roles". Mysteriously, though, it does have a "connamespace" column. Presumably this is a denormalization such that its value is always equal to "relnamespace" in "pg_class" for the table to which the constraint is attached..

It seems to me, therefore, that the role that creates the trigger is out of the picture once the trigger has been created. (There's no analogy here for a constraint because there's no "grant alter" on a table to correspond to "grant trigger" on a table.)

It seems, too, that the owner of the trigger function (and of the constraint function) is out of the picture at run-time (when these are "security invoker") for determining the value that "current_role" in such a function will report.

In other words, and as I see it, there are only two candidate answers: the role that does the DML that causes the function to be invoked; and the role that owns the table—DML to which causes the function to be invoked.

In most cases, current_role here shows who does the DML. But in those two rare corner cases that my testcase identified ("before statement" and "before row" for delete that's a consequence of cascade delete from a master) current_role shows the details table owner. (Then there's the knock-on. But this seems to be ordinarily expected and not, therefore, to require any special discussion.)

What do you all think?

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

From
"David G. Johnston"
Date:
On Tue, Aug 23, 2022 at 5:12 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
What do you all think?


That bug reports should be sent to the -bugs mailing list with a succinct test case demonstrating the bug.

David J.

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

From
Bryn Llewellyn
Date:
david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

What do you all think?

That bug reports should be sent to the -bugs mailing list with a succinct test case demonstrating the bug.

I explained that the bug doesn't allow a short testcase because there are many degrees of freedom and you don't know, before trying them all, what pattern will emerge.

It would be different if I could read a clear statement of expected behavior. But you've already said that there is none.

The account of my GitHub issue includes a preliminary test that shows that there's something to investigate further. I copied it below. Please tell me if it meets your succinctness criteria. If it does, then I'll submit it to the psql-bugs list as you suggest.

Notice, though, that, counting from « the testcase proper », it's ~70 lines long.

Moreover, it uses five different non-super users, all of whom can use and create in a schema whose name doesn't matter but that must be first in each user's search_path. That prelude (« drop and re-create the "bryn" database and the five users that the testcase needs ») is ~45 lines long.

It produces this output:

current_role, table, operation, v: client, masters, INSERT, Mary
current_role, table, operation, v: client, details, INSERT, shampoo
current_role, table, operation, v: client, details, INSERT, soap
current_role, table, operation, v: client, details, DELETE, soap
current_role, table, operation, v: client, masters, DELETE, Mary
current_role, table, operation, v: d_owner, details, DELETE, shampoo

I've attached it as "t.zip". It unzips to the single file "t.sql". This can be run and re-run time and again. I've proved to myself (again) that I can send from my "bryn@yugabyte.com" to other users with various email domains (like "icloud.com" and "gmail.com"). But I've done nothing to try to solve why my attachments don't make it to "pgsql-general@lists.postgresql.org". Please tell me if you get it at your "gmail.com" address.


--------------------------------------------------------------------------------

-- DROP AND RE-CREATE THE "bryn" DATABASE AND THE FIVE USERS THAT THE TESTCASE NEEDS.

\c postgres postgres
set client_min_messages = warning;
drop database if exists bryn;
create database bryn owner postgres;

\c bryn postgres
set client_min_messages = warning;
revoke all on database bryn from public;
drop schema public cascade;
create schema s authorization postgres;

create procedure s.create_role(name in text, can_create in boolean = false)
  security invoker
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  begin
    execute format('drop owned by %I cascade', name);
  exception
    when undefined_object then null;
  end;
  execute format('drop role if exists %I', name);
  execute format('create role %I login password ''p''', name);
  execute format('grant connect on database bryn to %I', name);
  execute format('grant usage on schema s to %I', name);
  execute format('alter user %I set search_path = s, pg_catalog, pg_temp', name);

  if can_create then
    execute format('grant create on database bryn to %I', name);
    execute format('grant create on schema s to %I', name);
  end if;
end;
$body$;

call s.create_role('m_owner',        true);
call s.create_role('d_owner',        true);
call s.create_role('trg_functions',  true);
call s.create_role('trg_creator',    true);
call s.create_role('client'              );
--------------------------------------------------------------------------------
-- THE TESTCASE PROPER.

\c bryn m_owner
create table masters(
  mk  serial primary key,
  v   text not null unique);
grant all on table     masters         to public;
grant all on sequence  masters_mk_seq  to public;

\c bryn d_owner
create table details(
  mk  int,
  dk  serial,
  v   text not null unique,

  constraint details_pk primary key(mk, dk),

  constraint details_fk foreign key(mk)
    references masters(mk)
    on delete cascade);
grant all on table     details         to public;
grant all on sequence  details_dk_seq  to public;

\c bryn trg_functions
create function trg_fn()
  returns trigger
  security invoker
  set search_path = s, pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  vv constant text not null :=
    case tg_op
      when 'INSERT' then new.v
      when 'DELETE' then old.v
    end;
begin
  raise info 'current_role, table, operation, v: %, %, %, %', current_role, tg_table_name, tg_op, vv;
  return case tg_op
    when 'INSERT' then new
    when 'DELETE' then old
  end;
end;
$body$;
grant all on function trg_fn() to public;

\c bryn trg_creator
create trigger masters_trg
  before insert or delete
  on masters
  for each row
  execute function trg_fn();

create trigger details_trg
  before insert or delete
  on details
  for each row
  execute function trg_fn();

\c bryn client
do $body$
declare
  new_mk int not null := 0;
begin
  insert into masters(v) values('Mary') returning mk into new_mk;
  insert into details(mk, v) values(new_mk, 'shampoo');
  insert into details(mk, v) values(new_mk, 'soap');
end;
$body$;

delete from details where v = 'soap';

delete from masters where v = 'Mary';

Attachment

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

From
"David G. Johnston"
Date:
On Tue, Aug 23, 2022 at 7:32 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

The account of my GitHub issue includes a preliminary test that shows that there's something to investigate further. I copied it below. Please tell me if it meets your succinctness criteria. If it does, then I'll submit it to the psql-bugs list as you suggest.


Fair point, go ahead and just post to -bugs because I'm not going to be diving that deeply into this right now.

David J.