Readonly user and "Cannot modify 'max_execution_time' setting in readonly mode."

[ghost]

What happened:

Using clickhouse-datasource 2.1.1, when running SELECT 1 query with a readonly user I get the error:

error querying the database: clickhouse [execute]:: 500 code: Code: 164. DB::Exception: Cannot modify 'max_execution_time' setting in readonly mode. (READONLY) (version 23.1.3.5 (official build)) 

What you expected to happen:

I expected a readonly user to query the data without any errors as with version 2.1.0 of the plugin.

How to reproduce it (as minimally and precisely as possible):

1. Install version 2.1.1 of the clickhouse-datasource plugin
2. Configure the datasource with a readonly user
3. From the explore panel select the datasource and run the query SELECT 1
4. See the error message

Anything else we need to know?:

There is no error when using clickhouse-datasource version 2.1.0 or before.

I've been trying to figure out where this could come from. I think that somewhere in this plugin or its go dependencies the max_execution_time parameter is set by default within the query.

Extract from the clickhouse-server logs (clickhouse datasource 2.1.1):

2023.03.17 09:37:26 [] {} <Trace> HTTPSHandler-factory: HTTP Request for HTTPSHandler-factory. Method: POST, User-Agent: grafana/9.3.6 clickhouse-datasource/2.1.1 clickhouse-go/2.7.0 (lv:go/1.19.7; os:linux), Length: 14, ...
2023.03.17 09:37:26 [] {} <Trace> DynamicQueryHandler: Request URI: /?database=default&default_format=Native&max_execution_time=64
2023.03.17 09:37:26 [] {} <Error> DynamicQueryHandler: Code: 164. DB::Exception: Cannot modify 'max_execution_time' setting in readonly mode. (READONLY),

Extract from the clickhouse-server logs (clickhouse datasource 2.1.0):

2023.03.17 12:31:37 [] {} <Trace> HTTPSHandler-factory: HTTP Request for HTTPSHandler-factory. Method: POST, User-Agent: grafana/9.3.6 clickhouse-datasource/2.1.0 clickhouse-go/2.5.1 (database/sql; lv:go/1.19.7; os:linux), Length: 8, ...
2023.03.17 12:31:37 [] {} <Trace> DynamicQueryHandler: Request URI: /?database=default&default_format=Native
2023.03.17 12:31:37 [] {} <Debug> executeQuery: SELECT 1 (stage: Complete)

Environment:

• Grafana version: 9.3.6
• Plugin version: 2.1.1

[abh]

Installing 2.1.0 instead of 2.1.1 fixed this for me. In case anyone else comes across this, the version can be specified in the helm chart by adding it after the plugin name and a space in the plugin list.

[mastertheknife]

I am also encountering this issue. I have downgraded to v2.1.0 and now its fine.

[jkaflik]

hi @abh @mastertheknife @pnax

max_execution_time is set by clickhouse-go client and it's expected as you have query timeout set. For some reason, it didn't work in previous versions. I will investigate it.

What is important, your user has to have readonly flag set as 2. https://clickhouse.com/docs/en/operations/settings/permissions-for-queries#readonly
Otherwise, ClickHouse will reject your queries as expected.

Probably, it should be included in sort of documentation.

[mshustov]

@jkaflik we had it in a blog post, but it should be a part of the docs, for sure:

additional_table_filters are passed in SETTINGS with the query itself. This is not permitted for read-only users unless readonly=2. This is not ideal, and we would not recommend setting this for public instances of ClickHouse. We recognize improvement is required here. Stay tuned.

[ghost]

Hi @jkaflik and @mshustov and thanks for the comments and pointers.

Reading the provided resources and digging into Clickhouse documentation on constraints I found:

• readonly=0: No read-only restrictions.
• readonly=1: Only read queries are allowed and settings cannot be changed unless changeable_in_readonly is set.
• readonly=2: Only read queries are allowed, but settings can be changed, except for readonly setting itself.

I understand there is no bug in the clickhouse-datasource plugin. Just an error in my Clickhouse configuration.

I managed to fix the error by updating the config.xml and users.xml files:

<!-- config.xml -->
<clickhouse>
  <access_control_improvements>
    <settings_constraints_replace_previous>true</settings_constraints_replace_previous>
  </access_control_improvements>
</clickhouse>

<!-- users.xml -->
<clickhouse>
  <profiles>
    <readonly>
      <constraints>
        <max_execution_time>
          <changeable_in_readonly/>
          <min>0</min>
          <max>180</max>
        </max_execution_time>
      </constraints>
    </readonly>
  </profiles>
</clickhouse>

Another shorter solution would be as you suggested to set the readonly flag to 2. However as this was stated in the blog post, this is not ideal for a public instance.

Since my issue is solved, I'm closing it. Thanks again for the answers and pointers.

[ghost]

@jkaflik Probably, it should be included in sort of documentation.
@mshustov but it should be a part of the docs, for sure

I was thinking about your comments. Would it help if I provide an update of the README file with the solution I used?

[jkaflik]

@pnax, that would be much appreciated

[ghost]

Ok, I've open #342. Let me know if this is clear enough.