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 Bryn Llewellyn
Subject Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
Date
Msg-id 16BB8A31-2E0A-4703-839B-24D0B2B93068@yugabyte.com
Whole thread Raw
In response to Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario  ("David G. Johnston" <david.g.johnston@gmail.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
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

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
Next
From: "David G. Johnston"
Date:
Subject: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario