Skip to content
Skills Library
Security · Review & audit

clickhouse-security-basics

/clickhouse-security-basics

Secure ClickHouse with user management, network restrictions, TLS, and audit logging. Use when hardening a ClickHouse deployment, creating restricted users, enforcing multi-tenant row isolation, or configuring network-level access controls. Trigger with "clickhouse security", "clickhouse user management", "secure clickhouse", "clickhouse TLS", "clickhouse access control", "clickhouse firewall".

Licence
MIT
Last repository change
26.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
3
Scripts
0
HTML comments
0
URLs in SKILL.md
5
Deprecation

No signals detected. Repository changed on 26.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Secure ClickHouse with user management, network restrictions, TLS, and audit logging. Use when hardening a ClickHouse deployment, creating restricted users, enforcing multi-tenant row isolation, or configuring network-level access controls. Trigger with "clickhouse security", "clickhouse user management", "secure clickhouse", "clickhouse TLS", "clickhouse access control", "clickhouse firewall".

Triggers from the description

clickhouse securityclickhouse user managementsecure clickhouseclickhouse TLSclickhouse access controlclickhouse firewall
Original author description

Secure ClickHouse with user management, network restrictions, TLS, and audit logging. Use when hardening a ClickHouse deployment, creating restricted users, enforcing multi-tenant row isolation, or configuring network-level access controls. Trigger with "clickhouse security", "clickhouse user management", "secure clickhouse", "clickhouse TLS", "clickhouse access control", "clickhouse firewall".

How it works

  1. Overview
  2. Prerequisites
  3. Instructions
  4. Output
  5. Error Handling
  6. Examples
  7. Resources
  8. Next Steps
Original SKILL.md headings · 121 lines, 595 words
SKILL.md preview
---
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, enforcing multi-tenant row isolation, or configuring network-level
  access controls. Trigger with "clickhouse security", "clickhouse user
  management", "secure clickhouse", "clickhouse TLS", "clickhouse access
  control", "clickhouse firewall".
allowed-tools: Read
version: 1.7.0
license: MIT
author: Jeremy Longshore <jeremy@intentsolutions.io>
tags:
- saas
- database
- analytics
- clickhouse
- olap
compatibility: Designed for Claude Code
---
# 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](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](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.

```sql
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](references/examples.md).

Run through the Security Checklist in
[references/implementation.md](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](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:

```sql
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](https://clickhouse.com/docs/operations/access-rights)
- [GRANT Statement](https://clickhouse.com/docs/sql-reference/statements/grant)
- [Row Policies](https://clickhouse.com/docs/knowledgebase/row-column-policy)
- [ClickHouse Cloud Access Management](https://clickhouse.com/docs/cloud/security/cloud-access-management/overview)

## 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.
Files in this package (3)
  • SKILL.md 5 KB
  • references/examples.md 2 KB
  • references/implementation.md 5 KB

Versions

The database has no other skills named clickhouse-security-basics.

Identical content appears in 1 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version 3d23a22c1e43 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100clickhouse-enterprise-rbac
    Configure ClickHouse enterprise RBAC — SQL-based users, roles, row policies, column-level grants, and quota management. Use when setting up multi-user access control, implementing tenant isolation, or configuring enterprise security for ClickHouse. Trigger with "clickhouse…
  • 100coreweave-security-basics
    Secure CoreWeave deployments with RBAC, network policies, and secrets management. Use when hardening GPU workloads, managing model access, or configuring namespace isolation. Trigger with phrases like "coreweave security", "coreweave rbac", "secure coreweave", "coreweave secrets".
  • 90network-security
    Firewall rules, VPC design, TLS config, and subnet isolation. Use when: network security.
  • 100clickhouse-pr-description
    Generate PR descriptions for ClickHouse/ClickHouse that match maintainer expectations. Use when creating or updating PR descriptions.
  • 100clickhouse-migrations
    ClickHouse migration patterns and rules. Use when creating or modifying ClickHouse migrations.
  • 90clickhouse-analytics
    Answer data questions against ClickHouse through the ch-ui MCP server. Use when the user asks about data, tables, metrics, dashboards or query performance in ClickHouse, or when ch-ui MCP tools are available.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.