ClickHouse DB::Exception: Access storage is readonly

The "DB::Exception: Access storage is readonly" error in ClickHouse occurs when you attempt to modify access control entities (users, roles, quotas, or row policies) in a storage backend that is configured as read-only. The ACCESS_STORAGE_READONLY error code indicates that while the entities can be read, the storage does not accept writes.

Impact

All attempts to create, alter, or drop access control entities fail. This prevents administrators from managing users, roles, quotas, and row policies through SQL statements. The server continues to operate normally for queries, but access control is frozen in its current state until the storage configuration is changed.

Common Causes

1. Access control entities are defined in users.xml or users.d/ files, which are inherently read-only from SQL
2. SQL-based access management is not enabled in the server configuration
3. The access_control_path directory has incorrect file system permissions
4. The server is running in a read-only mode due to disk issues
5. ClickHouse Keeper or ZooKeeper (for replicated access storage) is unavailable or in read-only mode
6. A read replica or follower node where access control writes are not permitted

Troubleshooting and Resolution Steps

1. Enable SQL-based access management:

   • In users.xml, ensure the admin user has access_management enabled:

     <users>
       <default>
         <access_management>1</access_management>
       </default>
     </users>
     

   • Alternatively, create a dedicated admin user with this flag enabled.

2. Check the access_control_path:

   • Verify the directory exists and is writable by the ClickHouse process:

     ls -la /var/lib/clickhouse/access/
     

   • Fix permissions if needed:

     chown -R clickhouse:clickhouse /var/lib/clickhouse/access/
     chmod 700 /var/lib/clickhouse/access/
     

3. Migrate from XML-based to SQL-based access control:

   • Entities defined in users.xml cannot be modified via SQL. To make them mutable, recreate them using SQL:

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

   • Then remove the corresponding entries from users.xml.

4. Check disk space and filesystem health:

   • Ensure the disk hosting the access control path has sufficient free space and is not mounted read-only.

5. For replicated access storage, verify Keeper connectivity:

   • Check that ClickHouse Keeper or ZooKeeper is running and accepting writes:

     SELECT * FROM system.zookeeper WHERE path = '/';
     

6. Restart ClickHouse after configuration changes:

   sudo systemctl restart clickhouse-server
   

Best Practices

• Enable SQL-based access management from the start to avoid limitations of XML-only configuration.
• Use a dedicated admin user with access_management=1 rather than enabling it on the default user.
• Monitor disk space on the volume hosting the access control storage.
• In production clusters, use replicated access storage backed by ClickHouse Keeper for consistency and availability.
• Keep a backup of the access_control_path directory as part of your regular backup procedures.

Frequently Asked Questions

Q: Can I modify users defined in users.xml via SQL?
A: No. Entities defined in XML configuration files are read-only from the SQL interface. You need to either edit the XML files directly or migrate the entities to SQL-based access control.

Q: How do I enable SQL-based access management?
A: Set <access_management>1</access_management> for at least one user in users.xml. That user can then create and manage other users, roles, and quotas via SQL.

Q: What is the difference between XML and SQL access storage?
A: XML storage (users.xml and files in users.d/) is file-based and read-only from SQL. SQL storage writes to the access_control_path directory and supports full CRUD operations via SQL statements. Both can coexist on the same server.

Q: Will switching to SQL access control disrupt existing connections?
A: Existing XML-based entities continue to function. You can gradually migrate by creating SQL-based equivalents and then removing XML entries. Active sessions are not interrupted during this process.