Grant access to users to manage specific databases #1492

darioneto · 2024-08-19T13:56:46Z

Maybe I missed something in the doc but I'm unable to modify additional user permissions dynamically due to read-only storage of user configurations. there is a conflict between different permission-setting methods in the CHI manifest.
Admin user lacks sufficient privileges to modify other users' permissions.

Attempt to use admin user to modify permissions but this fails

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, ALTER TABLE, 
      SHOW TABLES, SHOW COLUMNS, TRUNCATE, OPTIMIZE 
ON pantry_assistant.* TO veronica;

The combination of grants and allow_databases also fails in the manifest

veronica/allow_databases/pantry_assistant: 1
veronica/grants:
  - SELECT ON pantry_assistant.*
  - INSERT ON pantry_assistant.*
  - UPDATE ON pantry_assistant.*
  - DELETE ON pantry_assistant.*

tested with this but not working

    veronica/grants:
      - SELECT ON pantry_assistant.*
      - INSERT ON pantry_assistant.*
      - ALTER ON pantry_assistant.*
      - CREATE TABLE ON pantry_assistant.*
      - DROP TABLE ON pantry_assistant.*
      - SHOW TABLES ON pantry_assistant.*
      - SHOW COLUMNS ON pantry_assistant.*
      - TRUNCATE ON pantry_assistant.*
      - OPTIMIZE ON pantry_assistant.*
      - SHOW DATABASES

so far I have this on but this is still not working

  configuration:
    users: 
      admin/password: xxx    
      admin/networks/ip: 0.0.0.0/0 
      admin/access_management: 1

      veronica/password: trapagositrurinred12r
      veronica/networks/ip: 0.0.0.0/0
      veronica/profile: default
      veronica/quota: default
      veronica/allow_databases/pantry_assistant: 1
      veronica/settings/default_database: pantry_assistant

my intention is to add user veronica with full access to this one database "pantry_assistant" only so she can add/edit/remove tables within and no access to other sections of the DB. Is this doable?

Slach · 2024-08-20T15:55:43Z

Attempt to use admin user to modify permissions but this fails

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, ALTER TABLE, 
     SHOW TABLES, SHOW COLUMNS, TRUNCATE, OPTIMIZE 
ON pantry_assistant.* TO veronica;

XML users can't be modified with SQL RBAC commands

moreover
https://github.com/ClickHouse/ClickHouse/blob/d7c485b8abe64ea3e5883667c412ddf9c9335551/docs/en/operations/settings/settings-users.md?plain=1#L126-L145

allow_databases + grant doesn't work together this is expected behavior

tested with this but not working

   veronica/grants:
     - SELECT ON pantry_assistant.*
     - INSERT ON pantry_assistant.*
     - ALTER ON pantry_assistant.*
     - CREATE TABLE ON pantry_assistant.*
     - DROP TABLE ON pantry_assistant.*
     - SHOW TABLES ON pantry_assistant.*
     - SHOW COLUMNS ON pantry_assistant.*
     - TRUNCATE ON pantry_assistant.*
     - OPTIMIZE ON pantry_assistant.*
     - SHOW DATABASES

this should work, could you check
could you share
grep -C 10 grants -r /var/lib/clickhouse/preprocessed_configs/

darioneto · 2024-08-21T03:10:07Z

yeah in theory this should work from looking at this config

