Why securing the ClickHouse default user comes first
Securing the ClickHouse default user should be the first access control you change on a self-managed cluster, because the default user starts with all permissions. A default user with full rights means that any client able to reach the server on an account with no password, or with a password you never set, can read, write and alter data and change server configuration. Restricting it, either by setting a strong password or disabling it, and then moving day-to-day access to named accounts, is the baseline the ClickHouse access control documentation recommends. The reader's decision is not whether to do this but when, and the answer is before any non-test data is loaded and before any application or analyst gets network access to the cluster.
The default user is the built-in account that ClickHouse uses when a connection does not specify another user. Its permissions are broader than any account you would give an application or an analyst, so a single weak or shared credential on that account gives an attacker the whole server. The risk grows with every path into the cluster: HTTP and native ports, the replication traffic between nodes and any monitoring or ingestion tools that connect. Teams often leave the account in place during early testing and forget it when the cluster starts holding production event, log or telemetry data. Before go-live, confirm which ports are reachable, which clients use which account, and whether any configuration file still relies on the default account without a password.
The change itself is small, but it touches several places, so plan it as a short project. Decide whether the default user will keep a strong password for break-glass use or be disabled, then create a named administrator and check that it can perform the tasks the team needs. The documentation describes a SQL-driven workflow for users, roles and grants, which lets you manage access with statements rather than editing server files by hand. Test the new accounts from every client path before you remove the old access, and record who holds administrative rights. Confirm the exact settings for your ClickHouse version against the access control documentation before you apply them to a production cluster.
What makes the default user dangerous
The default user is the account a fresh ClickHouse server gives you before anyone has designed access. Each risk below is a different way that one shared, fully privileged account weakens the server, so the checks focus on what your environment actually exposes.
Full permissions from the start
The default user begins with all permissions, so any session that authenticates as it can create databases, drop tables, change settings and manage other users. Check whether any application, script or dashboard connects as default, and list what that account is allowed to do with SHOW GRANTS. The single question to answer is whether a compromised client could alter the whole server.
Open access on exposed networks
If the default user has no password, or a password that is weak or stored in a widely shared file, anyone who can reach the HTTP or native port can log in. Network exposure makes this worse, because a listener bound to all interfaces turns a configuration gap into internet-reachable access. Check the listen_host setting, the firewall rules in front of ports 8123 and 9000, and whether the password is set at all.
Shared credentials across tools
When one password serves dashboards, ETL jobs, notebooks and administrators, rotating it breaks every consumer at once, so the password tends to stay unchanged for years. Copies also spread into configuration repositories, CI variables and shell history. Inventory every place the credential is stored, and identify which service really needs full rights and which needs only read access to a few tables.
Weak attribution in audit logs
If many people and services log in as the same account, query_log records show who ran a statement only as default, not as a person or workload. Incident responders then cannot tell which team caused a bad delete or an unexpected spike in reads. Check whether your query logs can identify a specific client or user, and whether each tool has its own named account.
Lockout risk during the change
Restricting the default user can break the systems that still depend on it, and removing its access before a named admin exists can strand the team. Plan the change in order: create a named administrator, move each client to its own account, test the connections, then restrict the default user. Confirm who holds emergency access and where that recovery path is documented.
Ways to lock down the default user compared
This table compares four ways to handle the default user: setting a strong password, disabling it, restricting its networks in users.d, and keeping it as a break-glass account. Read each row against your own admin workflow, since most production clusters combine two or more of these options. The main takeaway is that none of them removes the need for a named admin account and SQL-driven access management.
| Option | What it protects against | What it does not cover | Confirm before choosing |
|---|---|---|---|
| Set a strong password on the default user | Anonymous or guessed logins to the default account from any client that can reach the port | Broad permissions: the default user still starts with all permissions, and a leaked password grants full control | Which tools, scripts and deployment jobs authenticate as default, and how they will receive the new password |
| Disable the default user | Use of a well-known account name, and the risk of a password that was never rotated or was stored in configuration | Whether automation still depends on it; a disabled account breaks any job that logs in as default | A named admin exists and works, every service has its own user, and you have tested the change on a non-production cluster |
| Restrict default user networks in users.d | Logins to the default account from networks outside the admin subnet, such as application or internet ranges | Logins from inside the allowed network, and any process on a permitted host that knows the credentials | The exact admin and monitoring source addresses, how load balancers and proxies affect the client address seen by the server, and every node in the cluster |
| Keep only as a break-glass account | Lock-out during a failed migration or lost admin credential, with a secret held offline | Routine use, audit trail gaps if the account is used, and the risk that people start using it for daily work | Who holds the sealed credential, how use is detected and logged, and the procedure for rotating the password after each use |
Steps to set a password, restrict networks and create a named admin
This sequence moves a self-managed ClickHouse cluster off its open default user without locking out the people and tools that depend on it. Each step produces a named admin, a tested grant set, and a default user that is either protected by a password or disabled and limited to approved networks.
Inventory every login that uses the default user
Before changing anything, list each application, pipeline, dashboard, script and operator account that connects as default, along with the source addresses and protocols they use (HTTP, native, or others). Check the server query logs and the configuration of each client, and record which of them need administrative rights and which only read or write data. The step is done when you have a written list that the platform owner and the application owners have both reviewed. Any client you miss will fail at the password or network change, so confirm this list with each team that owns a connection.
Create a named admin account in SQL
Connect as the default user from an approved network and create a separate named administrator with
CREATE USERusing the SQL-driven access control workflow described in the access control documentation. Store its password in your secrets manager, not in SQL scripts or shell history. Keep the account name specific to a person or a controlled service so later audits can trace actions. The check is thatSHOW CREATE USERreturns the expected definition and that the account can log in from a client session, even though it cannot yet do much else.Grant the admin the privileges it needs
Grant the named admin only the privileges it needs to manage users, roles, row policies, quotas and settings profiles, using
GRANTstatements. Where possible, use a dedicated administrative role and assign that role to the admin, so privileges can be reviewed in one place. Avoid giving it data-reading rights unless operators need them for a specific task. The check is thatSHOW GRANTS FORlists exactly the intended privileges and that a test session can run an access-control command, such as listing users, without an access-denied error.Test the admin login and its management rights
Open a new session as the named admin from each network where administrators work, and confirm that it can perform the management tasks it was granted. Also confirm it cannot perform tasks outside its grants, such as dropping a database it should not control. Repeat the login over the protocol your operators use. This step is complete when the positive and negative tests both behave as the grant design says, and the results are recorded with the change ticket. Do not proceed to the default user change until this passes.
Move each client to a named account
Update every client on the inventory list so that it authenticates with its own named account, or with a limited application account created for its needs, rather than the default user. Roll the changes out in the order that the inventory suggests, keeping the default user working until all clients have moved. The check is that the server logs show no new connections as default from the expected clients over an agreed observation window, and that dashboards, pipelines and scripts still return their usual results.
Set a password or disable the default user
Once no legitimate client depends on default, either assign it a strong password through the configuration files used in your deployment, or disable it according to the method the access control documentation describes for your ClickHouse version. Confirm the exact configuration keys in the official documentation before editing files, and apply the change to every node, since a single node left open can still accept logins. The check is that a login attempt as default with no password or the old credentials is rejected on each node.
Restrict the default user's networks in users.d
If you keep the default user, restrict the networks it can connect from using a file in the
users.ddirectory, with thenetworkssection limited to approved management addresses. Apply the file to every node and keep the same content across the cluster. Test from an approved address, which should succeed, and from an unapproved address, which should be rejected. The step is done when the rejection appears in the server logs for the unapproved test and the configuration is stored under version control with the rest of your ClickHouse settings.
How SQL-driven access management changes day-to-day administration
Once the default user is locked down, the administrative model you choose determines how every later access change is made. In ClickHouse, users, roles, row policies, settings profiles and quotas can all be defined in users.xml or created and changed with SQL statements such as CREATE USER, CREATE ROLE, GRANT and CREATE ROW POLICY. The access control documentation, linked below, recommends the SQL-driven workflow. The practical reason is that XML-defined users live in configuration files on each node, so every change means editing files, distributing them and restarting or reloading, while SQL-managed access is stored in ClickHouse itself and can be applied through one connection and reviewed with SHOW ACCESS. Read the access control documentation before you decide, and confirm with your team how your deployment tooling will handle both models during the transition.
The trade-off is mainly about who controls change and how it is audited. XML-defined users are easy to keep in version control and are useful for bootstrap accounts, such as the named admin you create before you disable the default user. SQL-managed access is easier to delegate, script and review, but it depends on the admin account being protected, because anyone with grant privileges can change who can read what. Plan for that by keeping the named admin separate from analyst and ingestion accounts, so that the account that changes grants is not the account that runs dashboards or pipelines. Decide early which changes must go through a reviewed ticket and which can be made directly, and confirm that your audit process captures the SQL statements or the system tables that record them.
For the admin account, the decision is which privileges it keeps and which it hands off. A common pattern is an administrative user that can manage users and roles but does not run ingestion or analyst queries, paired with read-only analyst roles and separate ingestion roles granted only the tables they write. Row policies and quotas attach to the same model, so the admin account is also the one that should own them. Ask your ClickHouse operator or partner to walk through the grants on a test cluster and to confirm which privileges your chosen version requires for each statement, because the exact grant set depends on the version you run.
Security and operational considerations after the change
Disabling or restricting the default user is only the first step. The controls below decide whether the new admin path stays protected over time, and each one needs an owner and a recurring check. Confirm each item against your own identity process and change records before you sign off the lockdown.
Store and rotate the break-glass credential
Once the default user is locked down, the named admin account and any emergency login become the only way back in during an outage. Decide where that credential is stored, who can retrieve it, and how it is rotated after each use. Confirm that the emergency path is tested on a non-production cluster, and that the vault or sealed-envelope process records every retrieval. A break-glass account nobody has rehearsed often fails at the moment it is needed.
Keep the default user out of configs
Application connection strings, monitoring agents, ingestion pipelines and scheduled jobs sometimes still point at the default account because it was the one that worked during setup. Search your application repositories, Kubernetes secrets, Prometheus exporter configs and CI variables for the default username before you close the change. Replace each reference with a named service account that has only the grants it needs. Confirm that no dashboard or script embeds a password for the default user.
Review grants with SHOW ACCESS
Permissions drift as teams add roles, grant extra privileges to get a job working, and leave old accounts behind. Run SHOW ACCESS on a schedule and compare the output against the access list your data owners approved. Look for accounts with administrative grants that should be read-only, roles granted to users who have left, and any account still allowed to connect from an unrestricted network. Record the review date and the reviewer so the result can be shown to an auditor.
Audit who can create users
The ability to create users and grant access is the most powerful permission in the server, because one account with it can grant itself everything else. Limit account creation to a small named group, and log every CREATE USER, ALTER USER and GRANT statement. Confirm that these statements appear in the system logs you retain and that someone reviews them. Treat any unexpected grant as an incident until the change is explained.
Tie admin accounts to your identity process
A named admin account should exist only while a person holds the role, so its creation, approval and removal should follow the same joiner, mover and leaver steps as your other privileged access. Decide whether admin accounts are local SQL users or come through an external authenticator such as LDAP or JWT, and confirm which option your identity team can revoke centrally. Document who approves each admin, how long access lasts, and how removal is verified. Confirm the mapping with your identity owner before rollout.
Questions teams ask before securing the default user
Can our own team lock down the ClickHouse default user without a partner?
Yes, most teams with Linux and ClickHouse experience can do the core change themselves: set a password or disable the default user, restrict its network access in users.d, and create a named admin. The work is small in code but touches every node and every client, so the risk sits in the rollout. Teams often bring in outside help when they run several clusters, lack an inventory of which applications connect as default, or need evidence for an assessment. Whichever path you choose, ask for the change plan in writing before any node is touched.
How long does it take to secure the default user?
The duration depends mainly on how many clusters and nodes you run, how many clients authenticate as default, and how many change windows your organization allows. A single cluster with a short client list is far simpler than a multi-shard estate where each application has its own credentials. No timeline figure is supplied for this work, so ask any provider, including DeployOpen, to state the duration and the assumptions behind it in a written scope. The inventory of clients is usually the longest step, not the configuration itself.
What does the work need from our team?
Expect to provide three things. First, an inventory of clients, services and scripts that connect to ClickHouse, including the user name and network path each one uses. Second, a change window on each cluster where a brief restart or configuration reload is acceptable, agreed with the owners of the workloads that depend on it. Third, an identity owner who decides how the named admin account is authenticated, whether by local password, LDAP, Kerberos or another external authenticator, and who holds the credential. Missing any of these usually extends the engagement.
Does disabling the default user break our clusters?
It can, if something still depends on it. Replicas, distributed queries and Keeper-related tooling sometimes authenticate with the default account or with credentials that inherit from it, so disabling it before those paths are mapped can stop replication or queries. Test the change on a pre-production cluster with representative traffic first. Confirm with your ClickHouse documentation and your own configuration how inter-node authentication is set up in your version, and confirm the rollback steps before the production change window opens.
Should we pay for a fixed-scope hardening engagement or a retainer?
Hardening the default user is usually a one-time piece of work with a defined end point, so a fixed-scope engagement fits best. Costs are driven by the number of clusters, the number of client applications to map, the authentication design, and the testing and documentation you require. A retainer covers work that recurs, such as reviewing access after new users are added, reviewing new ClickHouse releases, or running periodic checks. DeployOpen does not publish a price list. Ask for a quote built from a written scope that separates one-time and recurring items.
What should a written scope for this work include?
A useful scope names the clusters and nodes in scope, the client inventory and who verifies it, the authentication method for the named admin, the network rules to be written into users.d, the test plan on pre-production, the change windows, and the rollback procedure. It should also state what evidence you receive, such as configuration files, a record of SHOW ACCESS output and test results. Ask what is excluded, for example application credential changes, access reviews after go-live, or upgrades, so you can budget for them separately.
Does securing the default user satisfy an audit by itself?
No. Restricting the default user is one control among many. An assessor will also look at how named accounts are created, how privileges are granted, whether access is reviewed, and whether configuration changes are logged. DeployOpen prepares systems, controls and evidence for compliance assessments, but it does not issue audit reports or certifications. Independent auditors and certification bodies make that determination, so confirm the evidence list with your assessor before the work begins.
How DeployOpen works on this
DeployOpen plans, builds, tests and documents the default user lockdown inside your own infrastructure, so the change is scoped, verified and handed to your team in writing. Each step is designed to leave a named admin, restricted network access and evidence that the default user can no longer do what it could before.
How we scope
- We inventory every ClickHouse node, the users.d and users.xml files in use, and the clients that connect today.
- The written scope names the default user decision, network allow lists and the named admin accounts to be created.
- Engagements are fixed-scope, with deliverables and timelines agreed in writing before work starts.
How we build
- We set a password or disable the default user on a pre-production cluster first, then confirm dependent applications still connect.
- We restrict network access in users.d for the default user and create a named admin through SQL-driven access management.
- We test that a lower-privilege account cannot read or change access settings, and record the results for review.
How we hand over
- You receive the final configuration, the access model and a list of accounts with their roles and network scopes.
- We document the rollback steps and the procedure for changing the admin password or adding a new admin.
- Your team can rerun the verification queries, so the lockdown can be checked after every node change.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after handover.
- A retainer can cover periodic access reviews as roles, users and application connections change over time.
- Re-testing after ClickHouse upgrades helps confirm that access settings still behave as documented.
