The following bug has been logged on the website:
Bug reference: 18621
Logged by: RekGRpth
Email address: rekgrpth@gmail.com
PostgreSQL version: 16.4
Operating system: docker alpine
Description:
Let's create a trigger on a remote server that uses different functions for
different users. postgres_fdw cannot perform an insert in this case. And the
remote server may be a production server, where there is no access to change
the trigger code so that it uses an explicit schema specification!
drop database local;
drop database remote;
drop user alice;
drop user bob;
drop user local;
drop user remote;
create user alice superuser;
create user bob superuser;
create user local superuser;
create user remote superuser;
create database local with owner local;
create database remote with owner remote;
\connect "user=remote dbname=remote"
create table remote (i int, t text);
create schema remote;
truncate table remote;
create function multiply(i int) returns int language plpgsql as $body$
begin
return i * 1;
end;$body$;
create schema alice;
create function alice.multiply(i int) returns int language plpgsql as $body$
begin
return i * 2;
end;$body$;
create schema bob;
create function bob.multiply(i int) returns int language plpgsql as $body$
begin
return i * 3;
end;$body$;
create function remote_trigger() returns trigger language plpgsql as $body$
begin
if tg_when = 'BEFORE' and tg_op in ('INSERT', 'UPDATE') then
new.i = multiply(new.i);
new.t = current_user;
end if;
return case when tg_op = 'DELETE' then old else new end;
end;$body$;
create trigger remote_before_trigger before insert or update or delete on
remote for each row execute procedure remote_trigger();
create trigger remote_after_trigger after insert or update or delete on
remote for each row execute procedure remote_trigger();
insert into remote select 1;
\connect "user=bob dbname=remote"
insert into remote select 1;
\connect "user=alice dbname=remote"
insert into remote select 1;
\connect "user=local dbname=local"
create schema fdw;
create extension postgres_fdw schema fdw;
create server remote foreign data wrapper postgres_fdw options (dbname
'remote');
create user mapping for current_user server remote options (user
'remote');
create user mapping for alice server remote options (user 'remote');
create user mapping for bob server remote options (user 'remote');
create foreign table remote (i int, t text) server remote options
(schema_name 'public', table_name 'remote');
select * from remote;
\connect "user=bob dbname=local"
insert into remote select i from generate_series(1, 10) i;
ERROR: function multiply(integer) does not exist
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
CONTEXT: PL/pgSQL function remote.remote_trigger() line 3 at assignment
remote SQL command: INSERT INTO public.remote(i, t) VALUES ($1, $2)
