Re: [PATCH] Fix infinite recursion when foreign table references itself

On 5/12/26 16:06, John Mikk wrote:
> Hi, hackers.
>
> If you create a foreign table referencing itself on a loopback server, an unpleasant error occurs:
>
> ```sql
> create extension if not exists postgres_fdw;
>
> drop server if exists loopback cascade ;
> create server loopback
> foreign data wrapper postgres_fdw
> options (dbname 'postgres', host 'localhost');
>
> create user mapping for current_user server loopback;
>
> create foreign table test_self (id int)
> server loopback options (table_name 'test_self');
> --> Ok
>
> insert into test_self select 1;
> --> Err
> /*
> [08001] ERROR: could not connect to server "loopback"
> Detail: connection to server on socket "/tmp/.s.PGSQL.54321" failed:
> FATAL: sorry, too many clients already
> Where: remote SQL command: INSERT INTO public.test_self(id) VALUES ($1)
> remote SQL command: INSERT INTO public.test_self(id) VALUES ($1)
> remote SQL command: INSERT INTO public.test_self(id) VALUES ( ...
> */
> ```
>
> The proposed patch fixes this error.
>
> ```sql
> create foreign table test_self (id int)
> server loopback options (table_name 'test_self');
> --> Err
> /*
> [42P16] ERROR: foreign table "test_self" cannot reference itself
> Hint: Foreign table pointing to the same table on the same database
> creates circular reference
> */
> ```
>

I don't think this is a bug. There's probably a million other ways to
cause similar connection loops - consider for example two servers with
foreign tables pointing at each other. That'll trigger exactly the same
issue like your example.

The patch has a variety of other issues :-(

- The options (table_name ...) are specific to the postgres_fdw
extension. The code in src/backend/commands/foreigncmds.c should not be
checking that. There could be an arbitrary other FDW, which happens to
have the same option, or whatever.

- It checks the dbname and table_name, but there's no guarantee it
points at the same machine/instance. So it actually prevents references
to the same (dbname,table_name) on any server. I doubt that makes sense.

- It can't actually check the server in a reliable way, because it could
go through an arbitrary IP for the machine, hostname, connection pool or
whatever other proxy.

- It handles CREATE FOREIGN TABLE, but there's all kinds of ways to
adjust options for an existing table, e.g.

ALTER FOREIGN TABLE test_self OPTIONS (SET table_name '...')

The patch would have add protections in all those places.

There's probably more issues. I don't think this is feasible / worth it.
The connection failure seems like a reasonable end result.

regards

--
Tomas Vondra