Add caveat to KB "How to Configure Settings for a User in ClickHouse"

[jaijhala]

KB article - https://clickhouse.com/docs/knowledgebase/configure-a-user-setting#configure-a-setting-for-a-particular-user states that:

Configure a setting for a particular user
Use ALTER USER to define a setting just for one user. For example:

ALTER USER my_user_name SETTINGS max_threads = 8;

But it actually overwrites ALL the existing settings for this user which can surprise users.

A simple reproducer:

clickhouse-cloud :) CREATE USER test_user1 IDENTIFIED BY 'XXXXXX'
SETTINGS
    max_memory_usage = 10000000000,
    max_execution_time = 60,
    async_insert = 0;

CREATE USER test_user1 IDENTIFIED BY 'XXXXX' SETTINGS max_memory_usage = 10000000000, max_execution_time = 60, async_insert = 0


Ok.

0 rows in set. Elapsed: 0.085 sec.

clickhouse-cloud :) show create user 'test_user1';

SHOW CREATE USER test_user1


   ┌─CREATE USER test_user1─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE USER test_user1 IDENTIFIED WITH sha256_password SETTINGS max_memory_usage = 10000000000, max_execution_time = 60., async_insert = false │
   └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Now if I do as KB article suggested:

clickhouse-cloud :) ALTER USER test_user1 SETTINGS async_insert = 1;

ALTER USER test_user1 DROP ALL SETTINGS, DROP ALL PROFILES, ADD SETTING async_insert = 1

Ok.

0 rows in set. Elapsed: 0.091 sec.

clickhouse-cloud :) SELECT * FROM system.settings_profile_elements WHERE user_name = 'test_user1';

SELECT *
FROM system.settings_profile_elements
WHERE user_name = 'test_user1'

   ┌─profile_name─┬─user_name──┬─role_name─┬─index─┬─setting_name─┬─value─┬─min──┬─max──┬─writability─┬─inherit_profile─┐
1. │ ᴺᵁᴸᴸ         │ test_user1 │ ᴺᵁᴸᴸ      │     0 │ async_insert │ 1     │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ        │ ᴺᵁᴸᴸ            │
   └──────────────┴────────────┴───────────┴───────┴──────────────┴───────┴──────┴──────┴─────────────┴─────────────────┘

1 row in set. Elapsed: 0.002 sec.

clickhouse-cloud :) show create user 'test_user1';

SHOW CREATE USER test_user1

   ┌─CREATE USER test_user1──────────────────────────────────────────────────────────────┐
1. │ CREATE USER test_user1 IDENTIFIED WITH sha256_password SETTINGS async_insert = true │
   └─────────────────────────────────────────────────────────────────────────────────────┘

My other settings are gone!

Instead I should have used ADD/MODIFY SETTINGS for example:

clickhouse-cloud :) ALTER USER test_user1 MODIFY SETTINGS async_insert = 1;

ALTER USER test_user1 MODIFY SETTING async_insert = 1

Ok.

0 rows in set. Elapsed: 0.086 sec.

clickhouse-cloud :) show create user 'test_user1';

SHOW CREATE USER test_user1

   ┌─CREATE USER test_user1────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE USER test_user1 IDENTIFIED WITH sha256_password SETTINGS max_memory_usage = 10000000000, max_execution_time = 60., async_insert = true │
   └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.002 sec.

clickhouse-cloud :) SELECT * FROM system.settings_profile_elements WHERE user_name = 'test_user1';

SELECT *
FROM system.settings_profile_elements
WHERE user_name = 'test_user1'

   ┌─profile_name─┬─user_name──┬─role_name─┬─index─┬─setting_name───────┬─value───────┬─min──┬─max──┬─writability─┬─inherit_profile─┐
1. │ ᴺᵁᴸᴸ         │ test_user1 │ ᴺᵁᴸᴸ      │     0 │ max_memory_usage   │ 10000000000 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ        │ ᴺᵁᴸᴸ            │
2. │ ᴺᵁᴸᴸ         │ test_user1 │ ᴺᵁᴸᴸ      │     1 │ max_execution_time │ 60          │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ        │ ᴺᵁᴸᴸ            │
3. │ ᴺᵁᴸᴸ         │ test_user1 │ ᴺᵁᴸᴸ      │     2 │ async_insert       │ 1           │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ        │ ᴺᵁᴸᴸ            │
   └──────────────┴────────────┴───────────┴───────┴────────────────────┴─────────────┴──────┴──────┴─────────────┴─────────────────┘

It's also odd that I'm able to use SETTINGS directly because that doesn't seem like a valid syntax as per - https://clickhouse.com/docs/sql-reference/statements/alter/user which seems like another bug.