[Clickhouse] Clickhouse 패스워드 규칙 설정

[ 개요 ]

* Clickhouse 계정 패스워드 규칙 설정 적용

 

[ 설정 적용 전, 계정 생성 ]

1. test01 계정 생성

CREATE USER [IF NOT EXISTS | OR REPLACE] <user name> [ON CLUSTER <cluster name>] IDENTIFIED BY 'password string';

CREATE USER test01 IDENTIFIED BY '1234';

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       │ sha256_password │ {}          │ ['::/0'] │ []         │ []                │ []              │                 1 │ []                 │ []                   │            1 │ []            │ []              │                  │
│ test01  │ 5744f091-3d82-ac72-8715-59ae333bcf98 │ local directory │ sha256_password │ {}          │ ['::/0'] │ []         │ []                │ []              │                 1 │ []                 │ []                   │            1 │ []            │ []              │                  │
└─────────┴──────────────────────────────────────┴─────────────────┴─────────────────┴─────────────┴──────────┴────────────┴───────────────────┴─────────────────┴───────────────────┴────────────────────┴──────────────────────┴──────────────┴───────────────┴─────────────────┴──────────────────┘

* 정상적으로 생성되는 것 확인

* 이제 규칙을 설정해 보자

 

[ 규칙 적용 ]

1. /etc/clickhouse-server/config.xml 파일에 규칙 적용

   <password_complexity>
        <rule>
            <pattern>.{8}</pattern>
            <message>be at least 8 characters long</message>
        </rule>
        <rule>
            <pattern>\p{N}</pattern>
            <message>contain at least 1 numeric character</message>
        </rule>
        <rule>
            <pattern>\p{Ll}</pattern>
            <message>contain at least 1 lowercase character</message>
        </rule>
        <rule>
            <pattern>\p{Lu}</pattern>
            <message>contain at least 1 uppercase character</message>
        </rule>
        <rule>
            <pattern>[^\p{L}\p{N}]</pattern>
            <message>contain at least 1 special character</message>
        </rule>
    </password_complexity>

* 규칙 적용 후, clickhouse-server 재 기동 필요

 

[ 규칙 적용 후, 계정 생성 ]

click01.test.com :) CREATE USER test03 IDENTIFIED BY '1234';

CREATE USER test03 IDENTIFIED WITH sha256_hash BY '463C16785B62949B7FC5FB03ADB096491FBC6F98C77AC0254E1DD59EAA26B31B' SALT 'E6D2A23AA08FFC65341A6B7DA95A407E6373496E6F731DA6D87B13DC155D47E9'

Query id: 4a330b41-2759-4c48-a19a-7d1cbbd1b870

Exception on client:
Code: 36. DB::Exception: Invalid password. The password should: be at least 8 characters long, contain at least 1 lowercase character, contain at least 1 uppercase character, contain at least 1 special character. (BAD_ARGUMENTS)

* 규칙에 위반되어 생성되지 않는 것을 확인