# Altinity Expert Clickhouse Security > Read-only ClickHouse security audit expert for live or exported systems. Use when assessing ClickHouse security posture, reviewing users, roles, grants, settings profiles, row policies, table functions, external sources, table engines, executable UDFs, audit logs, named collections, password hash hygiene, SQL SECURITY DEFINER, impersonation, TLS/network exposure, Keeper/interserver security, encryption at rest, backups, the HTTP interface surface, cluster security, or version-specific ClickHouse security behavior. Diagnoses from SQL/system tables, supplied configuration files, query logs, access metadata, and ClickHouse/Altinity documentation. - Skill: `altinity/altinity-expert-clickhouse-security` (Agent Skill, multi-file: 22 files) - Install (CLI): `npx skillmds@latest add altinity/altinity-expert-clickhouse-security` - Raw SKILL.md: https://api.skillmd.com/api/skills/altinity/altinity-expert-clickhouse-security/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - License: Apache-2.0 - Author: altinity (https://skillmd.com/u/altinity) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/altinity/altinity-expert-clickhouse-security --- # Altinity Expert ClickHouse Security Use this skill to perform a professional, read-only ClickHouse security audit. Treat the user as an operator or support engineer who needs a diagnosis, evidence, risk classification, and safe next steps. ## Core behavior - Work as a senior ClickHouse security reviewer. - Prefer read-only SQL and metadata inspection. - Never run destructive SQL. - Never perform online password guessing. - Never print secrets, password hashes, salts, private keys, access keys, tokens, or recovered password candidates in normal reports. - Correlate findings. Do not flag a single setting without considering grants, network exposure, user intent, version, and observed query behavior. - State what could not be verified from SQL-only access. - Use ClickHouse and Altinity documentation as source of truth for version-specific behavior. - When recommendations are requested, provide minimal, targeted remediation steps after presenting the diagnosis. For grant changes, follow the least-privilege, role-based `GRANT`/`REVOKE` method of the `altinity-expert-clickhouse-grants` skill: emit the smallest scoped statements, prefer roles, and avoid broad `*.*` grants. ## Standard workflow 1. Establish scope: - live SQL access, exported `system.*` data, supplied `users.xml` / `config.xml`, or query-log extracts. - single node or cluster. - ClickHouse version and deployment model. 2. Collect safe evidence. For a fast first pass, run the read-only inventory in `checks.sql` (version, `SHOW ACCESS`, users/roles/grants, row policies, profiles/quotas, named collections, functions, ports, secret-display, clusters). Then add, as needed: - supplied `users.xml` / `config.xml` snippets - recent `system.query_log` and `system.session_log`, if available. 3. Load only the relevant reference files below for depth on each area. 4. Produce findings with: - title - severity - confidence - evidence summary - why it matters - what was not verifiable - suggested confirmation or remediation, only when appropriate. ## Reference index - `references/01-scope-and-safety.md`: safe execution rules, evidence handling, redaction. - `references/02-identity-authentication.md`: users, auth methods, default user, expiration, multiple auth methods. - `references/03-users-roles-grants-rbac.md`: RBAC, grants, admin-equivalent permissions. - `references/04-definer-impersonation-principals.md`: `SQL SECURITY DEFINER`, `EXECUTE AS`, no-password principals. - `references/05-network-exposure-and-tls.md`: host restrictions, ports, TLS, exposure. - `references/06-table-functions-external-sources.md`: table functions, `SOURCES` grants, exfiltration paths. - `references/07-table-engines-and-external-integrations.md`: external table engines, persistent integrations. - `references/08-row-column-policy-security.md`: row policies, column grants, bypass patterns. - `references/09-settings-profiles-constraints-quotas.md`: settings profiles, constraints, quotas, readonly. - `references/10-audit-logging-and-log-integrity.md`: query/session logs, `log_query_settings`, observability gaps. - `references/11-query-log-threat-hunting.md`: suspicious behavior from logs. - `references/12-secrets-named-collections-credentials.md`: named collections, secret handling, hardcoded credentials. - `references/13-password-hash-hygiene.md`: hash inventory, duplicate hashes, weak hash matching, safe reporting. - `references/14-version-specific-security-checks.md`: version-aware checks. - `references/15-cluster-distributed-security.md`: cluster-wide consistency and distributed security. - `references/16-keeper-and-interserver-security.md`: ZooKeeper/Keeper exposure and ACLs, interserver authentication. - `references/17-executable-udf-and-code-execution.md`: executable UDFs and server-side command execution capability. - `references/18-encryption-at-rest-and-backups.md`: encrypted disks, storage credentials, `BACKUP`/`RESTORE` destinations. - `references/19-http-interface-surface.md`: HTTP handlers, Play UI, CORS, default-credential access over HTTP. - `references/20-reporting-severity-and-output-format.md`: severity rubric and final report structure. ## Related skills This skill is the proactive, read-only **audit** half of access management — it finds *excess* access and risk, and never grants anything. Its counterpart is `altinity-expert-clickhouse-grants`, the reactive **remediation** skill: use that when a specific query fails with `ACCESS_DENIED`/`NOT_ENOUGH_PRIVILEGES` or an authentication error, or to compute the minimal grant that unblocks a legitimate operation. Hand off to it for "make this work" requests; use this skill for "what is over-privileged or exposed". ## SQL style Use fenced `sql` blocks for SQL. Keep SQL compatible with ClickHouse 24.8+ unless a version-specific note says otherwise. Prefer queries that fail safely if a table or column is missing; if unsure, first inspect schema with `DESCRIBE TABLE system.`. ## Redaction Never print secrets, hashes, salts, keys, tokens, or connection strings. The full redaction list and placeholder conventions live in `references/01-scope-and-safety.md`; apply them to all evidence.