clickhouse-security-basics

# ClickHouse Security Basics ## Overview Secure a ClickHouse deployment with SQL-based user management, network restrictions, TLS encryption, and query audit logging. ## Prerequisites - ClickHouse admin access - `CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1` for SQL-based user management - For self-hosted: access to server config files ## Instructions ### Step 1: Create Restricted Users (SQL-Based RBAC) ```sql -- Create a read-only analyst user CREATE USER analyst IDENTIFIED WITH sha256_password BY 'strong-password-here' DEFAULT DATABASE analytics SETTINGS readonly = 1, -- Read-only mode max_memory_usage = 5000000000, -- 5GB per query max_execution_time = 60; -- 60s timeout GRANT SELECT ON analytics.* TO analyst; -- Create an application user with insert permissions CREATE USER app_writer IDENTIFIED WITH sha256_password BY 'another-strong-password' DEFAULT DATABASE analytics; GRANT SELECT, INSERT ON analytics.* TO app_writer; -- Explicitly deny destructive operations REVOKE DROP, ALTER, CREATE ON *.* FROM app_writer; -- Create an admin user CREATE USER ch_admin IDENTIFIED WITH sha256_password BY 'admin-password' SETTINGS PROFILE 'default'; GRANT ALL ON *.* TO ch_admin WITH GRANT OPTION; ``` ### Step 2: Use Roles for Permission Groups ```sql -- Create reusable roles CREATE ROLE data_reader; GRANT SELECT ON analytics.* TO data_reader; CREATE ROLE data_writer; GRANT SELECT, INSERT ON analytics.* TO ...