ClickHouse用户资源隔离在 GrowingIO 的实践

业务场景

Hello World

── conf
   ├── config.xml
   └── users.xml

clickhouse server --config=/etc/clickhouse/conf/config.xml

<?xml version="1.0"?>
<yandex>
    <logger>
        <level>trace</level>
        <log>/tmp/log/clickhouse-server.log</log>
        <errorlog>/tmp/log/clickhouse-server.err.log</errorlog>
        <size>1000M</size>
        <count>10</count>
    </logger>
    <query_log>
        <database>system</database>
        <table>query_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>1000</flush_interval_milliseconds>
    </query_log>
    <tcp_port>9000</tcp_port>
    <listen_host>127.0.0.1</listen_host>
    <access_control_path>/tmp/ledzeppelin/</access_control_path>
    <max_concurrent_queries>500</max_concurrent_queries>
    <mark_cache_size>5368709120</mark_cache_size>
    <path>./clickhouse/</path>
    <users_config>users.xml</users_config>
</yandex>

<?xml version="1.0"?>
<yandex>
    <profiles>
        <default>
            <readonly>1</readonly>
        </default>
        <pA>
            <max_memory_usage>10G</max_memory_usage>
            <max_memory_usage_for_user>10G</max_memory_usage_for_user>
            <max_memory_usage_for_all_queries>10G</max_memory_usage_for_all_queries>
            <max_query_size>1073741824</max_query_size>
            <readonly>1</readonly>
        </pA>
    </profiles>
    <users>
        <zeppelin>
            <!-- <password_sha256_hex></password_sha256_hex> -->
            <password>password</password>
            <networks>
                <ip>::/0</ip>
            </networks>
            <profile>pA</profile>
            <quota>qA</quota>
        </zeppelin>
    </users>
    <quotas>
        <qA>
            <interval>
                <!-- 以秒为单位 -->
                <duration>10</duration>
                <!-- 10 秒内只能查询一次 -->
                <queries>2</queries>
                <errors>0</errors>
                <result_rows>0</result_rows>
                <read_rows>0</read_rows>
                <execution_time>0</execution_time>
            </interval>
        </qA>
    </quotas>
</yandex>

Quotas 和 Profile

SQL 驱动的访问控制

<user>
    <default>
        <!-- 其他配置 -->
        <access_management>1</access_management>
    </default>
</user>

CREATE USER ledzeppelin;
GRANT ALL ON *.* TO ledzeppelin WITH GRANT OPTION;
CREATE USER zeppelin;

CREATE USER MJ IDENTIFIED WITH sha256_password BY 'qwerty';

clickhouse-client -u MJ --password qwerty

clickhouse-client -u MJ --password ${CLICKHOUSE_MJ_PASSWORD}

REVOKE ALL ON *.* FROM default


Query id: 7ab4a62a-62d1-4d0b-bbfe-c27d0e595f65




0 rows in set. Elapsed: 0.002 sec. 


Received exception from server (version 21.10.2):
Code: 495. DB::Exception: Received from localhost:9000. DB::Exception: Cannot update user `default` in users.xml because this storage is readonly: Couldn't update user `default`. Successfully updated: none. (ACCESS_STORAGE_READONLY)

<profiles>
        <!-- Profile that allows only read queries. -->
        <readonly_profile>
            <readonly>2</readonly>
            <!-- 其他配置 -->
        </readonly_profile>
    </profiles>

<users>
    <profile> readonly_profile</profile>
    <!-- 其他配置 -->
</users>

<access_management>1</access_management>

CREATE SETTINGS PROFILE IF NOT EXISTS z_profile
SETTINGS
readonly = 2
READONLY

CREATE QUOTA IF NOT EXISTS z_quota
FOR INTERVAL 10 second
  MAX queries 1

CREATE ROLE IF NOT EXISTS z_role

GRANT SELECT ON db.* TO z_role;

CREATE ROLE IF NOT EXISTS gc_role

ALTER SETTINGS PROFILE z_profile TO z_role
ALTER QUOTA z_quota TO z_role;

SELECT * FROM system.role_grants WHERE user_name LIKE 'zeppelin'

GRANT z_role TO zeppelin

CREATE QUOTA IF NOT EXISTS z_quota_5
FOR INTERVAL 5 second
  MAX queries 1

ALTER QUOTA z_quota TO gc_role;

ALTER QUOTA z_quota_5 TO z_role;

revoke z_role from zeppelin;
grant z_role to zeppelin;

SELECT
    name,
    apply_to_list
FROM system.quotas
WHERE name LIKE 'z_quota_5'


Query id: dc5b2ece-c792-4585-95d8-8b4275631664


┌─name──────┬─apply_to_list─┐
│ z_quota_5 │ ['z_role']    │
└───────────┴───────────────┘


1 rows in set. Elapsed: 0.004 sec. 

CREATE SETTINGS PROFILE IF NOT EXISTS z_profile_permission_type_0
SETTINGS
    readonly = 0
READONLY

ALTER SETTINGS PROFILE z_profile TO gc_role

ALTER SETTINGS PROFILE z_profile_permission_type_0 TO z_role

revoke z_role from zeppelin;
grant z_role to zeppelin;

SELECT
    name,
    apply_to_list
FROM system.settings_profiles


Query id: 51d1c2de-ced7-4558-a164-020a76a53d97


┌─name────────────────────────┬─apply_to_list─┐
│ readonly                    │ []            │
│ default                     │ []            │
│ z_profile                   │ ['gc_role']   │
│ z_profile_permission_type_0 │ ['z_role']    │
└─────────────────────────────┴───────────────┘

5.SQL驱动设置持久化

<yandex>
...
    <access_control_path>/var/lib/clickhouse/access/ledzeppelin/</access_control_path>
...
</yandex

╭─/var/lib/clickhouse/access/ledzeppelin  
╰─➤  ls
094e5b76-1b4d-97a5-43cd-bbb3ccceb688.sql  9c492851-bf60-7753-7c14-c05f4be46b8e.sql
2949a246-848f-fc07-f1b9-a3f7246b31a0.sql  f340165d-0ec4-f90e-fb55-74c8d0cfd7ae.sql
394e47fd-b701-d017-6857-0d8744d830b1.sql  quotas.list
3d44c8a1-01b3-6f68-eff7-1e20a9080296.sql  roles.list
3f447b54-b515-aa93-bacc-1a649aeac19c.sql  row_policies.list
674912da-5593-e497-c5d8-d058e916c7a5.sql  settings_profiles.list
6c44ad5c-4354-c154-048e-8e293ff427ab.sql  users.list

结论

参考