ClickHouse Security Guide: Users, Ports and Audit Logs

ClickHouse is built for speed and large analytical workloads, and a single exposed instance can hand anyone millions of rows of logs, events and embedded secrets. The 2025 DeepSeek exposure, where researchers found an unauthenticated ClickHouse database holding chat histories and API keys, showed how quickly an analytics store becomes a credential leak. This guide is for platform, data and security engineers running open source ClickHouse or ClickHouse Cloud. It covers users and authentication methods, admin privileges, service accounts, named collections and Cloud API keys, logging and detection, a hardening baseline and the misconfigurations that cause most incidents. General database controls are in the Database Security Guide; this page is specific to ClickHouse.

Key takeaways

The default user is all-powerful. ClickHouse uses the default account whenever no username is given, and it has all rights. Give it a strong password or disable it in production once a SQL admin and inter-node secrets exist.
Network exposure is the main failure. HTTP on 8123 and the native protocol on 9000 are plain text by default; ClickHouse Cloud services start with an IP allow list of "Allow from anywhere" that the vendor strongly recommends restricting.
Use strong password storage or no passwords. ClickHouse calls bcrypt_password the most secure password option and warns against plaintext_password; certificate, SSH key, LDAP or Kerberos authentication avoids shared passwords altogether.
Named collections and API keys are secrets. Named collections hold credentials for S3, Kafka, MySQL and other sources, and ClickHouse Cloud API keys (prefix 4b1d, matched by the gitleaks rule) manage whole services.
System tables are the audit log. system.session_log and system.query_log record logins and queries, but ClickHouse Cloud keeps them for up to about 30 days by default: export them to a SIEM.

How identity and access work in ClickHouse

ClickHouse has two ways to manage accounts, and both can run at once:

Configuration files: users defined in users.xml (or files in users.d/) with a password or hash, allowed networks, a settings profile and a quota.
SQL-driven access control: CREATE USER, CREATE ROLE, GRANT, row policies, settings profiles and quotas managed in SQL and stored under /var/lib/clickhouse/access. ClickHouse recommends this workflow. It is disabled by default and must be enabled for at least one admin account by setting access_management to 1 in users.xml.

Authentication methods

Method (`IDENTIFIED WITH`)	Use	Notes
`no_password`	Never in production	Anyone who can reach the port can log in as that user
`plaintext_password`	Avoid	ClickHouse notes the password is stored in a SQL text file under `/var/lib/clickhouse/access`
`sha256_password` / `sha256_hash`	Common default	Salted SHA-256; set `default_password_type` to control what `IDENTIFIED BY` uses
`double_sha1_password`	MySQL client compatibility only	Weaker hash; limit to accounts that need the MySQL interface
`bcrypt_password`	Preferred for passwords	Described by ClickHouse as the most secure password option, resilient to brute force if hashes leak
`ssl_certificate CN '...'`	Service accounts and applications	Mutual TLS; no shared secret on the client beyond its private key
`ssh_key`	Admins using `clickhouse-client`	Public keys of type ssh-rsa or ssh-ed25519
`ldap`, `kerberos`, `http`	Central identity	Delegates authentication to a directory or an external HTTP authenticator

Users can also be limited by host (HOST IP, HOST REGEXP, HOST LOCAL), given an expiry with VALID UNTIL, and in configuration files given a TOTP second factor through a time_based_one_time_password section. ClickHouse warns that allowing ::/0 in networks is insecure unless a firewall is properly configured or the server is not directly connected to the internet.

ClickHouse Cloud adds a separate control plane: organisation members sign in to the Cloud console with roles such as Admin and Developer, optionally through SAML SSO on the Enterprise plan, while database users remain ClickHouse users inside each service.

Admin roles and privileged access