nsible@lb:~$ kubectl exec -it chi-ck3-replicated-0-0-0 -n ck -- bash
Defaulted container "clickhouse-pod" out of: clickhouse-pod, clickhouse-backup
root@chi-ck3-replicated-0-0-0:/# grep -C 10 grants -r /var/lib/clickhouse/preprocessed_configs/
/var/lib/clickhouse/preprocessed_configs/config.xml-        <!-- Enables logic that users without permissive row policies can still read rows using a SELECT query.
/var/lib/clickhouse/preprocessed_configs/config.xml-             For example, if there two users A, B and a row policy is defined only for A, then
/var/lib/clickhouse/preprocessed_configs/config.xml-             if this setting is true the user B will see all rows, and if this setting is false the user B will see no rows.
/var/lib/clickhouse/preprocessed_configs/config.xml-             By default this setting is false for compatibility with earlier access configurations. -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <users_without_row_policies_can_read_rows>false</users_without_row_policies_can_read_rows>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml-        <!-- By default, for backward compatibility ON CLUSTER queries ignore CLUSTER grant,
/var/lib/clickhouse/preprocessed_configs/config.xml-             however you can change this behaviour by setting this to true -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <on_cluster_queries_require_cluster_grant>false</on_cluster_queries_require_cluster_grant>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml:        <!-- By default, for backward compatibility "SELECT * FROM system.<table>" doesn't require any grants and can be executed
/var/lib/clickhouse/preprocessed_configs/config.xml-             by any user. You can change this behaviour by setting this to true.
/var/lib/clickhouse/preprocessed_configs/config.xml-             If it's set to true then this query requires "GRANT SELECT ON system.<table>" just like as for non-system tables.
/var/lib/clickhouse/preprocessed_configs/config.xml-             Exceptions: a few system tables ("tables", "columns", "databases", and some constant tables like "one", "contributors")
/var/lib/clickhouse/preprocessed_configs/config.xml-             are still accessible for everyone; and if there is a SHOW privilege (e.g. "SHOW USERS") granted the corresponding system
/var/lib/clickhouse/preprocessed_configs/config.xml-             table (i.e. "system.users") will be accessible. -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <select_from_system_db_requires_grant>false</select_from_system_db_requires_grant>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml:        <!-- By default, for backward compatibility "SELECT * FROM information_schema.<table>" doesn't require any grants and can be
/var/lib/clickhouse/preprocessed_configs/config.xml-             executed by any user. You can change this behaviour by setting this to true.
/var/lib/clickhouse/preprocessed_configs/config.xml-             If it's set to true then this query requires "GRANT SELECT ON information_schema.<table>" just like as for ordinary tables. -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <select_from_information_schema_requires_grant>false</select_from_information_schema_requires_grant>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml-        <!-- By default, for backward compatibility a settings profile constraint for a specific setting inherit every not set field from
/var/lib/clickhouse/preprocessed_configs/config.xml-             previous profile. You can change this behaviour by setting this to true.
/var/lib/clickhouse/preprocessed_configs/config.xml-             If it's set to true then if settings profile has a constraint for a specific setting, then this constraint completely cancels all
/var/lib/clickhouse/preprocessed_configs/config.xml-             actions of previous constraint (defined in other profiles) for the same specific setting, including fields that are not set by new constraint.
/var/lib/clickhouse/preprocessed_configs/config.xml-             It also enables 'changeable_in_readonly' constraint type -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <settings_constraints_replace_previous>false</settings_constraints_replace_previous>
--
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>10.244.4.153</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>0.0.0.0/0</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-            </networks>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <password_sha256_hex>921443c5e72aac9f10321d52f095edd5ed04ab8deeca8cd0eb425ad46c135c14</password_sha256_hex>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <profile>clickhouse_operator</profile>
/var/lib/clickhouse/preprocessed_configs/users.xml-        </admin>
/var/lib/clickhouse/preprocessed_configs/users.xml-        
/var/lib/clickhouse/preprocessed_configs/users.xml-        <veronica>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <access_management>0</access_management>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <default_database>pantry_assistant</default_database>
/var/lib/clickhouse/preprocessed_configs/users.xml:            <grants>SHOW DATABASES</grants>
/var/lib/clickhouse/preprocessed_configs/users.xml:            <grants>SELECT,INSERT,ALTER,CREATE TABLE,DROP TABLE,TRUNCATE,OPTIMIZE ON pantry_assistant.*</grants>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <networks>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <host_regexp>(chi-ck3-[^.]+\d+-\d+|clickhouse\-ck3)\.ck\.svc\.cluster\.local$</host_regexp>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>::1</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>127.0.0.1</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>0.0.0.0/0</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-            </networks>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <password_sha256_hex>7782cdcfbd26f37ea4f93ade227e83a0bcaca996e1941ebc57430bcb32ec4f2b</password_sha256_hex>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <profile>default</profile>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <quota>default</quota>
/var/lib/clickhouse/preprocessed_configs/users.xml-        </veronica>

however

ansible@lb:~$ kubectl exec -it chi-ck3-replicated-0-0-0 -n ck -- clickhouse-client --user veronica --password xxxxx
Defaulted container "clickhouse-pod" out of: clickhouse-pod, clickhouse-backup
ClickHouse client version 23.8.12.13 (official build).
Connecting to localhost:9000 as user veronica.
Connected to ClickHouse server version 23.8.12 revision 54465.

