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

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
Date: 2022-08-24 02:32:25
Message-ID: 16BB8A31-2E0A-4703-839B-24D0B2B93068@yugabyte.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)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(at)yugabyte(dot)com <mailto:bryn(at)yugabyte(dot)com>" to other users with various email domains (like "icloud.com <http://icloud.com/>" and "gmail.com"). But I've done nothing to try to solve why my attachments don't make it to "pgsql-general(at)lists(dot)postgresql(dot)org <mailto:pgsql-general(at)lists(dot)postgresql(dot)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';

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-08-24 02:50:50 Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
Previous Message David G. Johnston 2022-08-24 00:21:29 Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario