ClickHouse DB::Exception: Query is prohibited

The "DB::Exception: Query is prohibited" error in ClickHouse means that the server has blocked a query based on security rules, settings profiles, or readonly restrictions. The QUERY_IS_PROHIBITED error code is raised when a query violates an explicit prohibition configured by an administrator, such as disabling certain query types or preventing DDL operations.

Impact

The blocked query is not executed, and no data is read or modified. This is a security enforcement mechanism. Users receiving this error cannot proceed with the prohibited query type until an administrator changes their permissions or the relevant settings. In application contexts, this may cause unexpected failures if the application assumes certain query types are always available.

Common Causes

1. The user's settings profile has readonly = 1 or readonly = 2, which prohibits DDL or write operations
2. An allow_ddl setting is set to 0, preventing CREATE, ALTER, DROP, and similar statements
3. Query complexity or resource limits are configured in a way that blocks the specific query pattern
4. A settings constraint (defined in users.xml or via SQL) prevents changing a setting needed by the query
5. The allow_introspection_functions setting is disabled, blocking queries that use introspection
6. Custom query filters or security rules in the user configuration reject the query

Troubleshooting and Resolution Steps

1. Check the full error message for details on which rule or setting caused the prohibition:

   SELECT event_time, user, query, exception
   FROM system.query_log
   WHERE exception LIKE '%prohibited%'
   ORDER BY event_time DESC
   LIMIT 10;
   

2. Review the current user's effective settings, especially readonly and DDL-related ones:

   SELECT name, value, changed
   FROM system.settings
   WHERE name IN ('readonly', 'allow_ddl', 'allow_introspection_functions');
   

3. Check which profile is assigned to the user and what restrictions it imposes:

   SELECT * FROM system.settings_profiles;
   SELECT * FROM system.settings_profile_elements
   WHERE profile_name = 'your_profile';
   

4. If the query is blocked by readonly mode, connect with a user that has write permissions, or ask an administrator to adjust the profile:

   -- As admin, grant appropriate permissions
   ALTER SETTINGS PROFILE 'readonly_profile' MODIFY SETTINGS readonly = 0;
   

5. If DDL is blocked by allow_ddl = 0, adjust the setting at the profile level. Note that once allow_ddl = 0 is in effect for a session, you cannot re-enable it with SET allow_ddl = 1 in that same session — change it in the user's profile instead:

   -- As admin, update the profile
   ALTER SETTINGS PROFILE 'user_profile' MODIFY SETTINGS allow_ddl = 1;
   

6. If connecting via HTTP interface, check whether the connection string forces readonly mode:

   # readonly=1 in the URL forces read-only mode
   http://localhost:8123/?readonly=1
   

7. Review the users.xml configuration for any explicit prohibitions:

   grep -A 10 'readonly\|allow_ddl\|constraints' /etc/clickhouse-server/users.xml
   

Best Practices

• Design settings profiles that match the intended role of each user: read-only for analysts, full access for ETL pipelines, and administrative access only for DBAs.
• Use SQL-based access control (CREATE USER, GRANT, CREATE SETTINGS PROFILE) rather than editing XML files for easier management.
• Document which query types are blocked for each profile so application developers know what to expect.
• Prefer using GRANT and REVOKE for fine-grained access control over blanket readonly restrictions.
• Test application queries against the target user profile in a staging environment to catch prohibition errors before production.

Frequently Asked Questions

Q: What is the difference between QUERY_IS_PROHIBITED and access denied errors?
A: QUERY_IS_PROHIBITED is typically triggered by settings-level restrictions (like readonly or allow_ddl) that block entire categories of queries. Access denied errors are raised by the RBAC system when a user lacks specific privileges on a database, table, or column.

Q: Can I allow a readonly user to change some settings?
A: Yes. Setting readonly = 2 allows users to change settings that are not further restricted by constraints. You can define constraints in the settings profile to control exactly which settings a user may modify and within what range.

Q: Why is my application getting this error after a ClickHouse upgrade?
A: Some ClickHouse versions introduce new security defaults or tighten existing ones. Check the release notes for changes to default settings profiles and adjust your configuration accordingly.