ClickHouse DB::Exception: Access entity not found

The "DB::Exception: Access entity not found" error in ClickHouse is a general error indicating that a referenced access control entity -- whether a user, role, quota, or row policy -- does not exist on the server. The ACCESS_ENTITY_NOT_FOUND error code is raised when ClickHouse looks up the entity by name and finds no match in its access control storage.

Impact

Any operation that depends on the missing entity will fail. This includes granting privileges, assigning roles, altering user settings, or referencing quotas and row policies. When this error appears in automated provisioning or application startup, it can block the entire workflow until the missing entity is created or the reference is corrected.

Common Causes

1. The user, role, quota, or row policy was never created
2. The entity was dropped and references to it were not cleaned up
3. A typo in the entity name
4. Migrating between clusters without transferring access control definitions
5. The entity exists in a different access control storage backend (e.g., defined in XML but queried via SQL, or vice versa)
6. Using ON CLUSTER but the entity only exists on some nodes
7. Access control metadata corruption or disk issues preventing the entity from loading

Troubleshooting and Resolution Steps

1. Identify the type of missing entity from the error message and check for its existence:

   • For users: SHOW USERS;
   • For roles: SHOW ROLES;
   • For quotas: SHOW QUOTAS;
   • For row policies: SHOW ROW POLICIES;

2. Check the exact name and spelling:

   • Entity names are case-sensitive in ClickHouse. Verify the name matches exactly.

3. Create the missing entity:

   • User: CREATE USER my_user IDENTIFIED BY 'password';
   • Role: CREATE ROLE my_role;
   • Quota: CREATE QUOTA my_quota FOR INTERVAL 1 hour MAX queries = 1000 TO my_user;
   • Row policy: CREATE ROW POLICY my_policy ON my_table FOR SELECT USING condition TO my_role;

4. Check access control storage configuration:

   • ClickHouse can store access entities in local files or ZooKeeper/ClickHouse Keeper. Verify the access_control_path and <access_management> settings in config.xml:

     <access_control_path>/var/lib/clickhouse/access/</access_control_path>
     

   • Ensure the storage backend is healthy and accessible.

5. Synchronize across cluster nodes:

   • If using replicated access control (via Keeper), check that all nodes are in sync.
   • For non-replicated setups, verify the entity exists on the specific node you are connecting to.

6. Review server logs for load errors:

   • If the entity was defined but failed to load, the server log may contain errors about corrupt or unreadable access control files.

Best Practices

• Use replicated access control storage (backed by ClickHouse Keeper or ZooKeeper) in clustered environments so entities are consistent across all nodes.
• Include all access entity definitions in version-controlled migration scripts.
• Before referencing an entity in a GRANT, ALTER, or other statement, verify it exists with the appropriate SHOW command.
• Implement monitoring for access control consistency across cluster nodes.
• When decommissioning entities, trace all references first to avoid cascading ACCESS_ENTITY_NOT_FOUND errors.

Frequently Asked Questions

Q: How can I tell whether the missing entity is a user, role, quota, or row policy?
A: The full error message usually specifies the entity type and name, such as "There is no user my_user" or "There is no role my_role".

Q: Can this error occur if the entity exists in XML but I query it via SQL?
A: In most configurations, XML-defined and SQL-defined entities are accessible through both interfaces. However, some operations (like ALTER) may not work on XML-defined entities. Check your access_management settings.

Q: How do I export all access entities for migration?
A: Use SHOW CREATE USER, SHOW CREATE ROLE, SHOW CREATE QUOTA, and SHOW CREATE ROW POLICY for each entity. You can also back up the files in the access_control_path directory directly.

Q: Does dropping a user automatically clean up their role and quota assignments?
A: Dropping a user removes their grants and role assignments. However, the roles and quotas themselves remain. References from other entities to the dropped user are cleaned up.