# ClickHouse User & Grants

chmonitor needs a ClickHouse user with at minimum `SELECT` on `system.*`. Do not use an admin account for a shared dashboard.

## Minimum: read-only monitoring user

This user can view all monitoring pages. It cannot kill queries, run optimizations, or mutate data.

```sql
-- Create the user
CREATE USER monitoring
  IDENTIFIED WITH sha256_password BY 'your-password'
  HOST ANY;

-- Grant read-only access to system tables
GRANT SELECT ON system.* TO monitoring;

-- Allow temporary tables (required for some merge-aware queries)
GRANT CREATE TEMPORARY TABLE ON *.* TO monitoring;
```

## Optional grants for actions

Add these only if you want operators to use the corresponding actions from the UI.

```sql
-- Kill Query action (Running Queries page)
GRANT KILL QUERY ON *.* TO monitoring;

-- Optimize Table action (Data Explorer page)
GRANT OPTIMIZE ON *.* TO monitoring;
```

> **Important:** `AGENT_ENABLE_CONTROL_TOOLS` must also be set to `true` for the AI agent to use kill/optimize. Keep it `false` (the default) on public or shared deployments.

## Optional: self-tracking events table

chmonitor can write dashboard pageview events to a ClickHouse table (`system.monitoring_events` by default). Change `EVENTS_TABLE_NAME` to a table in your own database if you prefer not to write to `system.*`.

If you enable this, the monitoring user needs write access to that table:

```sql
GRANT SELECT, INSERT
  ON your_database.monitoring_events
  TO monitoring;
```

## Per-feature grants checklist

`GRANT SELECT ON system.*` covers the majority of the dashboard. Some features read tables that require additional grants or depend on optional system tables that are only present when a subsystem is configured.

### Always required

| Dashboard feature | System tables used | Grant |
|---|---|---|
| Overview metrics | `system.metrics`, `system.asynchronous_metrics` | `SELECT ON system.*` |
| Running queries | `system.processes` | `SELECT ON system.*` |
| Active merges | `system.merges` | `SELECT ON system.*` |
| Replica status | `system.replicas` | `SELECT ON system.*` |
| Disks & storage | `system.disks`, `system.parts` | `SELECT ON system.*` |
| Tables list | `system.tables`, `system.columns` | `SELECT ON system.*` |
| Clusters | `system.clusters` | `SELECT ON system.*` |
| Mutations | `system.mutations` | `SELECT ON system.*` |
| Replication queue | `system.replication_queue` | `SELECT ON system.*` |
| Settings / users / roles | `system.settings`, `system.users`, `system.roles` | `SELECT ON system.*` |
| Warnings | `system.warnings` | `SELECT ON system.*` |
| Errors | `system.errors` | `SELECT ON system.*` |
| Dictionaries | `system.dictionaries` | `SELECT ON system.*` |

### Requires `system.query_log` (enabled by default in most setups)

| Feature | Tables | Grant |
|---|---|---|
| Query history | `system.query_log` | `SELECT ON system.*` |
| Slow / expensive queries | `system.query_log` | `SELECT ON system.*` |
| Failed queries | `system.query_log` | `SELECT ON system.*` |
| Common errors | `system.errors` | `SELECT ON system.*` |
| Top tables / columns by usage | `system.query_log` | `SELECT ON system.*` |

### Optional — tables present only when the subsystem is configured

These pages show a "table not available" notice when the underlying table does not exist. No error is raised.

