Why is "Cannot modify setting in readonly mode" thrown when not explicitly modify it in ClickHouse?

Question

Applications were using readonly user when the following exception was thrown:

<Error> HTTPHandler: Code: 164, e.displayText() = DB::Exception: Cannot modify 'result_overflow_mode' setting in readonly mode

I read ClickHouse code and found that when query goes with set setting, if the user is read-only, this error will be thrown.

I checked the configuration of my read-only-user in users.xml and found that max_result_rows/bytes and result_overflow_mode were not actively set.

However, my query does not go with set setting.

So what logic triggers the change of this setting?

Answer 1

Try to specify <readonly>2</readonly>.

Description of readonly parameter: https://clickhouse.tech/docs/en/operations/settings/permissions_for_queries/#settings_readonly

Possible values:
0 — All queries are allowed.
1 — Only read data queries are allowed.
2 — Read data and change settings queries are allowed.

So, with readonly=2 you are let to you client to set result_overflow_mode but dont let to execute inserts and other requests of modifying data.