Replicate ClickHouse RBAC Users and Grants via Keeper

By default, every ClickHouse node stores SQL-managed RBAC objects locally under access/. Create a user on node A and node B will not know about it. The fix is the replicated access storage backend, which writes users, roles, grants, row policies, quotas, and settings profiles into Keeper or ZooKeeper. Other replicas watch the Keeper path and update their local cache instantly. The result is a single source of truth for RBAC across the cluster, propagated through the same coordination layer that already runs ReplicatedMergeTree.

Configuration

Apply the same user_directories block to every node:

<clickhouse>
  <user_directories replace="replace">
    <users_xml>
      <path>/etc/clickhouse-server/users.xml</path>
    </users_xml>
    <replicated>
      <zookeeper_path>/clickhouse/access/</zookeeper_path>
    </replicated>
  </user_directories>
</clickhouse>

What this does:

• replace="replace" discards the default user_directories so every node uses exactly this list. Without it, the merged config may include the local disk directory and you end up with split RBAC.
• <users_xml> keeps file-defined users (typically the bootstrap default user) usable.
• <replicated> registers Keeper as the storage backend for SQL-created entities at /clickhouse/access/.

Restart ClickHouse on each node after the change.

Verification

Check that the replicated storage is active:

SELECT name, type, params, precedence
FROM system.user_directories
ORDER BY precedence;

You should see users_xml and replicated entries. Then confirm where existing entities live:

SELECT name, storage
FROM system.users
ORDER BY name;

The storage column shows replicated for objects stored in Keeper and users.xml for file-based ones.

Smoke test propagation by creating a user on one node and querying it from another:

-- on node A
CREATE USER alice IDENTIFIED BY 'secret';

-- on node B
SELECT name, storage FROM system.users WHERE name = 'alice';

The user should appear on node B within milliseconds.

ON CLUSTER conflicts

Once RBAC is replicated, ... ON CLUSTER statements become redundant: the change propagates via Keeper regardless. ClickHouse handles this with a compatibility setting that silently ignores the ON CLUSTER clause for replicated access entity queries:

SET ignore_on_cluster_for_replicated_access_entities_queries = 1;

This is enabled by default, so existing scripts that issue CREATE USER ... ON CLUSTER keep working after the switch. Cleaner long-term is to remove the ON CLUSTER clause from RBAC DDL.

Migration paths

To switch an existing cluster from local to replicated storage, capture the current RBAC, change the config, and replay it on one node.

SQL dump and replay:

-- on a node with the current state
SHOW ACCESS;

Capture the output as a script, deploy the new user_directories config, restart, and replay the script on one node (without ON CLUSTER). Replicas pick it up automatically.

clickhouse-backup with RBAC scope:

clickhouse-backup create --rbac-only users_bkp_20260304
clickhouse-backup restore --rbac-only users_bkp_20260304

Embedded BACKUP/RESTORE:

BACKUP TABLE system.users, TABLE system.roles, TABLE system.row_policies,
       TABLE system.quotas, TABLE system.settings_profiles, TABLE system.functions
TO Disk('backups', 'rbac_snapshot_20260304');

RESTORE TABLE system.users, TABLE system.roles, TABLE system.row_policies,
        TABLE system.quotas, TABLE system.settings_profiles, TABLE system.functions
FROM Disk('backups', 'rbac_snapshot_20260304');

Pick whichever fits your existing backup tooling.

Keeper path structure

Under the configured zookeeper_path, ClickHouse organizes entities as:

/clickhouse/access
  /uuid/<entity_uuid>     -> serialized entity payload
  /U/<escaped_name>       -> user name to UUID mapping
  /R/<escaped_name>       -> role name to UUID mapping
  /S/<escaped_name>       -> settings profile name to UUID
  /P/<escaped_name>       -> row policy name to UUID
  /Q/<escaped_name>       -> quota name to UUID

Useful for forensics, not for hand editing.

Debugging

When propagation seems broken, check Keeper connectivity first:

SELECT * FROM system.zookeeper_connection;

SELECT *
FROM system.zookeeper_connection_log
ORDER BY event_time DESC
LIMIT 100;

Force a fresh RBAC reload on a single node:

SYSTEM RELOAD USERS;

Inspect the raw Keeper contents at the configured path:

SELECT * FROM system.zookeeper WHERE path = '/clickhouse/access';

Operational notes

• RBAC writes require Keeper to be available. During a Keeper outage you cannot create or modify users, roles, or grants. Authentication continues to work because each node serves from its local cache.
• zookeeper_path cannot be changed at runtime via SQL. To move RBAC to a different path, export, edit the config, restart, and re-import.
• An invalid payload in Keeper can prevent ClickHouse from starting cleanly. Test config changes in a non-production cluster first.
• Each node keeps an in-memory cache updated through Keeper watches. Authentication and authorization checks read from this cache, not from Keeper directly, so query-time latency is unaffected.

Common Pitfalls

• Omitting replace="replace". The default disk directory survives and you end up with conflicting copies of the same user.
• Different zookeeper_path values per node. Nodes connect to different RBAC stores and nothing propagates.
• Leaving ON CLUSTER in DDL scripts after the switch. Statements fail until you set ignore_on_cluster_for_replicated_access_entities_queries = 1 or remove the clause.
• Storing the bootstrap default user in Keeper. Keep a file-based admin so you can log in if Keeper is down.
• Mixing the replicated and local_directory backends across nodes. Some nodes will not see entries created on others.

Frequently Asked Questions

Q: Does this also replicate users defined in users.xml? A: No. File-based users stay file-based. Only SQL-created entities go through Keeper.

Q: What happens to existing local RBAC when I add the replicated directory? A: Existing local entities remain accessible because users_xml still lists them, but new SQL-created objects land in Keeper. Migrate explicitly using one of the dump-and-replay paths.

Q: Can I run replicated RBAC with ZooKeeper instead of Keeper? A: Yes. The <replicated> backend works with either coordinator. Use whichever your cluster already runs.

Q: How is this different from configuring users via Ansible? A: Config management applies file changes on every node and requires a restart for new users. Replicated storage propagates SQL changes in milliseconds without restarts.

Q: Can I log in if Keeper is unavailable? A: Yes, because authentication uses the local cache. You cannot create or modify users until Keeper returns.