Why ClickHouse access control needs its own backup plan
A team restores a damaged table from backup, reconnects its dashboards, and then discovers that the analysts' accounts, the row policies that limit each region's data, and the query quotas are gone. The data is back, but nobody can safely use it. This page covers how to keep those access entities in your recovery plan. Users, roles, row policies, quotas and settings profiles are not part of the table data path, so a data-only restore will not bring them back. They must be captured deliberately and tested alongside your tables.
ClickHouse calls these objects access entities. They are created and stored by the server's access control system, separate from the tables they protect. The access control documentation, linked below, recommends managing them through SQL statements rather than by editing files, and it recommends restricting the default user, which starts with all permissions. That combination matters for recovery: if your users and policies live in SQL-managed storage, your backup process has to include that storage, and your restore runbook has to reapply it in the right order. Confirm in your own environment which storage mechanism your cluster uses for access entities before you assume a table backup covers them.
Settings delivered through config.d and users.d files sit outside the database entirely, so they need their own copy in configuration management or version control. Before you finalize the plan, list every account and policy in use, confirm which ones were created in SQL and which come from files, and decide who is responsible for restoring each group. Then run a restore test on a spare cluster and check that the same users can log in and that each row policy and quota still applies. The ClickHouse access control documentation is the natural next read for the SQL workflow and the default user settings.
Which capture method fits each access object
This table compares each ClickHouse access object, how it is defined, how it can be captured, and when each capture method fits. Confirm in the official backup documentation which access entities a BACKUP includes before relying on it, since that scope determines how much of this must be captured separately.
| Access object | How it is defined | How it can be captured | When it fits |
|---|---|---|---|
| SQL-managed users | CREATE USER statements stored in ClickHouse access storage | Included in BACKUP only if the access entity scope is confirmed in the docs; otherwise export with SHOW CREATE USER | Teams that manage accounts through SQL and need them rebuilt alongside data after a site loss |
| Roles and grants | CREATE ROLE and GRANT statements that attach privileges to users or other roles | Capture with the same method as users; verify grants after a test restore | Environments with shared privilege sets that must be restored in the same state as the users |
| Row policies | CREATE ROW POLICY statements that filter rows per user or role on a table | Confirm inclusion in BACKUP; otherwise export the policy definitions with SHOW CREATE ROW POLICY | Multi-tenant or regulated tables where a missing policy after restore would expose rows |
| Quotas | CREATE QUOTA statements that limit resource use per interval | Confirm inclusion in BACKUP; otherwise export with SHOW CREATE QUOTA | Shared clusters where query limits protect other tenants and must survive a rebuild |
| Settings profiles | CREATE SETTINGS PROFILE statements that bundle query and session settings | Confirm inclusion in BACKUP; otherwise export with SHOW CREATE SETTINGS PROFILE | Clusters that rely on named settings bundles for memory limits or read-only behavior |
| config.d files | XML or YAML server configuration fragments such as cluster and listener settings | Version-control the files and copy them with configuration management; a BACKUP does not capture them | Server-wide settings, remote servers and ports that are defined outside the database |
| users.d files | XML or YAML user definitions loaded from the server configuration directory | Version-control and copy the files; users defined here are not stored as SQL access entities | Static accounts, default user settings and legacy setups that predate SQL-driven access |
How to capture and restore access entities
This sequence produces a backup of SQL-managed access entities and the XML configuration outside the database, plus proof that a spare cluster reproduces the same users, roles and grants before you rely on it.
Inventory access entities and configuration files
Start by listing every user, role, row policy, quota and settings profile on the production cluster, and note which were created through SQL and which come from XML. Export the current definitions with SHOW CREATE USER, SHOW CREATE ROLE, SHOW ROW POLICIES, SHOW CREATE QUOTA and SHOW CREATE SETTINGS PROFILE, and record the grants with SHOW GRANTS for each principal. Then list the files in the server's config.d and users.d directories, including any custom overrides, and check the default user and any accounts defined in XML. The output is a dated inventory file that the owner of access management signs off on, and it becomes the baseline for every later comparison.
Choose a capture method for each entity type
Decide how SQL-managed entities will be captured. The native BACKUP statement can include access entities, so confirm in the backup documentation that your ClickHouse version includes them in the backup scope and that your restore procedure uses the same settings. XML files sit outside the database and are not captured by BACKUP, so they need a separate method, such as copying them into version control or your configuration management system. Confirm with your platform team how secrets inside those files, including password hashes, are stored and who can read the repository. The deliverable is a short written decision that names the tool and the storage target for each entity type.
Take the backup and record its contents
Run a BACKUP statement that covers the databases and access entities in scope, writing to the storage target you chose. Run it asynchronously if the backup is large, and track progress in system.backups until the status shows completion. Review the entry for the backup name, status, size and any error text. Keep the backup name and timestamp in the inventory file so the restore step can refer to a specific backup. Completion of this step means the system.backups status is successful and the backup exists in the target storage.
Export configuration files to versioned storage
Copy the config.d and users.d files from each node into a versioned repository or artifact store, keeping one snapshot per change window. Compare the files across replicas with a checksum or diff, because nodes that drifted apart will restore differently. Confirm that the export excludes nothing you need, including include files referenced from the main configuration. Store the commit or artifact identifier in the same record as the SQL backup. The check is a clean diff across all nodes, or a documented explanation for each difference.
Restore into a spare cluster
Restore the access entities into a spare cluster that is not serving production traffic. First, place the exported XML files into config.d and users.d on the spare nodes, and then run the RESTORE statement for the access entities from the backup written in step three. Restart or reload configuration as your deployment requires, and confirm the server starts with no configuration errors in its logs. The output of this step is a spare cluster that starts cleanly and reports the restored entities in its system tables.
Compare definitions and grants before and after
Rerun the SHOW CREATE and SHOW GRANTS statements from step one on the spare cluster, and diff the output against the baseline inventory. Differences in ordering are acceptable only if the definitions match. Investigate any missing role, altered grant, changed row policy or quota that differs from the source. Record the diff, the reviewer and the date. A clean comparison for every user, role, policy, quota and profile is the evidence that the restore reproduces access control.
Verify that users log in with expected grants
Test representative accounts on the spare cluster, including an account for each major role and at least one account restricted by a row policy or quota. Confirm that each account authenticates through the expected method, such as password, certificate or an external authenticator, and that it can run its permitted queries but not restricted ones. Check that the default user has been restricted as your access policy requires. Write the results as a test log with pass or fail for each account, and keep it with the backup record so an auditor can see the restore was proven.
Record the restore evidence and schedule repeats
Bundle the inventory, backup identifier, configuration commit, comparison diff and login test log into one restore record. Note any gaps, such as entities outside the backup scope or external authenticator settings that the spare cluster could not reproduce. Assign an owner to repeat the sequence on a regular schedule and after any change to access control or ClickHouse version. The deliverable is a restore record that someone other than the author can follow end to end.
Protecting backed-up access data
Backups of users, roles and settings carry the same sensitivity as the access model they describe. The cards below cover where that data sits, what it contains and who can act on it, so the restore path does not become a way around the controls you designed.
Backup storage exposure
A backup that includes access entities also includes the definitions of who may query what. Treat the destination, whether local disk, S3-compatible storage or Azure Blob Storage, as a privileged store. Restrict bucket or container access to the backup service identity and a small operator group, enable encryption in transit and at rest, and confirm who can list or download objects. Check that read access to backup storage is not broader than read access to the live cluster.
Credentials inside configuration files
Files under config.d and users.d sit outside the database and are not captured by SQL-driven backups. They often hold passwords, certificate paths, LDAP bind details or external authenticator settings. Decide which of these files you back up, where the copies live, and who can read them. Keep secrets out of plain XML where your secrets platform can supply them, and check file permissions on every node, not only the first one.
Password hashes in backups
Users created with SQL carry password hashes, and a backup of the access entities copies them. A leaked hash can be attacked offline, so the hash is sensitive even though the plain password is not stored. Confirm which authentication method each account uses, retire accounts that rely on shared static passwords, and include backup contents in your data classification and encryption decisions.
Restricting the default user
ClickHouse's default user starts with all permissions. Before you back up or restore access entities, confirm that the default account is disabled or restricted to a narrow role, and that remaining administrative accounts are named individuals or service identities. A restore that recreates an unrestricted default account can reopen access you already closed, so verify the account state after every restore test.
Who can run RESTORE
RESTORE can recreate users, roles, row policies, quotas and settings profiles, which makes it an access-changing operation as well as a data operation. Keep the right to run RESTORE in a dedicated role that daily administrators do not hold. Require a second person or a change record before a restore that touches access entities, and log who ran it and when.
Restore tests with access checks
A restore test that only counts rows does not prove that the access model came back intact. For each test, compare the restored users, roles, row policies and quotas with the recorded definitions, and confirm that a restored least-privilege account can see only the rows it should. Keep the comparison output as evidence so a restore that silently drops a row policy is caught before an incident needs it.
Restoring access entities before or after data
Restore order decides whether access entities come back usable. Row policies, grants and quotas refer to databases, tables and other access entities by name. If a backup is restored into a cluster where those objects do not yet exist, the dependent statements can fail or attach to the wrong target, and a user may appear restored while still lacking the grants they need. Before a restore test, decide whether access entities are restored first, alongside the data, or after the tables they reference. Then confirm the behavior in the ClickHouse access control documentation and the backup documentation for the exact ClickHouse version you run, since restore handling of access entities and the settings that control it can change between releases.
A restored cluster needs an access check, not just a row count. Users, roles and row policies can all be present and still block the people who depend on them. A row policy that filters on a column that was renamed, a role that was granted on a table that was dropped and recreated, or a default user that was restricted before the backup can each leave an account unable to read data it should reach. Run queries as each representative account, including service accounts used by dashboards and ingestion, and compare the results with what the same accounts saw on the source cluster. Record which accounts were tested and what they could read, because that record is part of the restore evidence described in the restore testing section.
Plan the check into the restore runbook with named owners. Ask the team that will perform a restore to list every account, role and policy class that must work after recovery, and to state whether the access layer is restored before or after the data for each path. Confirm with your ClickHouse version whether RESTORE can include access entities and which options control overwrite behavior, because an overwrite can silently replace a live access configuration on a shared cluster. Keep the config.d and users.d files under the same change control as the backup, so the file-based configuration that backs up separately can be matched to the SQL-managed entities at restore time.
Limits to confirm in your environment
A ClickHouse backup of access control is only as complete as the version, configuration and authentication setup behind it. The cards below list the items to verify in your own environment before you treat a backup as proof that users, roles and settings can be recovered.
Which access entity types BACKUP covers
Confirm, against the version you run, which access entities a BACKUP statement includes and which it leaves out. Check users, roles, row policies, quotas and settings profiles individually, and do not assume that a backup of tables or databases carries them. Run a test backup into a scratch location, then inspect what it contains. Record the result per version, because coverage can change between releases.
External authenticator configuration
Users authenticated through LDAP, Kerberos, SSL X.509 certificates, an HTTP authenticator or JWT depend on settings that a SQL backup may not capture. Identify which external authenticators your users rely on, then capture the matching server configuration, directory bindings and certificate trust material separately. Confirm how each authenticator is defined in your deployment before you plan a restore.
Files outside the database
The config.d and users.d directories hold server settings and user definitions that live outside the database. Inventory every file in those directories, including overrides added by configuration management, and decide where a copy is stored and who can change it. Keep those files under version control or a configuration backup so a rebuilt node matches the access model that existed before the incident.
Release line support status
Check the ClickHouse security policy to confirm that your exact release line still receives security updates before you finalize a recovery plan. A backup procedure written for an unsupported line may still work, but unpatched access control defects will not be fixed. Schedule upgrades so that the version you restore into is one that the project currently supports.
Default user and restore order
Restoring access entities into a server where the default user still holds full permissions can hide gaps in your restore procedure. Confirm which accounts exist after a restore, whether the default user is restricted, and in what order roles, row policies and users must be recreated so that grants resolve. Write the order down and rehearse it on a spare cluster.
Questions teams ask before backing up ClickHouse access control
Is a normal data BACKUP enough to protect ClickHouse users and roles?
No. A standard BACKUP of tables or databases does not automatically cover SQL-managed access entities such as users, roles, row policies, quotas and settings profiles, so you must check what your backup command includes and test it. Separately, the config.d and users.d files on each server sit outside the database and need their own copy. Confirm the exact scope of your backup method against the ClickHouse backup documentation before assuming access control is covered.
What actually drives the effort of backing up access control?
Three factors matter most. The first is the number of users and roles, since each one needs to be inventoried and verified after a restore. The second is the number of row policies and quotas, because these are tied to tables and workloads and are easy to miss. The third is configuration drift: when some nodes have hand-edited users.d files and others do not, the backup captures an incomplete picture. Estates managed through SQL with consistent file templates are usually simpler to protect than estates that have grown by manual edits over several years.
What does your team need to provide before work starts?
Expect to supply a list of the access model in use, including the users, roles, row policies and external authenticators such as LDAP, Kerberos or JWT, if any are configured. You should also provide access to each cluster node's configuration directories, the backup storage target and its credentials handling policy, and a spare environment where restores can be tested. Confirm with your security team who owns the default user, since the access control documentation recommends restricting it.
Can our own team implement this without a partner?
Yes, if you have engineers who know ClickHouse access control and can run restore tests. The work is mostly procedural: define what must be captured, script or schedule the exports and copies, and prove a restore on a spare cluster. The harder parts are making sure nothing is overlooked across nodes and keeping the evidence current as users change. A partner is worth considering when your team lacks time for recurring restore tests or needs an independent review of the scope before an assessment.
How long does a restore test take for a cluster of our size?
No general duration can be stated reliably, because it depends on the number of nodes, the volume of data in the restored tables, the speed of your backup storage and how many access entities must be checked by hand. A small single-node estate with a few users can be tested quickly, while a multi-shard cluster with many row policies needs longer. Ask any provider to base the estimate on a sample restore of your own data on a spare cluster, and to record the time for each step.
What should a partner quote include for this work?
A written quote should list the systems in scope, including each cluster, node and config directory, and the access entities to capture, such as users, roles, row policies, quotas and settings profiles. It should state who runs the restore tests, on what spare environment, how evidence is recorded, and what is handed over at the end. Ask whether the price covers one-time setup, recurring test cycles, or both, and whether a retainer is needed for ongoing testing. DeployOpen works from a written scope and does not publish a price list, so the quote follows from that scope.
Does a restore of access control need to match the original configuration exactly?
Yes, for the restored system to behave as the original did. The restored users, roles and policies should produce the same permissions, and the config.d and users.d settings should match what was in production at the backup point. Verify this by comparing lists of entities and effective grants after the restore, not by assuming the restore completed successfully. Record the comparison so auditors can see what was checked.
Recording evidence that access restores work
A backup of users, roles and settings is only trustworthy once a restore has been run and the result written down. The ClickHouse backup documentation makes the core point: a backup that has never been test-restored will probably not restore properly when you need it. For access entities, the restore test should produce evidence you can show an auditor and use to catch drift, so keep the record for every backup run, not only the first successful one.
Build the evidence from artifacts that can be compared. Export the grant lists from the restored cluster with SHOW GRANTS for each role and user, and save the output of SHOW ROW POLICIES, SHOW QUOTAS and SHOW SETTINGS PROFILES alongside it. Diff those exports against the same queries run on the source cluster at backup time. Also keep copies of the config.d and users.d files from the restored host and diff them against the version in your configuration repository. Any difference is either an intended change that should be recorded or a gap that the backup missed. Ask your team to confirm which of these files your deployment actually uses before you write the diff step.
Login checks prove the restored access model behaves as designed, not only that the rows exist. For each role, sign in as a representative user on the spare cluster and confirm that allowed queries succeed, that row policies filter the rows they should, and that a denied action is refused. Also confirm that the restored default user is restricted as your security baseline requires. Store each run with its date, the backup identifier, the ClickHouse version, the person who ran it and the diff results. A dated record tied to a specific backup run lets you show which backup was proven, and it tells you when the proof is out of date.
What DeployOpen does with your ClickHouse access backups
DeployOpen plans and implements the backup of ClickHouse access entities and server configuration inside a fixed scope that is agreed in writing before work starts. In practice that covers the SQL-managed users, roles, row policies, quotas and settings profiles, which are captured through a backup of the access entities, and the files under config.d and users.d that sit outside the database. The written scope names which clusters, databases and access objects are included, where the backups are written (local disk, S3-compatible storage or Azure Blob Storage), how credentials for the backup target are stored, and who holds the restore keys. Each item in that scope becomes a deliverable you can check against the statement of work.
The part most often missed is the restore test. DeployOpen sets up a restore of the access backup on a spare cluster, replays the SQL and file contents, and checks that the users can authenticate, the roles grant what they should, and the row policies and quotas apply again. The result is written up as evidence you can keep for audit purposes. Confirm with your team whether the spare cluster must mirror your production authentication setup, including any LDAP, Kerberos or JWT external authenticators, because a restore that succeeds only against local users proves less than it appears to.
Ongoing work is handled separately. DeployOpen offers retainer support for scheduled restore tests, backup job checks and changes to access objects after the initial build, and the retainer terms are agreed in the same written form as a fixed-scope engagement. Ask for the exact list of recurring activities, the frequency each one runs at, and how a failed restore test is reported and escalated before you sign. Quotes follow from the written scope, so describe your clusters, authentication sources and retention needs in enough detail for the scope to be specific.
How DeployOpen scopes and delivers this work
DeployOpen treats access control and file-based configuration as part of the ClickHouse recovery design, not an afterthought to table backups. We inventory what lives in SQL and what lives in config.d and users.d, then build and test a repeatable way to capture and restore both in infrastructure you control.
How we scope
- Inventory SQL-managed users, roles, row policies, quotas and settings profiles across every cluster in scope
- Map each config.d and users.d file to its source repository, owner and change process
- Agree the written scope, deliverables and timeline before work starts, since engagements are fixed-scope
How we build
- Include access entities in the BACKUP scope and confirm which objects a restore recreates
- Version-control the external configuration files and store copies alongside the database backups
- Test restores on a spare cluster, checking that roles grant the same privileges and row policies still apply
How we hand over
- Document the restore order: access entities, schema, data, then configuration files, with the reasons for each step
- Provide runbooks for rebuilding the default user restriction and external authenticator settings after a site loss
- Deliver restore test records that your auditors and operators can reproduce on their own schedule
How we support
- Review backup coverage again after each ClickHouse upgrade, since release changes can affect access control behavior
- Run scheduled restore tests on spare infrastructure and report gaps in access entity or configuration recovery
- Ongoing support, upgrades and continuous testing are available as a retainer after handover
