ClickHouse DB::Exception: Access storage doesn't allow backup

The "DB::Exception: Access storage doesn't allow backup" error in ClickHouse appears when you try to back up access control entities (users, roles, quotas, row policies) from a storage backend that does not support the backup operation. The ACCESS_STORAGE_DOESNT_ALLOW_BACKUP error code means the particular access storage implementation has no mechanism for exporting its state into a backup.

Impact

The backup operation fails for access control entities, though backing up table data itself is not affected. If your backup strategy relies on capturing access control definitions alongside data, you will have an incomplete backup unless you address the underlying storage type limitation.

Common Causes

1. Access control entities are defined in users.xml or other XML configuration files, which are managed outside ClickHouse's SQL-based access storage and do not support BACKUP
2. An LDAP or external authentication backend is configured as the access storage, and ClickHouse cannot serialize its state
3. A custom or read-only access storage plugin that does not implement backup functionality
4. Attempting to use BACKUP with --include-access on a setup that has no SQL-based access management enabled

Troubleshooting and Resolution Steps

1. Identify which access storage backends are in use:

   SELECT * FROM system.user_directories;
   

   This shows all configured access control storage backends and their types.

2. If entities are defined in XML configuration files, migrate them to SQL-based access management. First, enable SQL-based access control in your server configuration:

   <access_control>
       <path>access/</path>
   </access_control>
   

3. Recreate XML-defined users and roles using SQL statements:

   CREATE USER my_user IDENTIFIED BY 'password';
   GRANT SELECT ON my_database.* TO my_user;
   

   After migrating, remove or comment out the corresponding XML definitions to avoid conflicts.

4. Once all entities are in SQL-based storage, retry the backup:

   BACKUP ALL TO Disk('backups', 'my_backup/');
   

5. For entities stored in LDAP or another external directory, back them up using that system's native backup tools. ClickHouse cannot export access definitions it does not own.

6. If you only need to back up table data and can skip access entities, run the backup without the access flag or specify only the databases and tables you need:

   BACKUP DATABASE my_database TO Disk('backups', 'my_backup/');
   

Best Practices

• Use SQL-based access management for all users, roles, and quotas if you need to include them in ClickHouse backups.
• Keep XML-based user definitions only for bootstrap or emergency access, and document them separately.
• For LDAP-managed users, maintain your LDAP directory backup independently from your ClickHouse backup process.
• Test your backup and restore workflow in a staging environment to catch access storage limitations before they affect production.
• Version-control any XML access configuration files so they can be restored manually if needed.

Frequently Asked Questions

Q: Can I back up some access entities but not others?
A: ClickHouse backs up access entities per storage backend. If you have a mix of SQL-based and XML-based entities, only the SQL-based ones will be included in a BACKUP command. XML-defined entities must be backed up separately.

Q: Will migrating from XML to SQL access storage cause downtime?
A: No. You can create SQL-based entities while XML-based ones are still active. Once you verify the SQL entities work correctly, remove the XML definitions. During the overlap period, both sets of entities are active.

Q: Does ClickHouse Cloud support backing up access entities?
A: ClickHouse Cloud uses SQL-based access management by default, so the ACCESS_STORAGE_DOESNT_ALLOW_BACKUP error is uncommon there. Consult the ClickHouse Cloud documentation for backup specifics.