Clickhouse Cloud Management

ClickHouse Cloud Management

ADR: 2025-12-08-clickhouse-cloud-management-skill

Self-Evolving Skill: This skill improves through use. If instructions are wrong, parameters drifted, or a workaround was needed — fix this file immediately, don't defer. Only update for real, reproducible issues.

Overview

ClickHouse Cloud user and permission management via SQL commands over HTTP interface. This skill covers database user creation, permission grants, and credential management for ClickHouse Cloud instances.

Schema documentation principle: All ClickHouse table/column COMMENTs are the single source of truth (SSoT). When creating tables or columns, always include COMMENT clauses. See quality-tools:clickhouse-architect for the full COMMENT SSoT policy.

When to Use This Skill

Invoke this skill when:

• Creating database users for ClickHouse Cloud
• Managing user permissions (GRANT/REVOKE)
• Testing ClickHouse Cloud connectivity
• Troubleshooting authentication issues
• Understanding API key vs database user distinction

Key Concepts

Management Options

ClickHouse Cloud provides two management interfaces with different capabilities:

Key distinction: Database users (created via SQL) authenticate to ClickHouse itself. API keys (created via console) authenticate to the ClickHouse Cloud management API.

Connection Details

ClickHouse Cloud exposes only HTTP interface publicly:

• Port: 443 (HTTPS)
• Protocol: HTTP (not native ClickHouse protocol)
• Native protocol: Requires AWS PrivateLink (not available without enterprise setup)

Password Requirements

ClickHouse Cloud enforces strong password policy:

• Minimum 12 characters
• At least 1 uppercase letter
• At least 1 special character

Example compliant password: StrongPass@2025!

Quick Reference

Create Read-Only User

curl -s "https://default:PASSWORD@HOST:443/" --data-binary \
  "CREATE USER my_reader IDENTIFIED BY 'StrongPass@2025!' SETTINGS readonly = 1"

Grant Database Access

curl -s "https://default:PASSWORD@HOST:443/" --data-binary \
  "GRANT SELECT ON deribit.* TO my_reader"

Delete User

curl -s "https://default:PASSWORD@HOST:443/" --data-binary \
  "DROP USER my_reader"

For comprehensive SQL patterns and advanced permission scenarios, see SQL Patterns Reference.

Credential Sources

1Password Items (Engineering Vault)

Retrieving Credentials

# Database credentials (for SQL commands)
op item get "gapless-deribit-clickhouse" --vault Engineering --reveal

# API key (for cloud management API)
op item get "ClickHouse Cloud - API Key (Admin)" --vault Engineering --reveal

Common Workflows

Workflow 1: Create Application User

1. Retrieve default user credentials from 1Password 2. Create new user with appropriate permissions:

HOST="your-instance.clickhouse.cloud"
PASSWORD="default-user-password"

# Create user
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "CREATE USER app_user IDENTIFIED BY 'AppPass@2025!'"

# Grant specific database access
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT SELECT, INSERT ON mydb.* TO app_user"

Workflow 2: Verify User Exists

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "SHOW USERS"

Workflow 3: Test Connection

curl -s "https://user:password@HOST:443/" --data-binary "SELECT 1"

Expected output: 1 (single row with value 1)

Troubleshooting

Authentication Failed

• Verify password meets complexity requirements
• Check host URL includes port 443
• Ensure using HTTPS (not HTTP)

Permission Denied

• Verify user has required GRANT statements
• Check database and table names are correct
• Confirm user was created with correct settings

Connection Timeout

• ClickHouse Cloud only exposes port 443 publicly
• Native protocol (port 9440) requires PrivateLink
• Use HTTP interface with curl or clickhouse-client HTTP mode

Next Steps After User Creation

<!-- ADR: 2025-12-10-clickhouse-skill-delegation -->

After creating a ClickHouse user, invoke `devops-tools:clickhouse-pydantic-config` to generate DBeaver configuration with the new credentials.

Additional Resources

Reference Files

For detailed patterns and advanced techniques, consult:

• [references/sql-patterns.md](./references/sql-patterns.md) - Complete SQL syntax reference with examples

Python Driver Policy

For Python application code connecting to ClickHouse Cloud, use clickhouse-connect (official HTTP driver). See `clickhouse-architect` for recommended code patterns and why to avoid clickhouse-driver (community).

Related Skills

• quality-tools:clickhouse-architect - Schema design, compression codecs, Python driver policy
• devops-tools:clickhouse-pydantic-config - DBeaver configuration generation
• devops-tools:doppler-secret-validation - For storing credentials in Doppler
• devops-tools:doppler-workflows - For credential rotation workflows

Post-Execution Reflection

After this skill completes, check before closing:

1. Did the command succeed? — If not, fix the instruction or error table that caused the failure. 2. Did parameters or output change? — If the underlying tool's interface drifted, update Usage examples and Parameters table to match. 3. Was a workaround needed? — If you had to improvise (different flags, extra steps), update this SKILL.md so the next invocation doesn't need the same workaround.

Only update if the issue is real and reproducible — not speculative.

Evolution Log

