ClickHouse DB::Exception: Access storage for insertion not found

The "DB::Exception: Access storage for insertion not found" error in ClickHouse occurs when you try to create an access control entity (such as a user, role, or quota) via SQL but no writable access storage backend is configured. The ACCESS_STORAGE_FOR_INSERTION_NOT_FOUND error code indicates that ClickHouse has nowhere to persist the new entity.

Impact

All SQL-based CREATE statements for access entities will fail. You cannot create users, roles, quotas, settings profiles, or row policies through SQL until a writable access storage is configured. This effectively locks you out of managing access control dynamically.

Common Causes

1. SQL-based access management was never enabled in the ClickHouse server configuration
2. The access_control_path or <access_control> section is missing from the server config
3. All configured access storage backends are read-only (for example, XML-based storage)
4. The directory specified for SQL access storage does not exist or lacks write permissions
5. A misconfigured user_directories section that only lists non-writable backends

Troubleshooting and Resolution Steps

1. Check which access storage backends are currently configured:

   SELECT * FROM system.user_directories;
   

   Look for a storage of type local_directory or replicated — these are writable. If only users_xml appears, that explains the error.

2. Enable SQL-based access management by adding a writable storage to your server config (typically config.xml or a file in config.d/):

   <clickhouse>
       <user_directories>
           <users_xml>
               <path>users.xml</path>
           </users_xml>
           <local_directory>
               <path>access/</path>
           </local_directory>
       </user_directories>
   </clickhouse>
   

3. Ensure the access storage directory exists and is writable by the ClickHouse process:

   mkdir -p /var/lib/clickhouse/access/
   chown clickhouse:clickhouse /var/lib/clickhouse/access/
   

4. Restart the ClickHouse server for the configuration change to take effect:

   sudo systemctl restart clickhouse-server
   

5. Verify that the writable storage is now available:

   SELECT * FROM system.user_directories;
   

6. Retry your access entity creation:

   CREATE USER my_user IDENTIFIED BY 'password';
   

Best Practices

• Always configure at least one writable access storage backend in production environments so you can manage users and roles through SQL.
• Use replicated access storage in clustered setups so that access control entities are automatically synchronized across nodes.
• Keep a bootstrap admin user in users.xml for emergency access, while managing all other entities through SQL.
• Document your access storage configuration in your operations runbook so new team members know where access entities are persisted.

Frequently Asked Questions

Q: Why does ClickHouse not enable SQL access storage by default?
A: Historically, ClickHouse relied on XML files for user management. SQL-based access management was added later and requires explicit configuration to maintain backward compatibility.

Q: Can I use both XML and SQL access storage at the same time?
A: Yes. ClickHouse checks all configured storage backends in order. Entities defined in XML remain read-only, while SQL-managed entities reside in the writable local_directory or replicated backend.

Q: What is the difference between local_directory and replicated access storage?
A: local_directory stores access entities on the local filesystem of a single node. replicated uses ZooKeeper or ClickHouse Keeper to synchronize entities across all nodes in a cluster, which is strongly recommended for multi-node deployments.