> ## Documentation Index > Fetch the complete documentation index at: https://docs.pavoai.com/llms.txt > Use this file to discover all available pages before exploring further. # ClickHouse > Create a scoped ClickHouse user and connect ClickHouse Cloud or self-hosted ClickHouse to Pavo. Works with ClickHouse Cloud and self-hosted ClickHouse 23.x or newer over HTTPS. | Source | Used for | | --------------------------------- | ------------------------------------------------------------------------------------------------ | | `system.tables`, `system.columns` | Table and column catalog: types, partition / sorting / primary keys, comments, row counts, sizes | | `system.query_log` | Query history: normalized query text, who ran it, duration, bytes read | | `SELECT ... LIMIT` on your tables | Sample values and null / distinct statistics per column | ## Step 1: Create a User and a Sandbox Database Create a dedicated database user that Pavo will use. | Option | Best For | Security Level | | ----------------------------- | ----------------------- | -------------------------------------------------------------------------------------------------------------------- | | Dedicated user (Recommended) | Production environments | High – SELECT everywhere, writes only in the `pavo_analysis` analysis database, expiry date, revocable independently | | Existing / default admin user | Quick setup / testing | Low – full admin rights, shared credential | Run the following as an admin (the `default` user on ClickHouse Cloud, or any user with `ACCESS MANAGEMENT`). Use a strong password: 12+ characters, upper and lower case, a number or symbol. ```sql theme={null} CREATE USER pavo_analytics IDENTIFIED WITH sha256_password BY '' VALID UNTIL '2027-09-01'; CREATE DATABASE pavo_analysis; CREATE ROLE pavo_analytics_role; GRANT pavo_analytics_role TO pavo_analytics; ``` Do not set `readonly = 1` on this user or its settings profile. It blocks `INSERT` regardless of grants and breaks the sandbox. Writes are confined by the grants in Step 2 instead. * `pavo_analysis` is Pavo's analysis database: the only place Pavo writes (scratch tables during analysis), the same role as the analysis schema on Databricks. * `VALID UNTIL` is optional but recommended. Pick your rotation date; the connector will start failing auth after it and Pavo will alert. * To rotate: `ALTER USER pavo_analytics IDENTIFIED BY '';` then update the password in Pavo. To revoke: `DROP USER pavo_analytics;` ## Step 2: Grant ClickHouse Permissions Grant the role read access to the databases Pavo should see, write access confined to the sandbox, and (Cloud only) the ability to read query history from every replica. ```sql theme={null} -- Read across all databases, system tables included. CURRENT GRANTS form because the Cloud SQL console cannot grant SELECT ON *.* directly GRANT CURRENT GRANTS(SELECT ON *.*) TO pavo_analytics_role; -- Writes confined to the sandbox, nothing else GRANT INSERT, CREATE, DROP, ALTER ON pavo_analysis.* TO pavo_analytics_role; -- ClickHouse Cloud only: query_log is per node, this lets Pavo read it from every replica GRANT CURRENT GRANTS(REMOTE ON *.*) TO pavo_analytics_role; ``` * To limit Pavo to specific databases, replace `SELECT ON *.*` with one `GRANT SELECT ON .* TO pavo_analytics_role;` per database, plus `GRANT SELECT ON pavo_analysis.* TO pavo_analytics_role;` (so Pavo can read back what it writes there), `GRANT SELECT ON system.query_log* TO pavo_analytics_role;` (the wildcard covers `query_log_0`, `query_log_1`, … that upgrades leave behind, so older history is not lost) and `GRANT SELECT ON system.clusters TO pavo_analytics_role;` — one statement each, ClickHouse does not accept a comma list with a wildcard * Without the `system.query_log` grant Pavo still syncs tables and columns and skips query history. * Without `REMOTE`, Pavo still reads query history from the node it connects to. Multi-replica Cloud services then show partial history. * `system.clusters` lets Pavo detect which cluster to read query history across. If the user cannot read it, or the server belongs to more than one cluster, set the **Cluster** field in Step 5 instead. ## Step 3: Allow Network Access **ClickHouse Cloud**: new services default to "Allow from anywhere". To confirm: 1. Open the service in the ClickHouse Cloud console → **Settings → Security → IP access list** 2. "Allow from anywhere" means nothing to do. If it lists specific addresses, tell your Pavo contact **Self-hosted**: make sure the HTTPS port (8443 by default) is reachable from the internet. If it is IP-restricted, tell your Pavo contact. PrivateLink / private networking is also fine; Pavo only needs outbound HTTPS to your host. ## Step 4: Verify the User (optional) From any machine with `curl`: ```bash theme={null} curl -sS "https://:8443/" -u "pavo_analytics:" --data-binary "SELECT count() FROM system.tables WHERE database NOT IN ('system','INFORMATION_SCHEMA','information_schema')" ``` * A number comes back → the user works. * `Authentication failed` → check user / password (Step 1). * Connection timeout → check network access (Step 3). ClickHouse Cloud only, checks the replica-wide query history read: ```bash theme={null} curl -sS "https://:8443/" -u "pavo_analytics:" --data-binary "SELECT count() FROM clusterAllReplicas('default', system.query_log)" ``` * `Not enough privileges` → the `REMOTE` grant is missing (Step 2). ## Step 5: Share Connection Details In Pavo, go to **Connectors → ClickHouse**, fill in the fields below and click **Connect**. Pavo runs `SELECT 1` on save and rejects the connector if it cannot connect. | Field | Example | Required | | ------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------- | | **Host** | `abc123.us-east-1.aws.clickhouse.cloud` | Yes | | **Port** | `8443` (Cloud, HTTPS). Self-hosted: use the HTTPS port (`8443` or `443`); plain HTTP `8123` passes the connection check but Pavo's analysis sandbox only reaches hosts over HTTPS | Yes | | **User** | `pavo_analytics` | Yes | | **Password** | from Step 1 | Yes | | **Database** | `analytics` (blank = every non-system database) | Optional | | **Cluster** | `default` (ClickHouse Cloud). Blank = detected from `system.clusters`; set it when that table is not readable or the server is in several clusters | Optional | To find your host on ClickHouse Cloud: 1. Open the service → **Connect** 2. Select **HTTPS** 3. Copy the hostname (without `https://` and port)