Convention: Reverse chronological order (newest on top, oldest at bottom). Prepend new entries.

---

2026-02-26: Initial Evolution Log

Status: Skill is in use and maintained. Track improvements here.

Purpose

This evolution log tracks updates to the skill. Each entry should note:

• What changed (content, structure, tooling)
• Why it changed (bug fix, feature request, best practice)
• Files affected

How to Use

1. When updating SKILL.md or references, add an entry here with the date 2. Keep entries reverse-chronological (newest first) 3. Link to ADRs or GitHub issues when relevant 4. Reference specific line changes when helpful

---

Skill: ClickHouse Cloud Management

SQL Patterns Reference

Comprehensive SQL patterns for ClickHouse Cloud user and permission management via HTTP interface.

Connection Format

All commands use curl with HTTP basic auth:

curl -s "https://USER:PASSWORD@HOST:443/" --data-binary "SQL_COMMAND"

Variables:

• USER - Database username (typically default for admin operations)
• PASSWORD - User password
• HOST - ClickHouse Cloud instance hostname (e.g., abc123.clickhouse.cloud)

User Management

Create User

# Basic user creation
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "CREATE USER username IDENTIFIED BY 'password'"

# Read-only user (cannot modify data)
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "CREATE USER reader IDENTIFIED BY 'ReaderPass@2025!' SETTINGS readonly = 1"

# User with specific default database
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "CREATE USER app_user IDENTIFIED BY 'AppPass@2025!' DEFAULT DATABASE mydb"

List Users

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "SHOW USERS"

Show User Grants

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "SHOW GRANTS FOR username"

Delete User

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "DROP USER username"

# Delete if exists (no error if missing)
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "DROP USER IF EXISTS username"

Alter User Password

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "ALTER USER username IDENTIFIED BY 'NewPassword@2025!'"

Permission Management

Grant SELECT (Read Access)

# Single database
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT SELECT ON mydb.* TO username"

# Single table
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT SELECT ON mydb.mytable TO username"

# All databases (use carefully)
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT SELECT ON *.* TO username"

Grant INSERT (Write Access)

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT INSERT ON mydb.* TO username"

Grant Multiple Permissions

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT SELECT, INSERT, ALTER ON mydb.* TO username"

Revoke Permissions

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "REVOKE SELECT ON mydb.* FROM username"

Grant Admin Privileges

# Full admin (use sparingly)
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT ALL ON *.* TO admin_user"

Common Permission Patterns

Pattern: Application Read-Only User

# Create user
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "CREATE USER app_reader IDENTIFIED BY 'AppReader@2025!' SETTINGS readonly = 1"

# Grant read access to specific database
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT SELECT ON production.* TO app_reader"

Pattern: Application Read-Write User

# Create user
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "CREATE USER app_writer IDENTIFIED BY 'AppWriter@2025!'"

# Grant read/write access
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT SELECT, INSERT ON production.* TO app_writer"

Pattern: Analytics User (Read + Create Temp Tables)

# Create user
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "CREATE USER analyst IDENTIFIED BY 'Analyst@2025!'"

# Grant read access + ability to create temporary tables
curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT SELECT ON production.* TO analyst"

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary \
  "GRANT CREATE TEMPORARY TABLE ON *.* TO analyst"

Database Operations

List Databases

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "SHOW DATABASES"

List Tables in Database

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "SHOW TABLES FROM mydb"

Describe Table Schema

curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "DESCRIBE TABLE mydb.mytable"

Testing and Verification

Test Connection

# Simple connectivity test
curl -s "https://user:password@$HOST:443/" --data-binary "SELECT 1"
# Expected output: 1

# Get server version
curl -s "https://user:password@$HOST:443/" --data-binary "SELECT version()"

Verify User Permissions

# Check current user
curl -s "https://user:password@$HOST:443/" --data-binary "SELECT currentUser()"

# Check if user can read specific table
curl -s "https://user:password@$HOST:443/" --data-binary "SELECT count() FROM mydb.mytable"

Test Insert Permission

# Attempt insert (will fail if no INSERT grant)
curl -s "https://user:password@$HOST:443/" --data-binary \
  "INSERT INTO mydb.test_table (id) VALUES (1)"

Error Handling

Common Errors

Check Error Details

# Add FORMAT Vertical for readable errors
curl -s "https://user:password@$HOST:443/" --data-binary \
  "SELECT * FROM nonexistent FORMAT Vertical"

Best Practices

Password Generation

Generate compliant passwords (12+ chars, uppercase, special):

# Using openssl
openssl rand -base64 16 | tr -d '/+=' | head -c 16
# Then manually add uppercase and special char

# Example format: Base16Chars@2025!

Naming Conventions

Audit Commands

/usr/bin/env bash << 'SQL_PATTERNS_SCRIPT_EOF'
# List all users and their grants
for user in $(curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "SHOW USERS" | tr '\n' ' '); do
  echo "=== $user ==="
  curl -s "https://default:$PASSWORD@$HOST:443/" --data-binary "SHOW GRANTS FOR $user"
done
SQL_PATTERNS_SCRIPT_EOF