Re: Fwd: Non-cancellable queries
Re: Fwd: Non-cancellable queries
От:
Andrei Lepikhov <lepihov@gmail.com>
Дата:
On 19/05/2026 22:44, Anton Fedorov wrote: > You can run as many queries as you want; the queries sitting server-side, > consuming CPU: > image.png > > The only way to kill them is to restart the server or wait almost indefinitely. 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? -- regards, Andrei Lepikhov, pgEdge
Re: Fwd: Non-cancellable queries
От:
Anton Fedorov <datacompboy@gmail.com>
Дата:
On Wed, 20 May 2026 at 22:11, Anton Fedorov <datacompboy@gmail.com> wrote:
On Wed, 20 May 2026 at 12:55, Andrei Lepikhov <lepihov@gmail.com> wrote: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?
as extra bit here: MySQL fixed that as CVE-2026-21968
Re: Fwd: Non-cancellable queries
От:
Anton Fedorov <datacompboy@gmail.com>
Дата:
On Wed, 20 May 2026 at 12:55, Andrei Lepikhov <lepihov@gmail.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.
