CREATE SETTINGS PROFILE | ClickHouse Docs

Creates settings profiles that can be assigned to a user or a role.

Syntax:

CREATE SETTINGS PROFILE [IF NOT EXISTS | OR REPLACE] name1 [, name2 [,...]]
    [ON CLUSTER cluster_name]
    [IN access_storage_type]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [CONST|READONLY|WRITABLE|CHANGEABLE_IN_READONLY] | INHERIT 'profile_name'] [,...]
    [TO {{role1 | user1 [, role2 | user2 ...]} | NONE | ALL | ALL EXCEPT {role1 | user1 [, role2 | user2 ...]}}]

ON CLUSTER clause allows creating settings profiles on a cluster, see Distributed DDL.

CREATE SETTINGS PROFILE requires the CREATE SETTINGS PROFILE privilege. OR REPLACE throws away an existing profile of the same name, including which roles it applies to, so it additionally requires the DROP SETTINGS PROFILE privilege. The DROP SETTINGS PROFILE privilege is required whether or not the profile already exists, so the statement cannot be used to find out which profiles exist.

Example

Create a user:

CREATE USER robin IDENTIFIED BY 'password';

Create the max_memory_usage_profile settings profile with value and constraints for the max_memory_usage setting and assign it to user robin:

CREATE
SETTINGS PROFILE max_memory_usage_profile SETTINGS max_memory_usage = 100000001 MIN 90000000 MAX 110000000
TO robin