clickhouse-security-basics

name	clickhouse-security-basics
description	Secure ClickHouse with user management, network restrictions, TLS, and audit logging. Use when hardening a ClickHouse deployment, creating restricted users, or configuring network-level access controls. Trigger: "clickhouse security", "clickhouse user management", "secure clickhouse", "clickhouse TLS", "clickhouse access control", "clickhouse firewall".
allowed-tools	Read, Write, Grep
version	1.0.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","database","analytics","clickhouse","olap"]
compatible-with	claude-code

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)

-- 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  .  app_writer;


  ch_admin
    IDENTIFIED  sha256_password  
    SETTINGS PROFILE ;

   .  ch_admin   OPTION;