> 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.
