How to Configure ClickHouse Replication User
You set up a clickhouse replication user by turning on SQL access control first. Then you create a dedicated account with host limits and specific table grants. Most teams start by sharing a single admin account across all cluster nodes. This wide access creates a big risk when an engineer or script logs in. Overly broad roles let one bad query drop tables or read private columns. How to set up a clickhouse replication user ClickHouse manages users, roles, row policies, settings profiles, and
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You set up a clickhouse replication user by turning on SQL access control first. Then you create a dedicated account with host limits and specific table grants. Most teams start by sharing a single admin account across all cluster nodes. This wide access creates a big risk when an engineer or script logs in. Overly broad roles let one bad query drop tables or read private columns.
How to set up a clickhouse replication user
ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. Before you make a clickhouse replication user, you must enable SQL access control. This setup needs a storage directory set in the server access control path parameter. You also need at least one base account with access management turned on. That base account must have named collection control set to one as well.
Once SQL access control is active, an admin runs the create user statement. Running this statement requires the create user privilege on the server. ClickHouse enforces a strict rule when you hand out database rights. A user can only grant the privileges that they already hold themselves. During creation, you define how the account logs in and which hosts can connect. You can also assign default roles and apply per-user settings profiles right away.
For data sync and read tasks, you should lock down table and column grants. ClickHouse lets you grant select rights on specific columns instead of whole tables. If an account only has rights to two columns, it can query just those. The server refuses full table select queries with an asterisk on that table. It returns no data at all unless the table holds only those granted columns. Since version 24.10, a trailing asterisk lets you grant rights by table name prefix.
Why native roles leave a gap during live queries
Native SQL access control does a good job of deciding who can log in. Host limits and column grants stop basic network mistakes between your cluster nodes. They also keep sync jobs from reading tables outside their assigned prefix.
Still, static grants cannot inspect what individual statements do once a session starts. A valid account with read rights can still pull millions of rows at once. When a human engineer uses a shared role to fix a sync bug, risks grow. Native grants do not mask emails, phone numbers, card numbers, or national IDs in results. Every person using that role sees raw values if they have column select rights.
User Provisioning (SCIM) + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Closing the gap for people and automated jobs
To keep data safe, teams must separate machine sync roles from human debug access. Automated cluster tasks should use host-locked accounts that only touch required table prefixes. Human engineers should not log in with those same static passwords to check data.
Instead, teams need a way to check each query in flight before it runs. Adding statement-level checks lets you require peer approval for risky commands. Dynamic data masking can also rewrite matched fields like emails or card numbers in results. This setup keeps cluster sync running fast while protecting live data during manual checks.
Check ClickHouse queries and result columns on the wire
ClickHouse users and row policies decide who connects. They do not check each statement on the wire or redact sensitive string columns in query results. With hoop.dev, you run an open-source proxy, a sidecar for your ClickHouse, inline in ClickHouse's native TCP protocol. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.
Multi-statement queries and table mutations are split and checked by hoop.dev guardrails, which refuse banned actions with a native ClickHouse error or hold risky ones for approval. On returned rows, hoop.dev data masking rewrites String and FixedString columns in memory, and the audit trail logs each query and verdict. See the MIT-licensed source on GitHub.
Frequently asked questions
What server settings do I need before making SQL users in ClickHouse?
You must configure a storage directory using the server access control path parameter. You also need a seed account with access management and named collection control enabled.
Why does a select asterisk query fail for a restricted ClickHouse account?
When you grant select on specific columns only, ClickHouse blocks full wildcard queries. The query returns no data unless the table contains only the exact granted columns.
Can a ClickHouse account grant permissions to another account?
Yes, if the account holds the right privileges. Still, a user can only grant the privileges that they already have themselves.
Related guides
In this post