Run read-only ClickHouse queries over HTTP

ClickHouse’s HTTP interface (port 8123, or 8443 with TLS) is the easiest way to query it from scripts and tools, and it accepts any setting as a URL parameter. Pass readonly=1 and the server rejects writes and DDL for that request. Add max_result_rows, result_overflow_mode and max_execution_time and a mistyped query cannot stream a billion rows to your laptop.

The request parameters are a convenience, though. Anyone holding the password can leave them out. The server-side boundary is a dedicated user that only has SELECT grants, plus a settings profile that caps how much a query can consume.

Step 1: send a query over HTTP

The query goes in the POST body. Settings go in the query string:

echo 'SELECT event_type, count() FROM analytics.events GROUP BY event_type' |
  curl -sS -u inspector:"$CH_PASSWORD" \
    'https://ch.internal:8443/?readonly=1&default_format=JSONCompact' \
    --data-binary @-

-u sends HTTP basic authentication. JSONCompact returns a meta array of column names and types and a data array of rows, which is easy to parse. A request sent with GET is also treated as read-only by ClickHouse, but POST lets you send long queries without URL-encoding them.

Step 2: know what readonly blocks

readonly	Allows
0	Everything the user’s grants allow
1	Read queries only; changing settings is also forbidden
2	Read queries, and changing settings

With readonly=1, INSERT, ALTER, CREATE, DROP and TRUNCATE fail with a “cannot execute query in readonly mode” error. SHOW CREATE TABLE, DESCRIBE, system.* tables and SELECT work.

Step 3: bound the result and the runtime

max_result_rows=500
result_overflow_mode=break
max_execution_time=15

• max_result_rows caps the rows returned. With result_overflow_mode=break, the server stops and returns what it has instead of raising an error. The check happens per block, so the result can slightly exceed the limit. Trim on the client if you need an exact number.
• max_execution_time is in seconds. The query is cancelled when it runs longer.

Your HTTP client should also have its own timeout, a little longer than the server’s, so a dead connection does not hang forever.

Step 4: make it a server-side rule

URL parameters can be removed by anyone holding the password. Grants and profile constraints cannot:

CREATE SETTINGS PROFILE inspector_profile SETTINGS
    max_execution_time = 15 MAX 60,
    max_result_rows = 500 MAX 10000;

CREATE USER inspector
    IDENTIFIED WITH sha256_password BY 'use-a-long-random-password'
    SETTINGS PROFILE 'inspector_profile';

GRANT SELECT ON analytics.* TO inspector;

• GRANT SELECT ON analytics.* is the actual write protection. Without INSERT, ALTER, CREATE or DROP grants, those statements fail whatever settings the request carries.
• The profile sets defaults, and MAX is a constraint: a client may lower the limits or raise them up to the maximum, but not beyond.

This needs SQL-driven access control to be enabled for the administrating user (access_management). If you manage users in XML instead, the same settings and constraints go under <profiles> and <constraints>. On ClickHouse Cloud, create users and grants from the SQL console in the same way.

You could also put readonly = 1 in the profile. Be aware that readonly = 1 forbids changing any setting, so tools that send their own per-request limits, DevOpsPad included, will have those requests rejected. Grants plus constraints protect the data without that side effect.

Step 5: verify

echo 'CREATE TABLE analytics.t (x UInt8) ENGINE = Memory' |
  curl -sS -u inspector:"$CH_PASSWORD" 'https://ch.internal:8443/' --data-binary @-
# Code: 497. DB::Exception: inspector: Not enough privileges. … (ACCESS_DENIED)

Then send a query with ?max_execution_time=600 in the URL. It should be rejected because it exceeds the profile’s MAX 60.

Doing this in DevOpsPad

DevOpsPad talks to ClickHouse over the HTTP interface only, on 8123 or 8443 with TLS. Every query is sent with readonly=1, max_result_rows=500, result_overflow_mode=break and max_execution_time=15, using JSONCompact output. Numbers are decoded without losing precision, the response is read up to 8 MiB, and the client stops waiting after 20 seconds. Redirects are not followed. The schema browser lists databases and tables, with columns, skipping indexes and DDL.

Those are client-side guards. Pair them with the inspector user from Step 4 and they become a second layer instead of the only one. Its limits fall within that profile’s constraints. The same adapter works with self-hosted ClickHouse and ClickHouse Cloud, directly or through an SSH tunnel. See connecting to ClickHouse, ClickHouse queries and the ClickHouse connector page.