Warnings:
 * Obsolete setting ['max_memory_usage_for_all_queries'] is changed. Please check 'select * from system.settings where changed and is_obsolete' and read the changelog.

chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :) SHOW DATABASES;

SHOW DATABASES

Query id: 16300254-0256-44c2-b652-f600de4a4c80

Ok.

0 rows in set. Elapsed: 0.002 sec. 

chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :) USE pantry_assistant;

USE pantry_assistant

Query id: 3d3850a5-2d53-4851-91a1-c4d55b92b107


0 rows in set. Elapsed: 0.004 sec. 

Received exception from server (version 23.8.12):
Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: veronica: Not enough privileges. To execute this query, it's necessary to have the grant SHOW DATABASES ON pantry_assistant.*. (ACCESS_DENIED)

chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :) SHOW TABLES;

SHOW TABLES

Query id: 2f4eaf2b-0064-442d-806a-9de3c4a07c4f

Ok.

0 rows in set. Elapsed: 0.003 sec. 

chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :) CREATE TABLE test (id UInt32, name String) ENGINE = MergeTree() ORDER BY id;
INSERT INTO test VALUES (1, 'Test');
SELECT * FROM test;

Syntax error (Multi-statements are not allowed): failed at position 76 (end of query) (line 1, col 76):

CREATE TABLE test (id UInt32, name String) ENGINE = MergeTree() ORDER BY id;
INSERT INTO test VALUES (1, 'Test');
SELECT * FROM test;



chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :)

by the way, this is my manifest, also added management to her db as previous code didn't work : 1

configuration:
  users: 
    admin/password: xxxx
    admin/networks/ip: 0.0.0.0/0 
    admin/access_management: 1

    veronica/password: xxxx
    veronica/networks/ip: 0.0.0.0/0
    veronica/profile: default
    veronica/quota: default
    veronica/access_management: 0
    veronica/default_database: pantry_assistant
    veronica/grants:
      - SHOW DATABASES
      - SELECT,INSERT,ALTER,CREATE TABLE,DROP TABLE,TRUNCATE,OPTIMIZE ON pantry_assistant.*
    veronica/grants/databases/pantry_assistant: 1

restarting pods not helping either, any idea what else can I try ?

Slach · 2024-08-21T10:22:55Z

could you try

 veronica/grants:
      - SHOW DATABASES,SELECT,INSERT,ALTER,CREATE TABLE,DROP TABLE,TRUNCATE,OPTIMIZE ON pantry_assistant.*

and check

kubectl exec -it chi-ck3-replicated-0-0-0 -n ck -- clickhouse-client --user veronica --password xxxxx  

SELECT currentUser(), currentDatabase();

Philippians413 · 2024-08-21T14:23:12Z

I have the same issue. I've tried several configurations, but still has the same error.

spec:
  configuration:
    users:
      username/grants/databases/MS: 1
      username/grants:
        - SELECT ON MS.*

or

spec:
  configuration:
    users:
      username/grants/query:
        - GRANT SELECT ON MS.*

I even tried to grant permissions from another user:

spec:
  configuration:
    users:
      dev/access_management: 1
      dev/named_collection_control: 1
      dev/show_named_collections: 1
      dev/show_named_collections_secrets: 1
      dev/grants:
        - SELECT ON MS.* TO username

but every time I have the same error

Application: DB::Exception: Any other access control settings can't be specified with grants: while parsing user 'username' in users configuration file: while loading configuration file '/etc/clickhouse-server/users.xml'

Application: DB::Exception: Any other access control settings can't be specified with grants: while parsing user 'dev' in users configuration file: while loading configuration file '/etc/clickhouse-server/users.xml'

Slach · 2024-08-21T14:52:36Z

@Philippians413 all your three variants wrong, read error message
use only

spec:
  configuration:
    user_name/grants:
     - <GRANT_NAME> ON db.table

@darioneto any news from your side?

Philippians413 · 2024-08-21T15:12:54Z

@Slach
I specified

spec:
  configuration:
    username/grants:
      - SELECT ON MS.*

but it fails on kubectl apply step:

error: error validating "dev.yaml": error validating data: ValidationError(ClickHouseInstallation.spec.configuration): unknown field "dkt/grants" in com.altinity.clickhouse.v1.ClickHouseInstallation.spec.configuration; if you choose to ignore these errors, turn validation off with --validate=false

I've tried a lot of things, but nothing works.
Clickhouse-operator version - 0.22.2

Slach · 2024-08-21T15:45:17Z

@Philippians413 sorry missed users section

spec:
  configuration:
    users:
      user_name/grants:
      - <GRANT_NAME> ON db.table

Philippians413 · 2024-08-22T12:03:07Z

@Slach I made with this configuration, but the error in the same

2024.08.22 12:01:05.460214 [ 1 ] {} Application: DB::Exception: Any other access control settings can't be specified with grants: while parsing user 'username' in users configuration file: while loading configuration file '/etc/clickhouse-server/users.xml'

spec:
  configuration:
    users:
      username/grants:
        - SELECT ON MS.MSTABLE

Slach · 2024-08-22T12:24:42Z

@Philippians413
grep grants -C 10 -r /var/lib/clickhouse/preprocessed_configs/

darioneto · 2024-08-23T10:41:52Z

sorry @Slach , been busy and missed your reply

so with the latest suggestion, I can see the use can perform selection on this

ansible@lb:~$ kubectl exec -it chi-ck3-replicated-0-0-0 -n ck -- clickhouse-client --user veronica --password xxxx
Defaulted container "clickhouse-pod" out of: clickhouse-pod, clickhouse-backup
ClickHouse client version 23.8.12.13 (official build).
Connecting to localhost:9000 as user veronica.
Connected to ClickHouse server version 23.8.12 revision 54465.

Warnings:
 * Obsolete setting ['max_memory_usage_for_all_queries'] is changed. Please check 'select * from system.settings where changed and is_obsolete' and read the changelog.

chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :) SELECT currentUser(), currentDatabase();

SELECT
    currentUser(),
    currentDatabase()

Query id: d0c616cf-1270-4773-b30e-3c633c1cbbf3

┌─currentUser()─┬─currentDatabase()─┐
│ veronica      │ pantry_assistant  │
└───────────────┴───────────────────┘

however, nothing else is permitted

chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :) SHOW DATABASES;

SHOW DATABASES

Query id: b2aec9a5-24a7-459f-8232-71eea7ae73f2

Ok.

0 rows in set. Elapsed: 0.004 sec. 

chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :)

my values settings are:

spec:
  defaults:
    templates: 
      dataVolumeClaimTemplate: default
      podTemplate: clickhouse:20.7-with-backup  
 
  configuration:
    users: 
      admin/password: xxxxx    
      admin/networks/ip: 0.0.0.0/0 
      admin/access_management: 1

      veronica/password: xxxxxx
      veronica/networks/ip: 0.0.0.0/0
      veronica/profile: default
      veronica/quota: default
      veronica/access_management: 0
      veronica/default_database: pantry_assistant
      veronica/grants:
        - SHOW DATABASES
        - SHOW DATABASES,SELECT,INSERT,ALTER,CREATE TABLE,DROP TABLE,TRUNCATE,OPTIMIZE ON pantry_assistant.*
      veronica/grants/databases/pantry_assistant: 1

