ClickHouse DB::Exception: Access entity already exists

The "DB::Exception: Access entity already exists" error in ClickHouse occurs when you attempt to create a user, role, quota, or row policy that already exists on the server. The ACCESS_ENTITY_ALREADY_EXISTS error code tells you that ClickHouse found a matching name in its access control storage and refused to create a duplicate. The two clean fixes are to add IF NOT EXISTS for idempotent scripts or OR REPLACE if you genuinely want to redefine the entity.

Impact

The CREATE statement fails, but the existing entity remains unchanged. This is typically a non-destructive error. However, if it occurs in an automated provisioning pipeline, it can halt execution unless the script accounts for pre-existing entities.

Common Causes

1. Running a CREATE USER, CREATE ROLE, or CREATE QUOTA statement for an entity that was already created
2. Migration scripts executed more than once without idempotency guards
3. Multiple administrators independently creating the same entity
4. Cluster replication already propagated the entity to the target node
5. An entity with the same name exists in a different access storage backend (XML vs. SQL)

Troubleshooting and Resolution Steps

1. Confirm the entity exists:

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

2. Use IF NOT EXISTS to make the statement idempotent:

   CREATE USER IF NOT EXISTS my_user IDENTIFIED BY 'password';
   CREATE ROLE IF NOT EXISTS my_role;
   CREATE QUOTA IF NOT EXISTS my_quota FOR INTERVAL 1 hour MAX queries = 1000 TO my_user;
   

3. If you need to update the existing entity, use ALTER instead:

   ALTER USER my_user IDENTIFIED BY 'new_password';
   ALTER QUOTA my_quota FOR INTERVAL 1 hour MAX queries = 5000;
   

4. If you want to replace the entity entirely, drop and recreate it:

   DROP USER IF EXISTS my_user;
   CREATE USER my_user IDENTIFIED BY 'password';
   

   Be aware that dropping a user removes all their grants and role assignments.

5. Use CREATE OR REPLACE where supported:

   CREATE OR REPLACE USER my_user IDENTIFIED BY 'password';
   

Best Practices

• Always use IF NOT EXISTS in provisioning and migration scripts to ensure idempotency.
• Prefer CREATE OR REPLACE when you want to ensure the entity matches a specific definition regardless of whether it already exists.
• Coordinate access entity creation among team members to avoid duplicated efforts.
• In clustered setups with replicated access control, create entities on one node and let replication handle propagation rather than running the same CREATE on every node.
• Version-control your access entity definitions so there is a single source of truth.

Frequently Asked Questions

Q: Does IF NOT EXISTS update the entity if it already exists?
A: No. IF NOT EXISTS simply suppresses the error and leaves the existing entity unchanged. If you need to modify it, use ALTER.

Q: Can I have a user and a role with the same name?
A: Users and roles occupy separate namespaces, so they can share a name. The error only occurs when creating a duplicate within the same entity type.

Q: Why do I get this error when the entity does not appear in SHOW USERS?
A: The entity might exist in a different access storage backend. For example, it could be defined in users.xml while you are querying SQL-managed entities. Check both XML configuration files and SQL storage.

Q: Is CREATE OR REPLACE safe for users with existing grants?
A: CREATE OR REPLACE drops and recreates the entity, which means existing grants and role assignments may be lost. Use it with caution and reassign privileges afterward if needed.