| Feature | Required table | Notes |
|---|---|---|
| Query thread analysis / parallelization | `system.query_thread_log` | Enable in ClickHouse server config |
| Query profiler | `system.processors_profile_log` | Enable in ClickHouse server config |
| Query views log | `system.query_views_log` | Available from ClickHouse 22.4+ |
| Query cache | `system.query_cache` | Available from ClickHouse 23.5+ |
| User processes | `system.user_processes` | Available from ClickHouse 23.3+ |
| Merge performance | `system.part_log` | Enable `part_log` in server config |
| Part log | `system.part_log` | Enable `part_log` in server config |
| Query metric log | `system.query_metric_log` | Enable in ClickHouse server config |
| Detached parts | `system.detached_parts` | Always exists; may be empty |
| Dropped tables | `system.dropped_tables` | Available from ClickHouse 23.x+ |
| Distributed DDL queue | `system.distributed_ddl_queue` | Only on clusters using DDL via ZooKeeper/Keeper |
| Moves | `system.moves` | Only when tiered storage / volume moves are active |
| Replicated fetches | `system.replicated_fetches` | Only on replicated clusters |
| View refreshes | `system.view_refreshes` | Only when Refreshable Materialized Views are in use |
| Skip indexes (Data Explorer) | `system.data_skipping_indices` | Available from ClickHouse 22.x+ |
| Login attempts / sessions | `system.session_log` | Enable in ClickHouse server config |
| Text log | `system.text_log` | Enable `text_log` in server config |
| Crash log | `system.crash_log` | Always exists on supported versions |
| **Backups** | `system.backup_log` | Optional. Only exists when backups are configured. |
| **Error log** | `system.error_log` | Optional. Enable `error_log` in server config. |

### ClickHouse Keeper / ZooKeeper (all optional)

All Keeper pages are `optional: true` — they silently degrade to a notice when Keeper is not configured.

| Feature | Required table | Notes |
|---|---|---|
| Keeper overview | `system.zookeeper_info` | Requires Keeper/ZooKeeper |
| Keeper connections | `system.zookeeper_connection` | Requires Keeper/ZooKeeper |
| Keeper watches | `system.zookeeper_watches` | Requires Keeper/ZooKeeper |
| Keeper log | `system.zookeeper_log` | Requires Keeper/ZooKeeper |
| Keeper connection log | `system.zookeeper_connection_log` | Available from ClickHouse 25.8+ |
| ZooKeeper data browser | `system.zookeeper` | Requires Keeper/ZooKeeper |

No special grants are needed for Keeper tables — `SELECT ON system.*` covers them. They simply do not exist when Keeper is not running.

### Kafka (optional)

| Feature | Required table | Notes |
|---|---|---|
| Kafka consumers | `system.kafka_consumers` | Only exists when Kafka engine tables are present |

### Action grants (not SELECT)

| Action | Grant needed |
|---|---|
| Kill Query (Running Queries page / AI agent) | `GRANT KILL QUERY ON *.* TO monitoring;` |
| Optimize Table (Data Explorer / AI agent) | `GRANT OPTIMIZE ON *.* TO monitoring;` |
| Create temporary tables (some merge queries) | `GRANT CREATE TEMPORARY TABLE ON *.* TO monitoring;` |

## Optional: ClickHouse Keeper / ZooKeeper access

`system.zookeeper` is available only when ZooKeeper or ClickHouse Keeper is configured. No special grants are needed — `SELECT ON system.*` covers it.

## Multi-host setup

Each ClickHouse host in a multi-host deployment can have its own user and password. Use comma-separated values in the same position across all four variables:

```bash
CLICKHOUSE_HOST=https://prod-a:8443,https://prod-b:8443
CLICKHOUSE_USER=monitoring,monitoring
CLICKHOUSE_PASSWORD=secret-a,secret-b
CLICKHOUSE_NAME=prod-a,prod-b
```

`CLICKHOUSE_HOST` defines the host count. `CLICKHOUSE_USER` and `CLICKHOUSE_PASSWORD` may be a single shared value or one value per host position. `CLICKHOUSE_NAME` is optional. Position N maps to host index N.

## Recommended ClickHouse profile

Create a monitoring profile to enable query caching and the experimental analyzer:

```xml
<!-- /etc/clickhouse-server/users.d/monitoring_profile.xml -->
<clickhouse>
  <profiles>
    <monitoring_profile>
      <allow_experimental_analyzer>1</allow_experimental_analyzer>

      <!-- Optional: reduce repeated load from dashboard queries -->
      <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>

  <users>
    <monitoring>
      <profile>monitoring_profile</profile>
    </monitoring>
  </users>
</clickhouse>
```