How to secure Clickhouse via the default user?

Question

I have a clickhouse database someone else has installed. I am able to connect to it via the

clickhouse-client

or the

clickhouse-client -u default

command and that connects the default user to Clickhouse. However, I would like to change the way we connect to Clickhouse and apply a password. Since I'm not confident yet, I intend to create a super user with a username and a pasword (I want to keep default temporarily and only remove/change it when I'm confident with this new user). However,

CREATE USER IF NOT EXISTS someuser
IDENTIFIED WITH sha256_password BY 'somepassword';

fails because default does not have the necessary privileges:

DB::Exception: default: Not enough privileges. To execute this query it's necessary to have grant CREATE USER ON .. (ACCESS_DENIED)

Not even

SHOW USERS;

works. How can I make sure that we secure clickhouse connection with a password?

Answer 1

Your default user just doesn't have permission to create users using SQL. Create a new XML file (name it something like my_users.xml) and place it in your config.d folder, and grant the default user permission to create new users using SQL (instead of XML configs) by enabling the access_management property:

<clickhouse>
  <users>
        <default>
            <password/>
            <access_management>1</access_management>
        </default>
    </users>
</clickhouse>

You can also give your new user access_management permission. And you can remove the default user with the following in your config:

    <users>
        <default remove="remove">
        </default>
        
        <someuser>
           ...
        </someuser>
    </users>