public inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Anton Fedorov <datacompboy@gmail.com>
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Fwd: Non-cancellable queries
Date: Wed, 20 May 2026 22:11:44 +0200
Message-ID: <CACNOLFGeVM+xdjd7ieR-kp+jV7gJOHxZdkqsaxOojRqiKakqKA@mail.gmail.com> (raw)
In-Reply-To: <3b02a3fe-bf62-4231-8ea8-5021f4846e2d@gmail.com>
References: <CACNOLFFvMX521BZvPFyGwW=WOWk_1u-zqAm+j3BerYA6mx0jCA@mail.gmail.com>
<CACNOLFGH_GPGXwzN23RXYbj3PaDxYKahHRGXnchCa7B=1TS7+w@mail.gmail.com>
<3b02a3fe-bf62-4231-8ea8-5021f4846e2d@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.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: datacompboy@gmail.com, lepihov@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Fwd: Non-cancellable queries
In-Reply-To: <CACNOLFGeVM+xdjd7ieR-kp+jV7gJOHxZdkqsaxOojRqiKakqKA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox