MIME-Version: 1.0
References: 
 <CACNOLFFvMX521BZvPFyGwW=WOWk_1u-zqAm+j3BerYA6mx0jCA@mail.gmail.com>
 <CACNOLFGH_GPGXwzN23RXYbj3PaDxYKahHRGXnchCa7B=1TS7+w@mail.gmail.com>
 <3b02a3fe-bf62-4231-8ea8-5021f4846e2d@gmail.com>
In-Reply-To: <3b02a3fe-bf62-4231-8ea8-5021f4846e2d@gmail.com>
From: Anton Fedorov <datacompboy@gmail.com>
Date: Wed, 20 May 2026 22:11:44 +0200
Message-ID: 
 <CACNOLFGeVM+xdjd7ieR-kp+jV7gJOHxZdkqsaxOojRqiKakqKA@mail.gmail.com>
Subject: Re: Fwd: Non-cancellable queries
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000038b4f80652456895"
Archived-At: 
 <https://www.postgresql.org/message-id/CACNOLFGeVM%2Bxdjd7ieR-kp%2BjV7gJOHxZdkqsaxOojRqiKakqKA%40mail.gmail.com>
Precedence: bulk

--00000000000038b4f80652456895
Content-Type: text/plain; charset="UTF-8"

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.

--00000000000038b4f80652456895
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, 20 May =
2026 at 12:55, Andrei Lepikhov &lt;<a href=3D"mailto:lepihov@gmail.com">lep=
ihov@gmail.com</a>&gt; wrote:</div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">
&gt; You can run as many queries as you want; the queries sitting server-si=
de,<br>
&gt; consuming CPU<br></blockquote><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">=C2=A0</blockquote><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex">
Yyou found the trick where the join list building routine consumes a lot of=
<br>
resources.=C2=A0</blockquote><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex">
Postgres code is aware of this situation and has a clear trade-off between<=
br>
performance and signal checking. Even if we fix this specific case, there a=
re<br>
multiple cycles in the code that might be expanded by a tricky query. So, c=
an<br>
you provide more context to define the problem&#39;s importance and scope s=
o we can<br>
determine whether this subject is actually important and should be fixed?<b=
r></blockquote><div><br></div><div>This problem can surface in the followin=
g situations:</div><div><br></div><div>a) &quot;growing analytics&quot; -- =
imagine tables sharded by very thin key (f.e., separate daily tables), and =
one</div><div>=C2=A0 =C2=A0 would want to run query against large date rang=
e;</div><div>b) blind SQL injection with ultra restricted user permissions =
(query only) became DoS instrument;<br><div>c) &quot;shared hosting&quot; o=
f some sort -- when the single server has ability to run queries from diffe=
rent users;</div><div>=C2=A0 =C2=A0 in this situation it would be just lack=
 of isolation. not a problem for the postgresql itself, it is more of a</di=
v><div>=C2=A0 =C2=A0 problem for services who build service on top of postg=
res;</div><div></div></div><div><br></div><div>There is another similar cas=
e with one more exploitation path:</div><div><br></div><div>python -c &quot=
;print(&#39;create table if not exists x(x int);&#39;);n=3D2000000;print(&#=
39;select 1 from x where 1=3D1&#39;,end=3D&#39; and x=3D1&#39;*n)&quot; | p=
sql -h 172.17.0.2 -Upostgres -A<br></div><div><br></div><div>the query is p=
erfectly valid repetition of &quot;x=3D1 and x=3D1 and ...&quot; that also =
leads to non-cancellable memory-eating query.</div><div><br></div><div>Can =
be a consequence of perfectly valid use-case:</div><div>=C2=A0 - simple tab=
le with category as a value</div><div>=C2=A0 - site has multiple-choice cat=
egory selector, that get POSTed as a form in some array sorts (cat[]=3DN&am=
p;cat[]=3DN or just cat=3DN&amp;cat=3DN etc)</div><div>=C2=A0 - the ORM con=
vert the array into query using &quot; &quot;.join(&quot;AND cat=3D%d&quot;=
 % (cat,) for cat in form[&#39;cat&#39;])</div><div><br></div><div><span st=
yle=3D"background-color:rgba(247,236,254,0.314)">The query is safe, the val=
ues are safe, no arbitrary injection, and yet, server DOS.</span></div></di=
v></div>

--00000000000038b4f80652456895--