oot@chi-ck3-replicated-0-0-0:/# grep -C 10 grants -r /var/lib/clickhouse/preprocessed_configs/
/var/lib/clickhouse/preprocessed_configs/config.xml-        <!-- Enables logic that users without permissive row policies can still read rows using a SELECT query.
/var/lib/clickhouse/preprocessed_configs/config.xml-             For example, if there two users A, B and a row policy is defined only for A, then
/var/lib/clickhouse/preprocessed_configs/config.xml-             if this setting is true the user B will see all rows, and if this setting is false the user B will see no rows.
/var/lib/clickhouse/preprocessed_configs/config.xml-             By default this setting is false for compatibility with earlier access configurations. -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <users_without_row_policies_can_read_rows>false</users_without_row_policies_can_read_rows>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml-        <!-- By default, for backward compatibility ON CLUSTER queries ignore CLUSTER grant,
/var/lib/clickhouse/preprocessed_configs/config.xml-             however you can change this behaviour by setting this to true -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <on_cluster_queries_require_cluster_grant>false</on_cluster_queries_require_cluster_grant>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml:        <!-- By default, for backward compatibility "SELECT * FROM system.<table>" doesn't require any grants and can be executed
/var/lib/clickhouse/preprocessed_configs/config.xml-             by any user. You can change this behaviour by setting this to true.
/var/lib/clickhouse/preprocessed_configs/config.xml-             If it's set to true then this query requires "GRANT SELECT ON system.<table>" just like as for non-system tables.
/var/lib/clickhouse/preprocessed_configs/config.xml-             Exceptions: a few system tables ("tables", "columns", "databases", and some constant tables like "one", "contributors")
/var/lib/clickhouse/preprocessed_configs/config.xml-             are still accessible for everyone; and if there is a SHOW privilege (e.g. "SHOW USERS") granted the corresponding system
/var/lib/clickhouse/preprocessed_configs/config.xml-             table (i.e. "system.users") will be accessible. -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <select_from_system_db_requires_grant>false</select_from_system_db_requires_grant>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml:        <!-- By default, for backward compatibility "SELECT * FROM information_schema.<table>" doesn't require any grants and can be
/var/lib/clickhouse/preprocessed_configs/config.xml-             executed by any user. You can change this behaviour by setting this to true.
/var/lib/clickhouse/preprocessed_configs/config.xml-             If it's set to true then this query requires "GRANT SELECT ON information_schema.<table>" just like as for ordinary tables. -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <select_from_information_schema_requires_grant>false</select_from_information_schema_requires_grant>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml-        <!-- By default, for backward compatibility a settings profile constraint for a specific setting inherit every not set field from
/var/lib/clickhouse/preprocessed_configs/config.xml-             previous profile. You can change this behaviour by setting this to true.
/var/lib/clickhouse/preprocessed_configs/config.xml-             If it's set to true then if settings profile has a constraint for a specific setting, then this constraint completely cancels all
/var/lib/clickhouse/preprocessed_configs/config.xml-             actions of previous constraint (defined in other profiles) for the same specific setting, including fields that are not set by new constraint.
/var/lib/clickhouse/preprocessed_configs/config.xml-             It also enables 'changeable_in_readonly' constraint type -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <settings_constraints_replace_previous>false</settings_constraints_replace_previous>
--
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>10.244.4.153</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>0.0.0.0/0</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-            </networks>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <password_sha256_hex>921443c5e72aac9f10321d52f095edd5ed04ab8deeca8cd0eb425ad46c135c14</password_sha256_hex>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <profile>clickhouse_operator</profile>
/var/lib/clickhouse/preprocessed_configs/users.xml-        </admin>
/var/lib/clickhouse/preprocessed_configs/users.xml-        
/var/lib/clickhouse/preprocessed_configs/users.xml-        <veronica>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <access_management>0</access_management>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <default_database>pantry_assistant</default_database>
/var/lib/clickhouse/preprocessed_configs/users.xml:            <grants>SHOW DATABASES</grants>
/var/lib/clickhouse/preprocessed_configs/users.xml:            <grants>SHOW DATABASES,SELECT,INSERT,ALTER,CREATE TABLE,DROP TABLE,TRUNCATE,OPTIMIZE ON pantry_assistant.*</grants>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <networks>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <host_regexp>(chi-ck3-[^.]+\d+-\d+|clickhouse\-ck3)\.ck\.svc\.cluster\.local$</host_regexp>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>::1</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>127.0.0.1</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>0.0.0.0/0</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-            </networks>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <password_sha256_hex>7782cdcfbd26f37ea4f93ade227e83a0bcaca996e1941ebc57430bcb32ec4f2b</password_sha256_hex>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <profile>default</profile>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <quota>default</quota>
/var/lib/clickhouse/preprocessed_configs/users.xml-        </veronica>
root@chi-ck3-replicated-0-0-0:/#

Slach · 2024-08-26T14:07:26Z

remove access_management from yaml manifest

darioneto · 2024-08-27T02:51:42Z

I removed it

