Getting "Cannot modify 'max_result_rows' setting in readonly mode" in cloudbeaver for any clickhouse select query

Question

I successfully authenticated to clickhouse in cloudbeaver using readonly user and able to see dbs and their tables.

However, for each attempt to view table contents I get below error in cloudbeaver:

SQL Error [164] [07000]: Code: 164. DB::Exception: Cannot modify 'max_result_rows' setting in readonly mode. (READONLY) (version 22.3.12.20.altinitystable (altinity build))
, server ClickHouseNode

Found docs on max_result_rows and I see that max_result_rows is 0 in driver properties in cloudbeaver.

Why it's not possible to perform read queries (select) by readonly users in cloudbeaver and how to overcome this?

Answer 1

how did you create your readonly user? Altering a setting in ClickHouse requires a GRANT for "ALTER SETTINGS". I can reproduce that error if missing the "ALTER SETTINGS" grant.

There are two methods of creating a user with the ability to change settings:

(a) If the readonly user was created with the option readonly = 2:

CREATE [USER|ROLE]...SETTINGS readonly = 2;

https://clickhouse.com/docs/en/operations/settings/constraints-on-settings readonly=2 : Only read queries are allowed, but settings can be changed, except for readonly setting itself.

(b) Create the user and role without the readonly option, but then assign the SELECT and ALTER SETTINGS grants:

GRANT SELECT ON [database].* TO ...
GRANT ALTER SETTINGS ON [database].* TO ...