Gotcha

ClickHouse Code: 516 Authentication failed: where the default user's password came from, and when it is not about the password at all

· By OtezVikentiy

ClickHouse is freshly installed, you run the first command, and instead of an answer you get:

Code: 516. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.

You never set a password, and the default user certainly exists. Most of the time there is a password after all — the package asked you for one during installation. But sometimes the password has nothing to do with it: ClickHouse answers with the very same line when the user is not allowed to connect from your address. Below is how to tell which case you have in a minute.

One error for three different causes

ClickHouse sends the client the same text whatever went wrong. The source code does this on purpose: the server should not tell a stranger whether a user exists. Behind 516 there are three different refusals:

The real cause goes to the server log. That is the first place to look:

sudo grep -E 'user: .*: Authentication failed' /var/log/clickhouse-server/clickhouse-server.log | tail -5

In Docker the file is inside the container, so run the command through docker exec <container>. The line carries the client address, the user name that actually reached the server, and the inner error:

In the logWhat it means
Invalid credentialsthe user was found, the password did not match
There is no user `name` in ...there is no such user
Connections from <address> are not allowedthe address is not in the user’s networks

The user name in the log is useful on its own: if you connect through a DSN or a driver, it shows what really arrived at the server.

For the default user the server appends a hint: the password usually lives in /etc/clickhouse-server/users.d/default-password.xml, and deleting that file resets it. That is true for the first case and not for the other two.

Where a password you never set comes from

After installation the clickhouse-server package runs clickhouse install, and that program asks on its own:

Set up the password for the default user:

The question appears only when output goes to a terminal and DEBIAN_FRONTEND is not noninteractive. The password is read straight from the terminal, so on Debian and Ubuntu the question comes up with both apt install and dpkg -i. The rpm package runs the same post-install script, but dnf sends package script output to its own log rather than the terminal, so dnf install on AlmaLinux, Rocky and RHEL does not ask and leaves the password empty (checked with dnf 4). Only rpm -i run straight from a terminal can ask.

Then there are two outcomes:

The trap is that this question is easy to miss or to answer on autopilot. The official guide mentions the password in a single line, and third-party guides often say nothing at all: the install runs in a live terminal, someone types a password — and the next step, say clickhouse-client --query "CREATE DATABASE ..." without a password, fails with 516. Our own guide worked that way before version 1.7.1, more on that below. The ClickHouse source has a comment right next to the hint text saying exactly that: it is typical to install, type a password and forget it at once.

The second source is a previous installation. If ClickHouse was already on the host and the password file survived, a reinstall sees that a password is set and does not ask again: Password for the default user is already specified.

To check where the password is set:

sudo ls /etc/clickhouse-server/users.d/
sudo grep -rlE '<password[a-z0-9_]*>[^<]' /etc/clickhouse-server/users.d/

In the stock users.xml the default password is empty unless someone edited the file by hand. Files in users.d/ are merged over users.xml in alphabetical order of their names, so default-password.xml overrides the empty password from users.xml: it contains <password remove='1' /> and a <password_sha256_hex>.

How to log in or reset the password

You know the password. Pass it to the client. With --password and no value the client asks for it, and it stays out of the shell history:

clickhouse-client --password

You do not know the password. It cannot be recovered from SHA-256. There are two ways out.

Delete the file — the default password becomes empty again:

sudo rm -f /etc/clickhouse-server/users.d/default-password.xml
sudo systemctl restart clickhouse-server

This is safe only while the server listens on localhost. That is the default in config.xml, but it is not the only place to check: right after the password, the same clickhouse install asks Allow server to accept connections from the network, and on y writes <listen_host>::</listen_host> to config.d/listen.xml. To check:

sudo grep -r '<listen_host>' /etc/clickhouse-server/config.d/

If the server is open to the network, then after deleting the file default has no password and is reachable from anywhere: in the stock users.xml its networks allows any address. In that case set a new password instead of deleting the file:

PASSWORD=$(openssl rand -hex 16)
echo "$PASSWORD"   # save it: it cannot be recovered from the hash
HASH=$(printf '%s' "$PASSWORD" | sha256sum | awk '{print $1}')
sudo tee /etc/clickhouse-server/users.d/default-password.xml >/dev/null <<EOF
<clickhouse>
    <users>
        <default>
            <password remove='1' />
            <password_sha256_hex>$HASH</password_sha256_hex>
        </default>
    </users>
</clickhouse>
EOF
sudo systemctl restart clickhouse-server

Note the printf '%s'. A common reason for 516 right after a password change is a hash computed with echo "$PASSWORD" | sha256sum: echo appends a newline, and the config gets the hash of a different password. That is why the ClickHouse docs use echo -n.

The prompt should never appear — in scripts, Ansible and CI install the packages non-interactively and set the password explicitly with a file, as above:

sudo DEBIAN_FRONTEND=noninteractive apt-get install -y clickhouse-server clickhouse-client

On the RHEL family dnf install does not ask in the first place.

If the user was created with SQL CREATE USER, it is stored not in XML but in /var/lib/clickhouse/access/, and its password is changed with ALTER USER.

Docker: default is cut off from the network although there is no password

In the official clickhouse/clickhouse-server image 516 arrives for a different reason. If none of CLICKHOUSE_USER, CLICKHOUSE_PASSWORD and CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT is set and mounted configs do not change default, the image’s entrypoint lets the default user connect only from 127.0.0.1 and ::1. Inside the container clickhouse-client works, while the application in a neighbouring container gets 516 — even though nobody has a password. The server log then says Connections from <container address> are not allowed, not Invalid credentials.

What to do about it:

The image rewrites its own file, users.d/default-user.xml, on every container start, not just the first one. Change CLICKHOUSE_PASSWORD in compose and recreate the container — the password changes, even if the data sits in an old volume. Other files you mounted into users.d/ yourself are left alone.

The password is right and 516 still comes back

Step by step

  1. Look at the name in the error: default: or your own user. That tells you whose password or whose restrictions to check.
  2. Find the Authentication failed line in the server log — it names one of the three causes.
  3. Invalid credentials — find where the password is set: users.d/, users.xml or SQL. Log in with the password or set a new one.
  4. There is no user — check the name in the DSN, and whether the Docker image removed default.
  5. Connections from ... are not allowed — widen the user’s networks, or set a password in the Docker image.

How Gotcha handles it

Gotcha, a self-hosted observability platform, keeps its events in ClickHouse and ran into exactly this package prompt on installs without Docker:

So the two possible 516s differ by the name in the message: default: is the database creation step, while gotcha: most often means the password in the DSN does not match the user’s password. Both cases, with recipes, are covered in the “Common issues” section of the docs.