How to setup an admin account for Clickhouse?

Question

I'm running Clickhouse in a docker container on a Windows host. I tried to create an account towards making it an admin account. It looks like the default user does not have permission to create other accounts. How can I get around this error and create an admin account?

docker-compose exec -T dash-clickhouse clickhouse-client --query="CREATE USER 'foo' IDENTIFIED WITH sha256_password BY 'bar'"

gave the error

Received exception from server (version 20.7.2):
Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: default: Not enough privileges. To execute this query it's necessary to have the grant CREATE USER ON *.*.

Answer 1

To fix it need to enable access_management-setting in the users.xml file:

#  execute an interactive bash shell on the container
docker-compose exec {container_name} bash
# docker exec -it {container_name} bash

# install preferable text editor (i prefer using 'nano')
apt-get update
apt-get install nano

# open file users.xml in the editor
nano /etc/clickhouse-server/users.xml

Uncomment the access_management-setting for the default user and save changes:

..
<!-- User can create other users and grant rights to them. -->
<!-- <access_management>1</access_management> -->
..

Answer 2

mkdir -p  ~/Documents/docker/click/etc/conf.d

cat ~/Documents/docker/click/etc/conf.d/zxylalalhuayk.xml
<?xml version="1.0" ?>
<yandex>
    <users>
        <default>
     <access_management>1</access_management>
        </default>
    </users>
</yandex>


docker run -d --name test  -v ~/Documents/docker/click/etc/conf.d/:/etc/clickhouse-server/conf.d --ulimit nofile=262144:262144  -p 8123:8123 yandex/clickhouse-server

Answer 3

If you're running clickhouse-server via the image from docker hub, the easiest way would be to pass in the login via environment variables

How to create default database and user on starting

Sometimes you may want to create a user (user named default is used by default) and database on a container start. You can do it using environment variables CLICKHOUSE_DB, CLICKHOUSE_USER, CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT and CLICKHOUSE_PASSWORD:

docker run --rm -e CLICKHOUSE_DB=my_database -e CLICKHOUSE_USER=username -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 -e CLICKHOUSE_PASSWORD=password -p 9000:9000/tcp clickhouse/clickhouse-server

If you leave these out, clickhouse gives you a default user which can do everything... except SQL-driven access control and account management. That's what the CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 allows.

https://hub.docker.com/r/clickhouse/clickhouse-server/