Thread: BUG #18621: postgres_fdw cannot work with tables that use schemaless functions in triggers

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)

On Tue, 2024-09-17 at 05:28 +0000, PG Bug reporting form wrote:
> 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!
>
> [...]
> 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 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)

That is documented
(https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-REMOTE-QUERY-EXECUTION-ENVIRONMENT)

  "In the remote sessions opened by postgres_fdw, the search_path parameter
   is set to just pg_catalog, so that only built-in objects are visible without
   schema qualification. This is not an issue for queries generated by
   postgres_fdw itself, because it always supplies such qualification.
   However, this can pose a hazard for functions that are executed on the
   remote server via triggers or rules on remote tables. For example, if a
   remote table is actually a view, any functions used in that view will be
   executed with the restricted search path. It is recommended to schema-qualify
   all names in such functions, or else attach SET search_path options (see
   CREATE FUNCTION) to such functions to establish their expected search path
   environment."

So I don't see a bug here.

Yours,
Laurenz Albe

Yes, but as you can see above, the same insert works when connected
directly and does not work when connected via postgres_fdw.

Moreover, if I set search_path to the default value "$user", public ,
then everything starts working fine.

вт, 17 сент. 2024 г. в 14:19, Laurenz Albe <laurenz.albe@cybertec.at>:
>
> On Tue, 2024-09-17 at 05:28 +0000, PG Bug reporting form wrote:
> > 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!
> >
> > [...]
> > 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 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)
>
> That is documented
> (https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-REMOTE-QUERY-EXECUTION-ENVIRONMENT)
>
>   "In the remote sessions opened by postgres_fdw, the search_path parameter
>    is set to just pg_catalog, so that only built-in objects are visible without
>    schema qualification. This is not an issue for queries generated by
>    postgres_fdw itself, because it always supplies such qualification.
>    However, this can pose a hazard for functions that are executed on the
>    remote server via triggers or rules on remote tables. For example, if a
>    remote table is actually a view, any functions used in that view will be
>    executed with the restricted search path. It is recommended to schema-qualify
>    all names in such functions, or else attach SET search_path options (see
>    CREATE FUNCTION) to such functions to establish their expected search path
>    environment."
>
> So I don't see a bug here.
>
> Yours,
> Laurenz Albe

On Tue, 2024-09-17 at 14:39 +0500, RekGRpth wrote:
> Yes, but as you can see above, the same insert works when connected
> directly and does not work when connected via postgres_fdw.

Yes, that's how it should be.

Yours,
Laurenz Albe

PG Bug reporting form <noreply@postgresql.org> writes:
> 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!

This is a bug in the trigger function, not in postgres_fdw.

            regards, tom lane