Buggy results from current_role in a "security invoker" trigger function in a multi-owner, "cascade delete via FK" scenario

The background for this post is a "pgsql-general" thread that I started here:

www.postgresql.org/message-id/6EA46B7D-7775-4524-96EC-E32599AA46EC@yugabyte.com

As of this email's timestamp, the latest turn is here:

www.postgresql.org/message-id/CAKFQuwYb_Z3B%3D127KOXrn%2BKmHHxTUUbHiM6OGqmKSgpb2_%2Bzug%40mail.gmail.com

This is the upshot of that thread:

- The rules that would allow the user to predict what "current_role" returns in a "security invoker" trigger function, in a multi-owner scenario like my testcase (below) demonstrates, are not specified in the current version of the PostgreSQL documentation.

- The emergent opinion is that the rules are simply stated thus: the answer will, *in all circumstances*, be the role that performs the table DML that causes the trigger to fire.

My testcase shows that this is not always the case—in other words that there seems to be a bug. I conducted extensive testing (using PG Version 14.4). It explores very many degrees of freedom. Because the presumed bug also affects YugabyteDB (which uses the PG SQL processing C code, as of Version 11.2, "as is"), I submitted a placeholder issue in the YugabyteDB GitHub repo here:

github.com/yugabyte/yugabyte-db/issues/13736

This has a .zip attachment that contains the scripts that implement the testcase with a one-touch master script. The repo is open for all to read my account and to download my zip.

The experiment can be run time and again and always produces the same spooled output file. The testcase that I'm copying below is the minimum form of my testcase that demonstrates that something needs investigation—and that there does seem to be a bug. Save it to a single file and run it at the psql prompt. It, too, can be run and re-run time and again. Here is what "raise info" says (after stripping the noisy preamble that starts each line):

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

The bug is that in just one case, "current_role" shows "d_owner" (the role that owns the "details" table) rather than "client" (the role that does the DML).

You can see from my code that the buggy outcome occurs under these circumstances:

- "client" deletes a row from the "masters" table and this causes cascade-delete of its child rows in the "details" table.

- the triggers that show the buggy outcome are "before delete", on just the "details" table, both at "statement" level and at "row" level.

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

-- 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';
--------------------------------------------------------------------------------

This script is attached as "t.zip". But experience has shown that attachments from my email address, uniquely when sent to a "pgsql-*" list, don't get through —at least not into the archive.