Clickhouse security basics

ClickHouse Security Basics

Overview

Secure a ClickHouse deployment with SQL-based user management, network restrictions, TLS encryption, and query audit logging. This skill walks the seven core hardening steps at a high level; the full copy-pasteable SQL, XML, and connection code lives in references/implementation.md.

Prerequisites

• ClickHouse admin access
• CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 for SQL-based user management
• For self-hosted: access to server config files (config.xml, users.xml)

Instructions

Work through the seven steps in order. Each summary below gives the essential first move; drill into references/implementation.md for the complete, copy-ready code for every step.

Step 1: Create restricted users (SQL-based RBAC)

Create least-privilege users and REVOKE destructive verbs from application users.

CREATE USER analyst
    IDENTIFIED WITH sha256_password BY 'strong-password-here'
    DEFAULT DATABASE analytics
    SETTINGS readonly = 1, max_execution_time = 60;
GRANT SELECT ON analytics.* TO analyst;

Step 2: Use roles for permission groups

Define data_reader / data_writer / schema_admin roles once, then grant roles to users instead of hand-managing per-user grants. Verify with SHOW GRANTS.

Step 3: Row-level security

Isolate multi-tenant data with CREATE ROW POLICY, mapping each user to a tenant via a custom setting (getSetting('custom_tenant_id')).

Step 4: Network security

Restrict connection sources — SQL HOST IP '10.0.0.0/8' (22.6+), users.xml per-user network allowlists for self-hosted, or the ClickHouse Cloud IP Access List.

Step 5: TLS configuration

Enable the HTTPS port (8443) in config.xml with a server cert, private key, and strict verification mode.

Step 6: Audit logging

Query system.query_log (on by default) to see who ran what, and filter exception_code = 516 to hunt failed logins.

Step 7: Application connection security

Connect over https://…:8443 with a minimal-privilege user (never default) and a password sourced from a secret manager — see the client snippet in references/examples.md.

Run through the Security Checklist in references/implementation.md before declaring a deployment hardened.

Output

Applying this skill produces:

• Restricted user and role definitions — least-privilege CREATE USER / CREATE ROLE / GRANT / REVOKE statements ready to run against your cluster.
• Row policies for multi-tenant isolation.
• config.xml / users.xml fragments for network allowlists and TLS.
• Audit queries against system.query_log for access review and failed-login detection.
• A completed security checklist confirming default credentials, TLS, IP allowlists, logging, and secret handling are all in place.

Error Handling

Error	Cause	Solution
Authentication failed (516)	Wrong password or user	Verify credentials
ACCESS_DENIED (497)	Missing GRANT	SHOW GRANTS FOR user to diagnose
READONLY (164)	User in readonly mode	Grant write if needed
Not enough privileges	Row policy blocking	Check SHOW ROW POLICIES

Examples

Four worked, end-to-end scenarios live in references/examples.md:

1. Stand up a least-privilege analyst — read-only BI user capped on memory and time.
2. Multi-tenant isolation with a row policy — each tenant sees only its own rows.
3. Lock the app user to the VPC and require TLS — SQL HOST IP + TLS client.
4. Audit the last hour and hunt failed logins — system.query_log queries.

Minimal first example — a read-only analyst:

CREATE USER analyst
    IDENTIFIED WITH sha256_password BY 'strong-password-here'
    DEFAULT DATABASE analytics SETTINGS readonly = 1;
GRANT SELECT ON analytics.* TO analyst;

Resources

• Access Control & Account Management
• GRANT Statement
• Row Policies
• ClickHouse Cloud Access Management

Next Steps

For production deployment, harden the wider cluster with the clickhouse-prod-checklist skill, which covers backups, replication, resource quotas, and monitoring beyond the security surface covered here.