The default account: it has all rights and cannot be managed through SQL-driven access control. ClickHouse's guidance is to enable access management for it only to bootstrap, create a named SQL admin, configure inter-node communication with a <secret> or cluster credentials, and then disable or restrict it.
Privilege flags: access_management (manage users and grants), named_collection_control (create and alter named collections) and show_named_collections_secrets (read stored secrets) are separate. A user can only grant what they hold, so keep the full set on one or two break-glass admins.
Role-based grants: create roles such as read_only_role with GRANT SELECT ON db1.* and grant roles to users rather than granting privileges directly. Avoid GRANT ALL outside the admin role.
Dangerous privileges: treat SYSTEM, INTROSPECTION, SOURCES (table functions such as url, s3, file) and ACCESS MANAGEMENT as privileged; table functions can read files or reach other systems from the server. Where a user genuinely needs object storage, scope the grant, for example GRANT READ ON S3('s3://bucket/path/.*') TO user, rather than granting the whole SOURCES group.
Row policies, profiles and quotas: use row policies to restrict tenants, settings profiles with readonly for analysts, and quotas to stop a stolen credential pulling the whole dataset in minutes.
Cloud console Admins: keep the Admin organisation role to named people with SSO and MFA. Once at least one SAML user holds Admin, ClickHouse advises removing users with other authentication methods to enforce SAML-only sign-in.

Integrations, API tokens and service accounts

Credential	Where it lives	Risk	Control
Database service account passwords	Application configs, BI tools, ETL jobs	Full read of every table the user can see	One account per application, certificate auth where possible, host restriction, quota
Named collections	ClickHouse config or DDL, local disk or Keeper	Credentials for S3, Kafka, MySQL, PostgreSQL and other sources	Encrypted storage, `SHOW NAMED COLLECTIONS SECRETS` only for admins
ClickHouse Cloud API keys	Terraform, CI/CD, scripts	Create, delete and reconfigure services, manage API keys and members	IP allow list on each key, least-privilege role, secrets manager, rotation
Inter-server secret and Keeper credentials	Cluster config	Impersonation between nodes and replication access	Unique per cluster, TLS on 9010 and 9281
Backups and exports credentials	Backup jobs, S3 policies	Copy of the whole dataset	Write-only backup roles, separate account

For service accounts, ClickHouse documents a pattern of granting several narrow roles and activating only the one a session needs with SET ROLE, so a compromised account is limited to the active role and logs show which role ran each query. ClickHouse Cloud allows up to 100 API keys per organisation, each limited to 10 requests in a 10-second window, and new API keys default to "Allow from anywhere" just as services do, so set an allow list when you create each key.

Leaked keys are easy to find automatically: the gitleaks rule clickhouse-cloud-api-secret-key matches 4b1d followed by 38 alphanumeric characters. Add it to pre-commit and CI scanning. See the Git Secret Scanning Guide and the Secrets Management Guide.

Logging and detection

ClickHouse logs security events to system tables rather than a separate audit file:

Source	What it records	Detection use
`system.session_log`	`LoginSuccess`, `LoginFailure`, `Logout`, user, `auth_type`, `client_address`	Brute force and credential stuffing; logins from new addresses
`system.query_log`	Every query with user, address, `initial_user`, `forwarded_for`, query text, rows and bytes read	What an attacker ran; bulk reads; access to `system.*` tables; use of `url`, `s3` or `file` table functions
Cloud console Activity log	Console actions by organisation members with IP and time; CSV export and Cloud API `activity` endpoint	New API keys, IP allow list changes, member and role changes
Server logs and OS	`clickhouse-server.log`, firewall and host logs	Configuration reloads, crashes, scanning of ports 8123 and 9000

ClickHouse Cloud retains system table data for up to 30 days by default, varying with merges. Export session and query logs to a SIEM continuously. Useful rules:

More than ten LoginFailure events for one user or address in five minutes.
A successful login for a service account from an address outside its allowed range.
Any CREATE USER, GRANT, ALTER USER or named collection change outside a change window.
Queries reading far more rows or bytes than the user's 30-day baseline, or SELECT against log tables that often contain secrets.
Cloud console events changing an IP allow list to "Anywhere" or creating API keys.

Metrics

Instances or services reachable from the internet without an allow list (target zero).
Users with no_password, plaintext_password or double_sha1 outside approved exceptions (target zero).
Share of service accounts using certificates or bcrypt with a host restriction.
Days of session and query log history held in the SIEM.
Leaked ClickHouse API keys found by scanning, and median time to revoke.

Hardening baseline

