ClickHouse: user `default` doesn't have enough grants for creating another user \ role \ row policy for 20.4 and 20.5

Describe the bug user default doesn’t have enough grants for creating another user \ role \ row policy for 20.4 and 20.5

How to reproduce I use following docker-compose.yaml

version: '3'

services:
  clickhouse:
    image: docker.io/yandex/clickhouse-server:latest
    ports:
      - 8123:8123
      - 8443:8443
      - 9000:9000
      - 9009:9009

docker-compose up -d clickhouse
docker-compose exec clickhouse clickhouse-client

SHOW GRANTS;

┌─GRANTS─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ GRANT SHOW, SELECT, INSERT, ALTER, CREATE, DROP, TRUNCATE, OPTIMIZE, KILL QUERY, SYSTEM, dictGet, INTROSPECTION, SOURCES ON *.* TO default │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

according to documentation https://clickhouse.tech/docs/en/sql-reference/statements/grant/#grant-privileges look like default user lost “ACCESS MANAGEMENT” grant groups in 20.4 and 20.5 version

cause

CREATE USER OR REPLACE monitoring IDENTIFIED WITH NO_PASSWORD HOST ANY SETTINGS PROFILE 'default';

return

Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: default: Not enough privileges. To execute this query it's necessary to have the grant CREATE USER ON *.*.

Which ClickHouse server version to use 20.5.3 20.4.7

for 20.3 all works fine

SHOW GRANTS;

return

┌─GRANTS────────────────────────────────────────┐
│ GRANT ALL ON *.* TO default WITH GRANT OPTION │
└───────────────────────────────────────────────┘

and

CREATE USER OR REPLACE monitoring PROFILE 'default';

return

Ok.
0 rows in set. Elapsed: 0.002 sec.

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 1
Comments: 18 (13 by maintainers)

Most upvoted comments

Exception message is correct but it can be more detailed.

I think if we can’t create user because of access_management is disabled, we should say smth like Can't create user because access_management is disabled the message Not enough privileges is very misleading.

+19

filimonov on Jul 29, 2020

Do you have access_management enabled? https://clickhouse.tech/docs/en/operations/access-rights/#enabling-access-control It is done by setting access_management to 1 in users.xml

+16

qoega on Jul 29, 2020

I think the bug is ‘misleading exception’

+10

filimonov on Jul 29, 2020

Look like there are something changed ? In recent code base, I run into very similar problems. I tried to run query by using HTTP interface without specifying any username/password. It reported DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name (version 20.12.1.1), but this issue doesn’t exist in prior versions.

Then i use the clickhouse-client to login without specifying any user/password neither. It worked. And I run the following command to try to fix the HTTP issue.

CREATE USER default IDENTIFIED with no_password HOST ANY; 
ALTER USER default DEFAULT ROLE ALL;

I tried to issue the HTTP query again, it reported a different message like below and now clickhouse-client login (without user/password) had the same issue as well (reporting not enough privileges errors).

Code: 497, e.displayText() = DB::Exception: default: Not enough privileges. To execute this query it's necessary to have the grant SELECT(s) ON default.alias_cols (version 20.12.1.1)

I have enabled access_management in users.xml by the way

chenziliang on Nov 29, 2020

for work GRANT ALL users.d/grant_all.xml

<yandex>
    <users>
        <default>
            <access_management>1</access_management>
            <named_collection_control>1</named_collection_control>
            <show_named_collections>1</show_named_collections>
            <show_named_collections_secrets>1</show_named_collections_secrets>
        </default>
    </users>
</yandex>

Slach on Feb 29, 2024

Still experiencing this on a new setup

I tried to run query by using HTTP interface without specifying any username/password.

Code: 497. DB::Exception: default: Not enough privileges. To execute this query it's necessary to have grant CREATE ROLE ON *.*. (ACCESS_DENIED) (version 22.4.2.1 (official build))

sagungargs15 on May 18, 2022

The default has changed in 23.11.

alexey-milovidov on Dec 25, 2023