Error trying to create table

[joan.ba...@gmail.com]

Hello,

I have the following grants for 'ROLE_ITRAVEX', one of them is 'CREATE TABLE':

clickhouse-1 :) SELECT * FROM system.grants where role_name = 'ROLE_ITRAVEX'

┌─user_name─┬─role_name────┬─access_type───────┬─database───┬─table─┬─column─┬─is_partial_revoke─┬─grant_option─┐
│ ᴺᵁᴸᴸ      │ ROLE_ITRAVEX │ SELECT            │ DM_ITRAVEX │ ᴺᵁᴸᴸ  │ ᴺᵁᴸᴸ   │                 0 │            0 │
│ ᴺᵁᴸᴸ      │ ROLE_ITRAVEX │ INSERT            │ DM_ITRAVEX │ ᴺᵁᴸᴸ  │ ᴺᵁᴸᴸ   │                 0 │            0 │
│ ᴺᵁᴸᴸ      │ ROLE_ITRAVEX │ ALTER UPDATE      │ DM_ITRAVEX │ ᴺᵁᴸᴸ  │ ᴺᵁᴸᴸ   │                 0 │            0 │
│ ᴺᵁᴸᴸ      │ ROLE_ITRAVEX │ ALTER DELETE      │ DM_ITRAVEX │ ᴺᵁᴸᴸ  │ ᴺᵁᴸᴸ   │                 0 │            0 │
│ ᴺᵁᴸᴸ      │ ROLE_ITRAVEX │ CREATE DATABASE   │ DM_ITRAVEX │ ᴺᵁᴸᴸ  │ ᴺᵁᴸᴸ   │                 0 │            0 │
│ ᴺᵁᴸᴸ      │ ROLE_ITRAVEX │ CREATE TABLE      │ DM_ITRAVEX │ ᴺᵁᴸᴸ  │ ᴺᵁᴸᴸ   │                 0 │            0 │
│ ᴺᵁᴸᴸ      │ ROLE_ITRAVEX │ CREATE VIEW       │ DM_ITRAVEX │ ᴺᵁᴸᴸ  │ ᴺᵁᴸᴸ   │                 0 │            0 │
│ ᴺᵁᴸᴸ      │ ROLE_ITRAVEX │ CREATE DICTIONARY │ DM_ITRAVEX │ ᴺᵁᴸᴸ  │ ᴺᵁᴸᴸ   │                 0 │            0 │
└───────────┴──────────────┴───────────────────┴────────────┴───────┴────────┴───────────────────┴──────────────┘

And this is the user:
SELECT *
FROM system.users

┌─name───────────┬─id───────────────────────────────────┬─storage─────────┬─auth_type──────────┬─auth_params─┬─host_ip──┬─host_names─┬─host_names_regexp─┬─host_names_like─┬─default_roles_all─┬─default_roles_list──┬─default_roles_except─┬─grantees_any─┬─grantees_list─┬─grantees_except─┬─default_database─┐
│ default        │ 94309d50-4f52-5250-31bd-74fecac179db │ users.xml       │ plaintext_password │ {}          │ ['::/0'] │ []         │ []                │ []              │                 1 │ []                  │ []                   │            1 │ []            │ []              │                  │
│ USR_ITRAVEX    │ 334ceb6d-1826-912f-45ab-55bb6f16c283 │ local directory │ plaintext_password │ {}          │ ['::/0'] │ []         │ []                │ []              │                 0 │ ['ROLE_ITRAVEX']    │ []                   │            1 │ []            │ []              │                  │

Now I connect to clickhouse using this user with its database:
root:~# clickhouse-client --user USR_ITRAVEX --password ******* --database DM_ITRAVEX

And try to create a table:
clickhouse-1 :) CREATE TABLE ITRAVEX_TMP_169080022776964(ID String,DESCRIPTION String,UPDATE_TIME DateTime) ENGINE = MergeTree() ORDER BY ID;

CREATE TABLE ITRAVEX_TMP_169080022776964
(
    `ID` String,
    `DESCRIPTION` String,
    `UPDATE_TIME` DateTime
)
ENGINE = MergeTree
ORDER BY ID

Query id: 877433e5-3fe5-46ec-b4a1-6da82f299deb
0 rows in set. Elapsed: 0.003 sec.

Received exception from server (version 21.9.4):
Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: USR_ITRAVEX: Not enough privileges. To execute this query it's necessary to have grant CREATE TABLE ON DM_ITRAVEX.ITRAVEX_TMP_169080022776964. (ACCESS_DENIED)

Why can't I create a tble for this user and database if I have the right role with the CREATE permission?

Thanks,

Joan.

