clickhouse设置密码登录以及只读账号

设置明文密码

• cd到/etc/clickhouse-server目录
• 编辑users.xml文件

查找到<user>的标签里面，找到<password>

<users>
        <!-- If user name was not specified, 'default' user is used. -->
        <default>
            <!-- Password could be specified in plaintext or in SHA256 (in hex format).

                 If you want to specify password in plaintext (not recommended), place it in 'password' element.
                 Example: <password>qwerty</password>.
                 Password could be empty.

                 If you want to specify SHA256, place it in 'password_sha256_hex' element.
                 Example: <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>

                 How to generate decent password:
                 Execute: PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
                 In first line will be password and in second - corresponding SHA256.
            -->
            <password></password>

            <!-- List of networks with open access.

                 To open access from everywhere, specify:
                    <ip>::/0</ip>

                 To open access only from localhost, specify:
                    <ip>::1</ip>
                    <ip>127.0.0.1</ip>

                 Each element of list has one of the following forms:
                 <ip> IP-address or network mask. Examples: 213.180.204.3 or 10.0.0.1/8 or 2a02:6b8::3 or 2a02:6b8::3/64.
                 <host> Hostname. Example: server01.yandex.ru.
                     To check access, DNS query is performed, and all received addresses compared to peer address.
                 <host_regexp> Regular expression for host names. Example, ^server\d\d-\d\d-\d\.yandex\.ru$
                     To check access, DNS PTR query is performed for peer address and then regexp is applied.
                     Then, for result of PTR query, another DNS query is performed and all received addresses compared to peer address.
                     Strongly recommended that regexp is ends with $
                 All results of DNS requests are cached till server restart.
            -->
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>

            <!-- Settings profile for user. -->
            <profile>default</profile>

            <!-- Quota for user. -->
            <quota>default</quota>
        </default>

在<password>之间创建密码，然后重启

$ /etc/init.d/clickhouse-server restart

设置sha256加密的密码

• 还是上面的那个文件

  将<password>改为<password_sha256_hex>，之间填加密之后的东西

• 如何加密？

$ PASSWORD=$(base64 < /dev/urandom | head -c8);
$ echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'

• 生成大概如下：

1Ng71p8I
378cd552637f2c779c25076bee090eaae3700c1be7e8ac4da82c60fba833b4c8

第一行为明文密码，第二行为加密之后的密码，在<password_sha256_hex>之间填入加密之后的密码

登录

$ clickhouse-client -u default --password=1Ng71p8I -m

设置只读账号：

• 添加一个比如ck的用户
  在<user></user>中添加：

<ck>
    <readonly>
            <password>1Ng71p8I</password>
            <networks incl="networks" replace="replace">
                <!--<ip>::1</ip>-->
                <ip>127.0.0.1</ip>
            </networks>
            <profile>readonly</profile>
            <quota>default</quota>
    </readonly>
</ck>

重启

• 修改了配置文件之后，需要重启

$ /etc/init.d/clickhouse-server restart