Re: Fwd: Non-cancellable queries

On Wed, 20 May 2026 at 12:55, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:

> > You can run as many queries as you want; the queries sitting server-side,
> > consuming CPU
>

Yyou found the trick where the join list building routine consumes a lot of
> resources.

Postgres code is aware of this situation and has a clear trade-off between
> performance and signal checking. Even if we fix this specific case, there
> are
> multiple cycles in the code that might be expanded by a tricky query. So,
> can
> you provide more context to define the problem's importance and scope so
> we can
> determine whether this subject is actually important and should be fixed?
>

This problem can surface in the following situations:

a) "growing analytics" -- imagine tables sharded by very thin key (f.e.,
separate daily tables), and one
would want to run query against large date range;
b) blind SQL injection with ultra restricted user permissions (query only)
became DoS instrument;
c) "shared hosting" of some sort -- when the single server has ability to
run queries from different users;
in this situation it would be just lack of isolation. not a problem for
the postgresql itself, it is more of a
problem for services who build service on top of postgres;

There is another similar case with one more exploitation path:

python -c "print('create table if not exists x(x
int);');n=2000000;print('select 1 from x where 1=1',end=' and x=1'*n)" |
psql -h 172.17.0.2 -Upostgres -A

the query is perfectly valid repetition of "x=1 and x=1 and ..." that also
leads to non-cancellable memory-eating query.

Can be a consequence of perfectly valid use-case:
- simple table with category as a value
- site has multiple-choice category selector, that get POSTed as a form
in some array sorts (cat[]=N&cat[]=N or just cat=N&cat=N etc)
- the ORM convert the array into query using " ".join("AND cat=%d" %
(cat,) for cat in form['cat'])

The query is safe, the values are safe, no arbitrary injection, and yet,
server DOS.