[joan.ba...@gmail.com]

Sorry, versions:

root:~# clickhouse-client --user USR_ITRAVEX --password itravex_client_secret --database DM_ITRAVEX
ClickHouse client version 21.9.4.35 (official build).
Connecting to database DM_ITRAVEX at localhost:9000 as user USR_ITRAVEX.
Connected to ClickHouse server version 21.9.4 revision 54449.

[Vladimir]

Hello Joan,

Seems this script works as expected, could you pleas try it?

From default user:

CREATE ROLE IF NOT EXISTS ROLE_ITRAVEX;

CREATE DATABASE IF NOT EXISTS DM_ITRAVEX;

GRANT SELECT ON DM_ITRAVEX.* TO ROLE_ITRAVEX;

GRANT INSERT ON DM_ITRAVEX.* TO ROLE_ITRAVEX;

GRANT ALTER UPDATE ON DM_ITRAVEX.* TO ROLE_ITRAVEX;

GRANT ALTER DELETE ON DM_ITRAVEX.* TO ROLE_ITRAVEX;

GRANT CREATE DATABASE ON DM_ITRAVEX.* TO ROLE_ITRAVEX;

GRANT CREATE TABLE ON DM_ITRAVEX.* TO ROLE_ITRAVEX;

GRANT CREATE VIEW ON DM_ITRAVEX.* TO ROLE_ITRAVEX;

GRANT CREATE DICTIONARY ON DM_ITRAVEX.* TO ROLE_ITRAVEX;

CREATE USER IF NOT EXISTS USR_ITRAVEX;

GRANT ROLE_ITRAVEX TO USR_ITRAVEX;

SET DEFAULT ROLE ROLE_ITRAVEX TO USR_ITRAVEX;

-- Ok:

-- clickhouse-client --user USR_ITRAVEX --database DM_ITRAVEX -q "CREATE TABLE ITRAVEX_TMP_169080022776964(ID String,DESCRIPTION String,UPDATE_TIME DateTime) ENGINE = MergeTree() ORDER BY ID;"

I tested in on latest version, 23.5 and 21.5.

Access management should be enabled ( https://clickhouse.com/docs/en/operations/access-rights#enabling-access-control )

<clickhouse>

<users>

<default>

<access_management>1</access_management>

<named_collection_control>1</named_collection_control>

<show_named_collections_secrets>1</show_named_collections_secrets>

<show_named_collections>1</show_named_collections>

</default>

</users>

</clickhouse>

Do you have some specific configuration of a server? If it's possible you may share configs as well.

[joan.ba...@gmail.com]

Hi Vladimir,

It does not work, same error.

Attached the config and users files. You'll see some users on the users.xml file that were created a lot of time ago manually adding them to this file.

New users are now created from an application in this way:

CREATE ROLE IF NOT EXISTS ROLE_****;
GRANT CREATE,SELECT,UPDATE,INSERT,DELETE ON DM_****.* TO ROLE_****;
CREATE USER IF NOT EXISTS USR_**** HOST LIKE '192.168.%.%' IDENTIFIED WITH plaintext_password BY '******' DEFAULT ROLE ROLE_**** DEFAULT DATABASE DM_****;

Thanks,

Joan.

[joan.ba...@gmail.com]

Hi,

If I execute:

root:~#  clickhouse-client --user default --password ******

ClickHouse client version 21.9.4.35 (official build).

Connecting to localhost:9000 as user default.

Connected to ClickHouse server version 21.9.4 revision 54449.

clickhouse-1 :) GRANT ALL ON DM_ITRAVEX.* TO USR_ITRAVEX;

GRANT SHOW, SELECT, INSERT, ALTER, CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY, DROP, TRUNCATE, OPTIMIZE, SYSTEM MERGES, SYSTEM TTL MERGES, SYSTEM FETCHES, SYSTEM MOVES, SYSTEM SENDS, SYSTEM REPLICATION QUEUES, SYSTEM DROP REPLICA, SYSTEM SYNC REPLICA, SYSTEM RESTART REPLICA, SYSTEM RESTORE REPLICA, SYSTEM FLUSH DISTRIBUTED, dictGet ON DM_ITRAVEX.* TO USR_ITRAVEX

Query id: 4431edbc-c1e6-4651-a071-37b9c9bfe259

Ok.

0 rows in set. Elapsed: 0.003 sec.

Then it works.

Joan.

[joan.ba...@gmail.com]

Hi,

Confirmed, this works:

GRANT CREATE,SELECT,UPDATE,INSERT,DELETE ON DM_ITRAVEX.* TO USR_ITRAVEX;

But going through the role does not work.

Joan.