Defaulted container "clickhouse-pod" out of: clickhouse-pod, clickhouse-backup
root@chi-ck3-replicated-0-0-0:/# grep -C 10 grants -r /var/lib/clickhouse/preprocessed_configs/
/var/lib/clickhouse/preprocessed_configs/config.xml-        <!-- Enables logic that users without permissive row policies can still read rows using a SELECT query.
/var/lib/clickhouse/preprocessed_configs/config.xml-             For example, if there two users A, B and a row policy is defined only for A, then
/var/lib/clickhouse/preprocessed_configs/config.xml-             if this setting is true the user B will see all rows, and if this setting is false the user B will see no rows.
/var/lib/clickhouse/preprocessed_configs/config.xml-             By default this setting is false for compatibility with earlier access configurations. -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <users_without_row_policies_can_read_rows>false</users_without_row_policies_can_read_rows>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml-        <!-- By default, for backward compatibility ON CLUSTER queries ignore CLUSTER grant,
/var/lib/clickhouse/preprocessed_configs/config.xml-             however you can change this behaviour by setting this to true -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <on_cluster_queries_require_cluster_grant>false</on_cluster_queries_require_cluster_grant>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml:        <!-- By default, for backward compatibility "SELECT * FROM system.<table>" doesn't require any grants and can be executed
/var/lib/clickhouse/preprocessed_configs/config.xml-             by any user. You can change this behaviour by setting this to true.
/var/lib/clickhouse/preprocessed_configs/config.xml-             If it's set to true then this query requires "GRANT SELECT ON system.<table>" just like as for non-system tables.
/var/lib/clickhouse/preprocessed_configs/config.xml-             Exceptions: a few system tables ("tables", "columns", "databases", and some constant tables like "one", "contributors")
/var/lib/clickhouse/preprocessed_configs/config.xml-             are still accessible for everyone; and if there is a SHOW privilege (e.g. "SHOW USERS") granted the corresponding system
/var/lib/clickhouse/preprocessed_configs/config.xml-             table (i.e. "system.users") will be accessible. -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <select_from_system_db_requires_grant>false</select_from_system_db_requires_grant>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml:        <!-- By default, for backward compatibility "SELECT * FROM information_schema.<table>" doesn't require any grants and can be
/var/lib/clickhouse/preprocessed_configs/config.xml-             executed by any user. You can change this behaviour by setting this to true.
/var/lib/clickhouse/preprocessed_configs/config.xml-             If it's set to true then this query requires "GRANT SELECT ON information_schema.<table>" just like as for ordinary tables. -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <select_from_information_schema_requires_grant>false</select_from_information_schema_requires_grant>
/var/lib/clickhouse/preprocessed_configs/config.xml-
/var/lib/clickhouse/preprocessed_configs/config.xml-        <!-- By default, for backward compatibility a settings profile constraint for a specific setting inherit every not set field from
/var/lib/clickhouse/preprocessed_configs/config.xml-             previous profile. You can change this behaviour by setting this to true.
/var/lib/clickhouse/preprocessed_configs/config.xml-             If it's set to true then if settings profile has a constraint for a specific setting, then this constraint completely cancels all
/var/lib/clickhouse/preprocessed_configs/config.xml-             actions of previous constraint (defined in other profiles) for the same specific setting, including fields that are not set by new constraint.
/var/lib/clickhouse/preprocessed_configs/config.xml-             It also enables 'changeable_in_readonly' constraint type -->
/var/lib/clickhouse/preprocessed_configs/config.xml-        <settings_constraints_replace_previous>false</settings_constraints_replace_previous>
--
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>127.0.0.1</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>0.0.0.0/0</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-            </networks>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <password_sha256_hex>921443c5e72aac9f10321d52f095edd5ed04ab8deeca8cd0eb425ad46c135c14</password_sha256_hex>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <profile>default</profile>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <quota>default</quota>
/var/lib/clickhouse/preprocessed_configs/users.xml-        </admin>
/var/lib/clickhouse/preprocessed_configs/users.xml-        
/var/lib/clickhouse/preprocessed_configs/users.xml-        <veronica>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <default_database>pantry_assistant</default_database>
/var/lib/clickhouse/preprocessed_configs/users.xml:            <grants>SHOW DATABASES</grants>
/var/lib/clickhouse/preprocessed_configs/users.xml:            <grants>SHOW DATABASES,SELECT,INSERT,ALTER,CREATE TABLE,DROP TABLE,TRUNCATE,OPTIMIZE ON pantry_assistant.*</grants>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <networks>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <host_regexp>(chi-ck3-[^.]+\d+-\d+|clickhouse\-ck3)\.ck\.svc\.cluster\.local$</host_regexp>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>::1</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>127.0.0.1</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-                <ip>0.0.0.0/0</ip>
/var/lib/clickhouse/preprocessed_configs/users.xml-            </networks>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <password_sha256_hex>7782cdcfbd26f37ea4f93ade227e83a0bcaca996e1941ebc57430bcb32ec4f2b</password_sha256_hex>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <profile>default</profile>
/var/lib/clickhouse/preprocessed_configs/users.xml-            <quota>default</quota>
/var/lib/clickhouse/preprocessed_configs/users.xml-        </veronica>

