search

Altinity / clickhouse-operator

Altinity Kubernetes Operator for ClickHouse creates, configures and manages ClickHouse clusters running on Kubernetes
https://altinity.com
Apache License 2.0
1.86k stars 455 forks source link

Grant access to users to manage specific databases #1492

Open darioneto opened 3 weeks ago

darioneto commented 3 weeks ago

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 commented 3 weeks ago

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 commented 3 weeks ago

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 commented 3 weeks ago

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 commented 3 weeks ago

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 commented 3 weeks ago

@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 commented 3 weeks ago

@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 commented 3 weeks ago

@Philippians413 sorry missed users section

spec:
  configuration:
    users:
      user_name/grants:
      - <GRANT_NAME> ON db.table
Philippians413 commented 3 weeks ago

@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 commented 3 weeks ago

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

darioneto commented 3 weeks ago

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 commented 2 weeks ago

remove access_management from yaml manifest

darioneto commented 2 weeks ago

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