ClickHouse DB::Exception: Readonly setting cannot be modified

The READONLY_SETTING error appears when a user attempts to change a setting that has been locked by the server's access control configuration. The typical message is DB::Exception: Cannot modify 'setting_name'. Setting is readonly. ClickHouse supports a readonly flag on user profiles that restricts which settings a session can modify, and this error means the current session does not have permission to alter the requested setting.

Impact

Users affected by this error cannot tune query behavior for their session -- for example, they cannot increase max_threads, change max_memory_usage, or enable experimental features. This can be frustrating for developers and analysts who need to optimize individual queries. The error does not affect the server itself or other users; it is scoped to the specific session that attempts the change.

Common Causes

1. User profile has readonly = 1 -- This mode forbids all setting modifications and also forbids non-SELECT queries.
2. User profile has readonly = 2 -- This mode allows changing settings but only those not explicitly constrained. Some settings may still be locked.
3. Constraints in users.xml or SQL-based access control -- Individual settings can be marked as const in a profile's <constraints> section, making them immutable regardless of the readonly flag.
4. ClickHouse Cloud or managed service restrictions -- Managed ClickHouse providers sometimes enforce readonly profiles for certain user tiers.
5. HTTP interface default behavior -- Connections via the HTTP interface may default to readonly = 1 depending on configuration, especially for GET requests.

Troubleshooting and Resolution Steps

1. Check your current readonly mode:

   SELECT name, value FROM system.settings WHERE name = 'readonly';
   

   A value of 1 means fully read-only. A value of 2 allows setting changes (with some exceptions). A value of 0 means no readonly restriction.

2. Identify which profile is applied:

   SELECT * FROM system.settings_profiles WHERE name IN (
       SELECT granted_role_name FROM system.role_grants
       WHERE user_name = currentUser()
   );
   

   Or check users.xml for the profile assigned to your user.

3. Review constraints on the specific setting: Check if the setting has a const constraint that prevents modification:

   <!-- In users.xml -->
   <profiles>
       <my_profile>
           <constraints>
               <max_memory_usage>
                   <const/>  <!-- This makes the setting immutable -->
               </max_memory_usage>
           </constraints>
       </my_profile>
   </profiles>
   

4. Switch to a less restrictive profile (if permitted): An admin can assign a different profile that allows the needed changes:

   ALTER USER my_user SETTINGS PROFILE 'default';
   

5. For HTTP interface users: If you connect via HTTP and get readonly errors, ensure you are using POST requests (GET requests default to readonly). You can also set readonly=0 in the URL parameters if the server allows it:

   POST http://localhost:8123/?readonly=0
   

6. Modify the profile configuration: If you are the administrator, adjust the profile in users.xml or via SQL:

   -- SQL-based access control
   ALTER SETTINGS PROFILE 'analyst' SETTINGS readonly = 0;
   

   Or remove the const constraint on the specific setting that needs to be changeable.

Best Practices

• Use readonly = 2 instead of readonly = 1 when you want to restrict DDL and DML but still allow users to tune query settings.
• Apply constraints on individual dangerous settings (like max_memory_usage) using min/max bounds rather than blanket readonly modes.
• Document which profiles are assigned to which user groups and what restrictions they carry.
• Test profile changes in a non-production environment before rolling them out.
• For automated tools and dashboards that need to set query-level options, ensure their ClickHouse user has an appropriate profile.

Frequently Asked Questions

Q: What is the difference between readonly = 1 and readonly = 2?
A: With readonly = 1, the user can only run SELECT queries and cannot modify any settings. With readonly = 2, the user can still only run SELECT queries, but they are allowed to change session settings (unless individually constrained). readonly = 0 imposes no restrictions.

Q: Can I override readonly for a single query without changing the profile?
A: No. If your session is in readonly mode, you cannot override it from within that session. An administrator must change the profile or grant a less restrictive one.

Q: I am an admin but still getting this error. Why?
A: Check whether your admin user has an explicit profile assigned that includes readonly constraints. Even the default user can have restrictions if users.xml was configured that way. Also verify you are connecting with the correct user credentials.

Q: Does this error affect INSERT and CREATE TABLE statements too?
A: With readonly = 1, yes -- all non-SELECT operations are blocked, including INSERT, CREATE, ALTER, and DROP. The READONLY_SETTING error specifically relates to setting changes, but you will see separate permission errors for DDL/DML under readonly mode.