What is a ClickHouse Settings Profile?
A ClickHouse Settings Profile is a named collection of settings - and constraints on those settings - that can be assigned to users, roles, or ALL. Profiles let an administrator centralize per-user behavior such as max_memory_usage, max_execution_time, readonly, and max_threads, and they can enforce minimum/maximum bounds or mark settings read-only so users cannot override them at the session level. Profiles are managed through SQL (CREATE SETTINGS PROFILE) or in users.xml, and they support inheritance, which makes it practical to maintain a hierarchy like base -> analyst -> senior_analyst.
How Settings Profiles Work
When a user connects, ClickHouse resolves their effective settings by walking three layers: defaults from users.xml, the user's assigned profile (and any profiles that profile inherits from), and finally any SET statements the user runs during the session. Each layer can be locked down: a profile-level setting marked READONLY cannot be raised by a SET in the session, and a setting wrapped in MIN/MAX constraints will be clamped to the allowed range. This is how multi-tenant ClickHouse deployments enforce per-tenant resource boundaries without trusting client code.
-- Constrain a heavy-analytics group: 32 GB memory ceiling, no write privileges,
-- and prevent the user from raising the limit at session time
CREATE SETTINGS PROFILE analyst
SETTINGS
max_memory_usage = 32000000000 MAX 32000000000 READONLY,
max_execution_time = 600 MAX 1800,
max_threads = 16 MAX 32,
readonly = 2 READONLY
TO ROLE analytics_read;
Here MAX 32000000000 READONLY means the user cannot set max_memory_usage above 32 GB and cannot change it at all in the session. MAX 1800 allows session-level adjustments up to 30 minutes. The TO ROLE clause assigns the profile to a role, which propagates to anyone granted that role.
Settings Profile Constraint Modifiers
| Modifier | Effect |
|---|---|
MIN value |
Setting cannot be set below value |
MAX value |
Setting cannot be set above value |
CONST |
Setting cannot be changed at session time (alias of READONLY for values) |
READONLY |
Setting is locked to the profile's value |
WRITABLE |
Setting is freely modifiable (the default) |
CHANGEABLE_IN_READONLY |
Allow override even when the user has readonly=1 |
INHERIT 'profile_name' |
Pull settings from another profile first; later clauses override |
The full DDL signature is:
CREATE SETTINGS PROFILE [IF NOT EXISTS | OR REPLACE] name [, name2, ...]
[ON CLUSTER cluster_name]
[SETTINGS variable [= value] [MIN [=] min] [MAX [=] max]
[CONST|READONLY|WRITABLE|CHANGEABLE_IN_READONLY] | INHERIT 'profile_name']
[TO { user | role | ALL | ALL EXCEPT user | NONE }]
ALTER SETTINGS PROFILE and DROP SETTINGS PROFILE use the same shape. To inspect what is in effect for a user, query system.settings_profiles and system.settings_profile_elements.
Common Pitfalls
- Assigning a profile to a user with
TO userthen forgetting that the user is already a member of a role that has its own profile - both apply, the user's directly assigned profile takes precedence. - Using
READONLYonmax_threadsfor analytical users. They lose the ability to lower it for cheap point queries, which can cause queues to back up. - Forgetting
ON CLUSTERwhen runningCREATE SETTINGS PROFILEon a sharded deployment - the profile only lives on the node it was created on. - Editing
users.xmlto manage profiles while also creating them via SQL. The SQL-driven access entities live inaccess/; mixing the two storage paths causes drift on restart. - Putting
readonly=2in a default profile and then being unable to run any DDL because the new session inherits the read-only flag.
Operating Settings Profiles
The relevant system tables for auditing profiles are system.settings_profiles (one row per profile), system.settings_profile_elements (the settings and their constraints inside each profile), and system.users / system.roles (links profile assignments back to identities).
-- Which profile is currently in effect for the running session
SELECT name, value, changed, readonly, type
FROM system.settings WHERE changed = 1;
-- All profiles assigned to a given role
SELECT settings_profile, granted_to_role
FROM system.settings_profile_elements
WHERE granted_to_role = 'analytics_read';
Pulse tracks settings drift across replicas, alerts when a profile is silently changed, and can recommend tighter max_memory_usage or max_execution_time clamps when it detects users repeatedly tripping limits. For shared clusters that hand out access to many tenants, Pulse's agentic SRE engine can auto-apply profile fixes (for example, lowering max_concurrent_queries_for_user on a noisy tenant) and roll them back on a schedule.
Frequently Asked Questions
Q: How do I create a ClickHouse settings profile?
A: Use CREATE SETTINGS PROFILE <name> SETTINGS <setting> = <value> [MIN ...] [MAX ...] [READONLY] TO <user_or_role>. Profiles can also be defined in users.xml under <profiles>, but SQL DDL is the recommended path on modern clusters because it replicates via Keeper when run with ON CLUSTER.
Q: Can a ClickHouse user have multiple settings profiles?
A: Direct assignment is one profile per user via TO, but a profile can inherit from any number of others using INHERIT 'base_profile' clauses. Roles assigned to a user can also bring their own profile, so a user effectively combines: their direct profile, all inherited profiles, and the profile of each assigned role.
Q: What is the difference between a settings profile and a user quota?
A: A settings profile controls per-query and per-session behavior (memory, threads, timeouts). A quota controls aggregated resource usage over a time window (queries per hour, bytes read per day) and can apply to a user or role independently. Both apply simultaneously - hitting either one blocks the query.
Q: How do MIN, MAX, and READONLY interact in a profile?
A: MIN and MAX define an allowed range for session-time overrides; READONLY (and CONST) lock the value entirely so even values inside the MIN/MAX range cannot be set. If you specify both a MAX and READONLY, the value is fixed to the profile's value - the MAX is informational and the setting is unchangeable.
Q: Are profile changes applied to existing sessions?
A: No. Active sessions keep the settings they resolved at connect time. New sessions and new queries from existing connections pick up the new profile values. To force a refresh, drop the affected user's sessions or KILL QUERY and reconnect.
Q: How do I assign a profile to every user?
A: Use TO ALL (or TO ALL EXCEPT admin to keep admin sessions free of the constraints). For roles, GRANT <role> TO ALL plus a profile attached to that role gives the same effect with more granularity.
Related Reading
- ClickHouse Memory Management - the limits typically clamped by a profile
- CREATE USER in ClickHouse - users that profiles are attached to
- CREATE ROLE in ClickHouse - roles as a cleaner assignment target
- max_memory_usage Setting - common per-profile clamp
- max_execution_time Setting - another common clamp
- GRANT and REVOKE - access control that pairs with profiles
- ClickHouse Documentation Hub - index of all ClickHouse KB pages
