Skip to main content

How to remove the default user

· 2 min read

In this guide, we're going to learn how to remove the default user from ClickHouse Server.

We can do this by creating a YAML file (let's call it remove_default_user.yaml) that has the following content

users:
  default:
    "@remove": remove

The location of this file depends on how we have ClickHouse installed.

Running the executable directly​

If we're running the ClickHouse directly (clickhouse server), we need to put the file under the config.d directory.

When we run ClickHouse Server:

clickhouse server

We'll see the following line in the logs:

{} <Debug> ConfigProcessor: Merging configuration file 'config.d/remove_default_user.yaml'.

And we'll be unable to connect using clickhouse client:

ClickHouse client version 24.11.1.2557 (official build).
Connecting to localhost:9000 as user default.
Password for user (default):
Connecting to localhost:9000 as user default.
Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.

Docker or installed​

If we're running ClickHouse via Docker or have it installed on our machine, we need to put the file under the /etc/clickhouse-server/users.d directory instead.

So if we're running with Docker, we can mount the config.d directory that we created earlier to /etc/clickhouse-server/users.d:

docker run \
  -v ./config.d:/etc/clickhouse-server/users.d \
  -p 8123:8123 -p9000:9000 \
  clickhouse/clickhouse-server:24.12
Merging configuration file '/etc/clickhouse-server/config.d/docker_related_config.xml'.
Logging trace to /var/log/clickhouse-server/clickhouse-server.log
Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log

We can then search the server logs to check that it gets picked up:

docker ps --format "table {{.ID}}\t{{.Image}}\t{{.Names}}\t{{.Command}}"
CONTAINER ID   IMAGE                                NAMES               COMMAND
383e8ed89431   clickhouse/clickhouse-server:24.12   trusting_rosalind   "/entrypoint.sh"
docker exec -it trusting_rosalind grep "users\.d" /var/log/clickhouse-server/clickhouse-server.log

We should see the following line:

{} <Debug> ConfigProcessor: Merging configuration file '/etc/clickhouse-server/users.d/remove_default_user.yaml'.

Why ClickHouse default logging setting is verbose?

· One min read

One thing that often confuses new users is that ClickHouse generates a lot of log output, even under light load.

This is because the default log level is for historical reasons trace (instead of warning that would be the default in other databases).

The ClickHouse developers argue that trace provides a lot of insight in case goes wrong.

On the other hand, large volumes of logging means that system table system.text_log fills up quickly and needs to be merged in the background.

If the database runs stable, users may re-configure the log level.

How to insert all rows from one table to another?

· 2 min read

Introduction​

Sometimes you need to reingest all the data from one table to another.

For example, you might want to reingest data from a staging table to a production table. This article shows how to do this using the INSERT INTO statement.

Why is my primary key not used? How can I check?

· 6 min read

Users may see cases where their query is slower than expected, in the belief they are ordering or filtering by a primary key. In this article we show how users can confirm the key is used, highlighting common reasons its not.

How to Enable SSL with Let's Encrypt on a Single ClickHouse Server

· 6 min read

The following steps can be used to enable SSL for a single ClickHouse Server using Let's Encrypt, a free, automated, and open Certificate Authority (CA) designed to make it easy for anyone to secure their websites with HTTPS. By automating the certificate issuance and renewal process, Let's Encrypt ensures websites remain secure without requiring manual intervention.