ClickHouse User Role and Profile

1. Monitoring user role

Suggested role for monitoring user must have these privileges on system database:

# File: users.d/monitoring_role.xml
<clickhouse>
  <users>
    <monitoring>
      <password><!-- define password here --></password>
      <profile>monitoring_profile</profile>
      <networks><ip>::/0</ip></networks>
      <grants>
        <query>GRANT monitoring_role</query>
      </grants>
    </monitoring>
  </users>
 
  <roles>
    <monitoring_role>
      <grants>
        <query>REVOKE ALL ON *.*</query>
        <query>GRANT SELECT,SHOW,OPTIMIZE,dictGet,REMOTE ON *.*</query>
        <query>GRANT CREATE TEMPORARY TABLE ON *.*</query>
        <query>GRANT CREATE KILL QUERY ON *.*</query>
        <query>GRANT SELECT,INSERT,ALTER,CREATE,DROP,TRUNCATE ON system.monitoring_events</query>
      </grants>
    </monitoring_role>
  </roles>
</clickhouse>

Privileges explains:

CREATE TEMPORARY TABLE is needed because the UI uses FROM merge(system, '^query_log'), which allows retrieving all the data from old tables that were renamed during the upgrade.
OPTIMIZE is needed for the table optimization action on Data Explorer.
KILL QUERY is needed for the “Kill query” action on Running Queries.
INSERT, ALTER, CREATE, DROP, and TRUNCATE are needed for the feat: add pageview tracking #243 feature, which is configurable. You should change system.monitoring_events to your own database and table.

Optimize Table Action

Running Queries Action

2. Monitoring user profile

# File: users.d/monitoring_profile.xml
<clickhouse>
  <profiles>
    <monitoring_profile>
      <allow_experimental_analyzer>1</allow_experimental_analyzer>
 
      <!-- Optional: query cache to avoid hit too much queries on database -->
      <use_query_cache>1</use_query_cache>
      <query_cache_ttl>50</query_cache_ttl>
      <query_cache_max_entries>0</query_cache_max_entries>
      <query_cache_system_table_handling>save</query_cache_system_table_handling>
      <query_cache_nondeterministic_function_handling>save</query_cache_nondeterministic_function_handling>
    </monitoring_profile>
  </profiles>
</clickhouse>

Local Development Enable System Tables