Constraints on Settings

The constraints on settings can be defined in the profiles section of the user.xml configuration file and prohibit users from changing some of the settings with the SET query.
The constraints are defined as the following:

  1. <profiles>
  2.   <user_name>
  3.     <constraints>
  4.       <setting_name_1>
  5.         <min>lower_boundary</min>
  6.       </setting_name_1>
  7.       <setting_name_2>
  8.         <max>upper_boundary</max>
  9.       </setting_name_2>
  10.       <setting_name_3>
  11.         <min>lower_boundary</min>
  12.         <max>upper_boundary</max>
  13.       </setting_name_3>
  14.       <setting_name_4>
  15.         <readonly/>
  16.       </setting_name_4>
  17.     </constraints>
  18.   </user_name>
  19. </profiles>

If the user tries to violate the constraints an exception is thrown and the setting isn’t changed.
There are supported three types of constraints: min, max, readonly. The min and max constraints specify upper and lower boundaries for a numeric setting and can be used in combination. The readonly constraint specifies that the user cannot change the corresponding setting at all.

Example: Let users.xml includes lines:

  1. <profiles>
  2.   <default>
  3.     <max_memory_usage>10000000000</max_memory_usage>
  4.     <force_index_by_date>0</force_index_by_date>
  5.     ...
  6.     <constraints>
  7.       <max_memory_usage>
  8.         <min>5000000000</min>
  9.         <max>20000000000</max>
  10.       </max_memory_usage>
  11.       <force_index_by_date>
  12.         <readonly/>
  13.       </force_index_by_date>
  14.     </constraints>
  15.   </default>
  16. </profiles>

The following queries all throw exceptions:

  1. SET max_memory_usage=20000000001;
  2. SET max_memory_usage=4999999999;
  3. SET force_index_by_date=1;
  1. Code: 452, e.displayText() = DB::Exception: Setting max_memory_usage should not be greater than 20000000000.
  2. Code: 452, e.displayText() = DB::Exception: Setting max_memory_usage should not be less than 5000000000.
  3. Code: 452, e.displayText() = DB::Exception: Setting force_index_by_date should not be changed.

Note: the default profile has special handling: all the constraints defined for the default profile become the default constraints, so they restrict all the users until they’re overridden explicitly for these users.

Original article