Message-ID: <ecc1d31ae512cc5795bfa470bf548afe4145639c.camel@cybertec.at>
Subject: Re: BUG #18621: postgres_fdw cannot work with tables that use
 schemaless functions in triggers
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: rekgrpth@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Tue, 17 Sep 2024 11:19:45 +0200
In-Reply-To: <18621-34e3fc574aa9fbc1@postgresql.org>
References: <18621-34e3fc574aa9fbc1@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ecc1d31ae512cc5795bfa470bf548afe4145639c.camel%40cybertec.at>
Precedence: bulk

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 f=
or
> 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 cha=
nge
> the trigger code so that it uses an explicit schema specification!
>=20
> [...]
> 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 $bo=
dy$
> 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 $bod=
y$
> begin
>     if tg_when =3D 'BEFORE' and tg_op in ('INSERT', 'UPDATE') then
>         new.i =3D multiply(new.i);
>         new.t =3D current_user;
>     end if;
>     return case when tg_op =3D '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();
>=20
> [...]
>
> 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;
>=20
> \connect "user=3Dbob dbname=3Dlocal"
> insert into remote select i from generate_series(1, 10) i;
>=20
> ERROR:  function multiply(integer) does not exist
> HINT:  No function matches the given name and argument types. You might n=
eed
> 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-REM=
OTE-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 wit=
hout
   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-qu=
alify
   all names in such functions, or else attach SET search_path options (see
   CREATE FUNCTION) to such functions to establish their expected search pa=
th
   environment."

So I don't see a bug here.

Yours,
Laurenz Albe