and still the same

SHOW DATABASES

Query id: bb1b7779-0965-4cf5-919a-a3992c6d792a

Ok.

0 rows in set. Elapsed: 0.007 sec. 

chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :) show tables

SHOW TABLES

Query id: b0ba79f7-fdd6-4ac7-be33-e8a42fa81066

Ok.

0 rows in set. Elapsed: 0.004 sec. 

chi-ck3-replicated-0-0-0.chi-ck3-replicated-0-0.ck.svc.cluster.local :) create table test;

CREATE TABLE test

Query id: ae13b649-b50a-43a7-a363-1d58889838c8


0 rows in set. Elapsed: 0.027 sec. 

Received exception from server (version 23.8.12):
Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: veronica: Not enough privileges. To execute this query, it's necessary to have the grant CREATE TABLE ON pantry_assistant.test. (ACCESS_DENIED)

current manifest

spec:
  defaults:
    templates: 
      dataVolumeClaimTemplate: default
      podTemplate: clickhouse:20.7-with-backup  
 
  configuration:
    users: 
      admin/password: xxxx    
      admin/networks/ip: 0.0.0.0/0 
      # admin/access_management: 1

      veronica/password: xxxx
      veronica/networks/ip: 0.0.0.0/0
      veronica/profile: default
      veronica/quota: default
      # veronica/access_management: 0
      veronica/default_database: pantry_assistant
      veronica/grants:
        - SHOW DATABASES
        - SHOW DATABASES,SELECT,INSERT,ALTER,CREATE TABLE,DROP TABLE,TRUNCATE,OPTIMIZE ON pantry_assistant.*
      veronica/grants/databases/pantry_assistant: 1

alex-watchenterprise · 2024-11-05T08:00:17Z

@darioneto not sure if it helps, but in my case, this didn’t work (and it's clear why after I checked the final users.xml):

    metabase/grants:
      - GRANT SELECT ON default.*
      - GRANT SELECT ON metrics.*

but this worked:

    metabase/grants/query:
      - GRANT SELECT ON default.*
      - GRANT SELECT ON metrics.*

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Grant access to users to manage specific databases #1492

Grant access to users to manage specific databases #1492

darioneto commented Aug 19, 2024 •

edited by Slach

Loading

Slach commented Aug 20, 2024

darioneto commented Aug 21, 2024 •

edited

Loading

Slach commented Aug 21, 2024

Philippians413 commented Aug 21, 2024 •

edited

Loading

Slach commented Aug 21, 2024

Philippians413 commented Aug 21, 2024 •

edited

Loading

Slach commented Aug 21, 2024

Philippians413 commented Aug 22, 2024

Slach commented Aug 22, 2024

darioneto commented Aug 23, 2024

Slach commented Aug 26, 2024

darioneto commented Aug 27, 2024

alex-watchenterprise commented Nov 5, 2024 •

edited

Loading

Grant access to users to manage specific databases #1492

Grant access to users to manage specific databases #1492

Comments

darioneto commented Aug 19, 2024 • edited by Slach Loading

Slach commented Aug 20, 2024

darioneto commented Aug 21, 2024 • edited Loading

Slach commented Aug 21, 2024

Philippians413 commented Aug 21, 2024 • edited Loading

Slach commented Aug 21, 2024

Philippians413 commented Aug 21, 2024 • edited Loading

Slach commented Aug 21, 2024

Philippians413 commented Aug 22, 2024

Slach commented Aug 22, 2024

darioneto commented Aug 23, 2024

Slach commented Aug 26, 2024

darioneto commented Aug 27, 2024

alex-watchenterprise commented Nov 5, 2024 • edited Loading

darioneto commented Aug 19, 2024 •

edited by Slach

Loading

darioneto commented Aug 21, 2024 •

edited

Loading

Philippians413 commented Aug 21, 2024 •

edited

Loading

Philippians413 commented Aug 21, 2024 •

edited

Loading

alex-watchenterprise commented Nov 5, 2024 •

edited

Loading