ClickHouse

​

Step 1: Create a User and a Sandbox Database

CREATE USER pavo_analytics IDENTIFIED WITH sha256_password BY '<strong-password>' VALID UNTIL '2027-09-01';

CREATE DATABASE pavo_analysis;

CREATE ROLE pavo_analytics_role;

GRANT pavo_analytics_role TO pavo_analytics;

• 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 '<new-password>'; then update the password in Pavo. To revoke: DROP USER pavo_analytics;

​

Step 2: Grant ClickHouse Permissions

-- 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 <database>.* 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

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

​

Step 4: Verify the User (optional)

curl -sS "https://<host>:8443/" -u "pavo_analytics:<password>" --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).

curl -sS "https://<host>:8443/" -u "pavo_analytics:<password>" --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

1. Open the service → Connect
2. Select HTTPS
3. Copy the hostname (without https:// and port)