ClickHouse DB::Exception: Incorrect access entity definition

The "DB::Exception: Incorrect access entity definition" error in ClickHouse occurs when the server encounters a malformed or invalid definition for a user, role, quota, row policy, or settings profile. The INCORRECT_ACCESS_ENTITY_DEFINITION error code means the entity's structure does not conform to what ClickHouse expects, preventing it from being loaded or created.

Impact

The malformed entity cannot be used. If it is a user definition, that user cannot log in. If it is a role or quota, it cannot be assigned or enforced. In severe cases, a corrupted access entity file can cause warnings during server startup or configuration reload, though ClickHouse typically skips the invalid entity rather than failing entirely.

Common Causes

1. Manually editing access control files in the access_control_path directory and introducing syntax errors
2. Corrupted access entity files due to disk errors or incomplete writes
3. Incompatible access entity format after a ClickHouse version upgrade or downgrade
4. Malformed XML in users.xml or users.d/ configuration files
5. Importing access entity definitions from an incompatible ClickHouse version
6. Invalid SQL syntax in CREATE or ALTER statements for access entities

Troubleshooting and Resolution Steps

1. Check the server log for specific error details:

   grep -i "incorrect access entity" /var/log/clickhouse-server/clickhouse-server.log
   

   The log typically identifies which entity file or definition is problematic.

2. If the error is in a SQL statement, review the syntax:

   • Verify the statement matches ClickHouse's expected format:

     CREATE USER my_user IDENTIFIED WITH sha256_password BY 'password';
     

   • Check for missing or extra clauses.

3. Inspect the access control files directly:

   • Access entity files are stored in the directory specified by access_control_path (default: /var/lib/clickhouse/access/).
   • Each entity is stored as a .sql file. Open the problematic file and look for syntax errors.

4. Recreate the corrupted entity:

   • Drop the broken entity if possible:

     DROP USER IF EXISTS my_user;
     

   • Recreate it with the correct definition:

     CREATE USER my_user IDENTIFIED BY 'password';
     

5. If the file is unreadable or severely corrupted:

   • Remove the corrupt file from the access_control_path directory.
   • Restart ClickHouse or reload the configuration:

     SYSTEM RELOAD CONFIG;
     

   • Recreate the entity via SQL.

6. Fix XML configuration errors:

   • Validate users.xml with an XML linter or parser:

     xmllint --noout /etc/clickhouse-server/users.xml
     

   • Correct any structural issues in the XML.

Best Practices

• Avoid manually editing files in the access_control_path directory. Use SQL statements to manage access entities instead.
• Back up the access control directory before ClickHouse upgrades to enable recovery if format incompatibilities arise.
• Validate XML configuration files with a linter before deploying changes.
• Test access entity definitions in a staging environment before applying them to production.
• Monitor ClickHouse server logs for warnings about malformed access entities during startup.
• When migrating access entities between clusters, use SHOW CREATE commands to generate portable SQL definitions.

Frequently Asked Questions

Q: Can a corrupted access entity file prevent ClickHouse from starting?
A: Generally no. ClickHouse logs a warning and skips the malformed entity. However, if the issue is in users.xml (invalid XML structure), it can prevent the server from loading its configuration correctly.

Q: How do I know which access entity file is corrupted?
A: The server log includes the file path or entity name in the error message. Check the log around startup time or after a SYSTEM RELOAD CONFIG.

Q: Can I recover a corrupted access entity?
A: If you have backups of the access_control_path directory, restore the file from backup. Otherwise, drop the entity and recreate it from your documented definitions.

Q: Does upgrading ClickHouse change the access entity file format?
A: Major version upgrades can introduce changes to the internal format. ClickHouse generally handles migrations automatically, but in rare cases manual intervention may be needed. Always review upgrade notes for access control changes.