Cancel query where client connection was terminated

[rdlrt]

Environment

• PostgreSQL version: 16.1
• PostgREST version: 12.0.2
• Operating system: Debian 12

Description of issue

When a client cancels a connection, the underlying query continues to run on PostgREST db pool (atleast until reaching it's timeout). This gives a potential to easily (knowingly/unknowingly) DDoS an instance by exhausting it's pool if a query is identified that may take a while to run.

While this might be an expected behaviour from db pool and solution there would be nice, in absence of such a solution - an ability on PostgREST layer (perhaps could be a time interval to cleanup connections without client) would be useful. This would allow the security rules at proxy layer to step in and properly terminate unexpectedly wrong running queries, while disconnected clients are cleaned up at source itself

To test:

1. Create a simple function below:

   CREATE FUNCTION public.sleeptest() RETURNS VOID AS $$
   BEGIN
   select pg_sleep(300);
   END
   $$ LANGUAGE PLPGSQL;

2. Call http://127.0.0.1:8050/rpc/sleeptest and cancel right after (the server side query would continue to run using one of the DB Pool connection - can be verify via pg_stat_activity)
3. Repeat a few times until pool is exhausted

(PS: Working against db pool max session timings isnt applicable as there could be legit cases where an exposed query could run for 5 minutes, while most should finish in < 5s, the control for this can be done on proxy layer as long as cancelled connection is shutdown on postgres DB)

[wolfgangwalther]

Duplicate of #699

[rdlrt]

@wolfgangwalther - While title sounds similar, isnt the linked issue more about COMMIT of transaction in progress rather than connection itself being open in DB (as part of connection pool)?

[wolfgangwalther]

The linked issue deals with the same problem and has multiple suggestions on how to solve it:

• The first idea was to solve it by rolling back the transaction.
• Later on, cancelling the running query is explicitly discussed.

So, yes, a duplicate.