Network first: bind ClickHouse to private interfaces, firewall 8123, 9000, 9004, 9005, 9009 and 9100, and in ClickHouse Cloud replace "Allow from anywhere" with specific ranges or use private networking with DenyAll for public traffic.
Encrypt in transit: enable https_port 8443, tcp_port_secure 9440 and interserver_https_port 9010, disable the plain ports, and disable old protocols (disableProtocols sslv2, sslv3 in the ClickHouse TLS guide).
Accounts: enable SQL-driven access control for one admin, create named admins, then disable or lock down default. Set default_password_type and use bcrypt or certificates.
Least privilege: roles per function, row policies for tenants, read-only profiles for analysts, quotas on every external-facing account, and restrictions on table functions.
Secrets: move integration credentials into named collections with encrypted storage, restrict secret visibility, and keep Cloud API keys in a secrets manager with IP allow lists.
Data hygiene: do not ingest raw application logs that contain tokens; redact at the collector.
Logging: confirm session_log and query_log are enabled, set TTLs, and stream to the SIEM.
Test and roll back: apply changes to a staging cluster, confirm BI tools and pipelines still connect over TLS, keep the previous users.d and config.d files for rollback, and switch off plain ports last.

Decision guidance: self-managed or ClickHouse Cloud

Concern	Self-managed open source	ClickHouse Cloud
Network exposure	You own firewalls, bind addresses and every port in the table above	Only 8443 and 9440 are exposed; you own the IP allow list and private networking choice
Admin identity	Local users, LDAP or Kerberos; you build MFA around SSH and bastions	Console roles with SAML SSO on Enterprise, plus database users inside each service
Machine credentials	Database users, named collections, inter-server secrets	Database users, named collections, plus Cloud API keys with their own allow lists
Audit evidence	System tables and server logs; retention set by your TTLs	System tables (about 30 days by default) plus console Activity log and API

A small team with one analytics workload usually gets a safer result from ClickHouse Cloud with an allow list and SSO than from a self-managed node. Large or regulated estates that self-manage should run ClickHouse in private subnets only, front it with an application or query gateway, and manage users through LDAP or Kerberos so leavers lose access centrally.

Framework mapping

Framework	Reference	ClickHouse control
NIST CSF 2.0	PR.AA-05, PR.DS-01, PR.DS-02, PR.IR-01, DE.CM-03	Least privilege, data at rest and in transit, network protection, activity monitoring
NIST SP 800-53 Rev. 5	AC-3, AC-6, IA-2, IA-5, SC-7, SC-8, SC-28, AU-2, AU-11	Access enforcement, least privilege, authentication, boundary protection, encryption, logging and retention
CIS Controls v8.1	Controls 3, 5, 6 and 8	Data protection, account and access management, audit logs
MITRE ATT&CK v20	T1190, T1078, T1110, T1552.001, T1528, T1213	Exposed service exploitation, valid accounts, brute force, credentials in files and logs, stolen tokens, data from information repositories

Common misconfigurations

Public ports with no authentication: the DeepSeek database exposure involved a ClickHouse instance reachable on ports 8123 and 9000 with no login, where a log_stream table held more than a million log lines including API keys and anyone could run SQL queries.
Leaving Cloud services and API keys open to anywhere: both default to "Allow from anywhere". Teams forget to tighten them after a proof of concept.
Shared application password on the default user: every tool connects as default, so there is no attribution and no least privilege.
Secrets in analytical data: application and agent logs shipped into ClickHouse carry bearer tokens and keys, turning a read-only analyst role into credential access.
Unrestricted table functions: users with url, s3 or file access can pull or push data from the server's network position.
Short log retention: session and query logs age out before an incident is noticed.

Practitioner checklist

Confirm no ClickHouse port is reachable from the internet, and restrict every Cloud service and API key with an IP allow list.
Enable TLS on 8443, 9440 and 9010 and disable plain HTTP and native ports.
Disable or lock down the default user after creating named admins.
Remove no_password and plaintext_password users; prefer bcrypt, certificates or LDAP.
Give each application its own user with roles, host restrictions and quotas.
Store integration credentials in encrypted named collections and limit secret visibility.
Scan code and CI for the 4b1d Cloud API key pattern and rotate any hit.
Stream session_log, query_log and console activity to the SIEM.
Redact tokens from logs before they are ingested.
Review grants, row policies and API keys every quarter.

Standards and references

Related NHI Mgmt Group resources: Database Security Guide · Vector Database Security Guide · Secrets Management Guide · Git Secret Scanning Guide

ClickHouse Security Guide: Users, Network Exposure, Secrets and Audit Logs