ClickHouse CREATE USER: Authentication, Roles, and Best Practices

To create a user in ClickHouse, run CREATE USER <name> IDENTIFIED BY '<password>' from a session that has the CREATE USER privilege. The full statement supports a dozen authentication methods, host restrictions, default roles, default database, granular setting limits, and distributed creation across a cluster. Users are stored in the access control system and can be managed with SQL, configuration files, or external sources like LDAP. This guide covers the syntax end to end, the safer authentication methods, and the RBAC patterns that hold up in production.

The Quick Answer

CREATE USER alice IDENTIFIED BY 'a-strong-password';
GRANT SELECT ON analytics.* TO alice;

That creates a user authenticated with a SHA-256 password (the default) and grants read access to one database. For anything beyond a developer sandbox, you want to be more deliberate about the password hash, the host clause, and the role assignment.

Full CREATE USER Syntax

CREATE USER [IF NOT EXISTS | OR REPLACE] name [, name2 ...] [ON CLUSTER cluster_name]
    [NOT IDENTIFIED | IDENTIFIED { WITH auth_method [BY 'secret'] | BY 'password' }]
    [HOST {LOCAL | NAME 'fqdn' | REGEXP 'pattern' | IP 'addr/mask' | LIKE 'tpl' | ANY | NONE}]
    [VALID UNTIL datetime]
    [IN access_storage_type]
    [DEFAULT ROLE role [, role2 ...]]
    [DEFAULT DATABASE db | NONE]
    [GRANTEES {user | role | ANY | NONE} [, ...] [EXCEPT ...]]
    [SETTINGS variable [= value] [MIN min] [MAX max] [READONLY | WRITABLE] [, ...] | PROFILE 'profile_name']

Every clause is optional except the user name. Sensible defaults are: no host restriction (HOST ANY), no default role, no default database, and IDENTIFIED WITH sha256_password if you specify IDENTIFIED BY '<password>'.

Authentication Methods

ClickHouse supports a wide range of authentication backends. Pick based on your security posture and existing identity infrastructure.

Password-Based

-- SHA-256 (default when you write IDENTIFIED BY)
CREATE USER alice IDENTIFIED BY 'secret';

-- Explicit hash type
CREATE USER alice IDENTIFIED WITH sha256_password BY 'secret';
CREATE USER alice IDENTIFIED WITH sha256_hash BY '<precomputed-hex-digest>';

-- bcrypt is the most secure option. Limited to 72 characters.
CREATE USER alice IDENTIFIED WITH bcrypt_password BY 'secret';

-- Legacy: double_sha1 is what older clients and MySQL-protocol connections expect.
CREATE USER alice IDENTIFIED WITH double_sha1_password BY 'secret';

-- Explicit no authentication. Use only for a sandbox.
CREATE USER alice NOT IDENTIFIED;

-- Plaintext is stored in the access entity. Avoid in production.
CREATE USER alice IDENTIFIED WITH plaintext_password BY 'secret';

Use bcrypt_password for anything user-facing. The default sha256_password is acceptable but bcrypt's deliberately slow hash function makes brute force attacks substantially more expensive.

External Identity Providers

-- LDAP, when an ldap_server is configured in users.xml
CREATE USER alice IDENTIFIED WITH ldap SERVER 'corp_ldap';

-- Kerberos, when a kerberos block is configured
CREATE USER 'alice@CORP.EXAMPLE.COM' IDENTIFIED WITH kerberos REALM 'CORP.EXAMPLE.COM';

-- SSL client certificate (matches by Common Name or Subject Alternative Name)
CREATE USER alice IDENTIFIED WITH ssl_certificate CN 'alice@corp.example.com';

-- SSH public key
CREATE USER alice IDENTIFIED WITH ssh_key BY KEY 'AAAA...' TYPE 'ssh-ed25519';

-- HTTP-based authentication via an external service
CREATE USER alice IDENTIFIED WITH http SERVER 'auth_service' SCHEME 'Basic';

LDAP and Kerberos are the right fit for organizations that already centralize identity. SSL certificates and SSH keys remove the password from the picture entirely and are well suited to service accounts and CI agents.

HOST Clause: Restricting Where Connections Can Come From

CREATE USER alice IDENTIFIED BY 'secret'
  HOST IP '10.0.0.0/8', IP '192.168.0.0/16';

CREATE USER bob IDENTIFIED BY 'secret'
  HOST NAME 'app-server-1.corp', NAME 'app-server-2.corp';

CREATE USER carol IDENTIFIED BY 'secret'
  HOST REGEXP '^worker-[0-9]+\.corp$';

CREATE USER local_admin IDENTIFIED BY 'secret' HOST LOCAL;

The valid HOST clauses are LOCAL, NAME 'fqdn', REGEXP 'pcre-pattern', IP 'addr/mask', LIKE 'sql-pattern', ANY (the default), and NONE (block all connections, useful for temporarily disabling an account). You can also use the shorthand CREATE USER 'alice'@'10.0.0.5' for a single-host grant.

Host restrictions are evaluated by the listener and complement, rather than replace, network-level controls. Always pair with a firewall and TLS.

DEFAULT ROLE and DEFAULT DATABASE

CREATE USER alice IDENTIFIED BY 'secret'
  DEFAULT ROLE analyst, qa_reviewer
  DEFAULT DATABASE analytics;

DEFAULT ROLE controls which of the user's granted roles are active by default at login. Without it, the user has to call `SET ROLE` before any role-scoped privilege applies. DEFAULT ROLE ALL EXCEPT admin is a common pattern for users who have an elevated role available but should only use it intentionally.

DEFAULT DATABASE sets the schema for unqualified queries (SELECT * FROM events rather than analytics.events). Use DEFAULT DATABASE NONE to force fully-qualified table names.

SETTINGS: Per-User Limits

CREATE USER alice IDENTIFIED BY 'secret'
  SETTINGS
    max_memory_usage = 4000000000,
    max_threads = 8 MIN 1 MAX 16 WRITABLE,
    readonly = 0;

-- Or attach a settings profile
CREATE USER alice IDENTIFIED BY 'secret' SETTINGS PROFILE 'analyst_profile';

Per-user settings are how you bound resource usage for individual accounts. The MIN/MAX and READONLY/WRITABLE modifiers limit what the user can override at query time with SET. Combined with quotas, this is how you keep one runaway query from blowing up a shared cluster.

ON CLUSTER: Distributed Creation

CREATE USER alice IDENTIFIED BY 'secret' ON CLUSTER my_cluster
  DEFAULT ROLE analyst;

ON CLUSTER runs the statement on every node in the named cluster, so the user exists everywhere it might need to authenticate. The Replicated database engine handles this automatically for databases inside it; for user-level statements ON CLUSTER is still the explicit knob. In ClickHouse Cloud the cluster name is default (or all_replicas in some configurations) and the platform handles distribution.

GRANTEES: Limiting Who Can Receive Grants From This User

CREATE USER team_lead IDENTIFIED BY 'secret'
  GRANTEES junior_analyst, junior_engineer
  SETTINGS PROFILE 'lead_profile';

GRANTEES constrains which other users or roles team_lead is allowed to grant their own privileges to (via WITH GRANT OPTION). GRANTEES NONE blocks delegation entirely. This is the cleanest way to set up a privileged user who can administer a slice of users without being able to escalate access elsewhere.

VALID UNTIL: Time-Bound Users

CREATE USER temp_contractor IDENTIFIED BY 'secret'
  VALID UNTIL '2026-09-01 00:00:00'
  DEFAULT ROLE contractor;

After the cutoff, authentication fails. Useful for contractor accounts and break-glass access without relying on a calendar reminder. See also user expired error for how the failure surfaces.

Common Patterns

A read-only analyst

CREATE ROLE analyst;
GRANT SELECT, SHOW TABLES ON analytics.* TO analyst;

CREATE USER alice IDENTIFIED WITH bcrypt_password BY 'secret'
  HOST IP '10.0.0.0/8'
  DEFAULT ROLE analyst
  DEFAULT DATABASE analytics
  SETTINGS readonly = 1, max_memory_usage = 4000000000;

A service account for an application

CREATE ROLE app_writer;
GRANT SELECT, INSERT ON analytics.events TO app_writer;
GRANT SELECT ON analytics.dim_* TO app_writer;

CREATE USER app_etl IDENTIFIED WITH ssl_certificate CN 'etl-prod.corp.example.com'
  HOST IP '10.10.0.0/16'
  DEFAULT ROLE app_writer;

An admin user with restricted default privileges

CREATE USER ops_admin IDENTIFIED WITH bcrypt_password BY 'secret'
  DEFAULT ROLE NONE
  GRANTEES NONE;

GRANT ALL ON *.* TO ops_admin;

The user has full privileges but no role active by default; they must explicitly SET ROLE ... to use them. This is a useful belt-and-braces protection for privileged accounts.

Common Errors

• Access entity already exists: pick a different name or use OR REPLACE. See access-entity-already-exists.
• Authentication failed: wrong password, wrong host, or wrong authentication method. Check system.user_directories and the server log. See authentication-failed.
• User does not exist: usually a case mismatch or a user defined in users.xml that has been replaced by an SQL-defined one. See user-does-not-exist.
• Cannot set non-granted role: the user has the role available but it has not been activated. Either set it as default or call SET ROLE. See set-non-granted-role.
• User is expired: the VALID UNTIL deadline passed. See user-expired.

How Pulse Helps With ClickHouse Access Control

User and role configuration is one of the most common sources of "it worked yesterday" incidents on ClickHouse clusters. Permissions drift as roles are added, default roles get reshuffled, host restrictions stop matching real network topology, and password hashes never get rotated. Pulse continuously inspects ClickHouse access entities and surfaces users with weak hash methods (plaintext, double_sha1 where bcrypt is available), users without host restrictions, users with readonly = 0 who should be read-only, unused roles, and stale VALID UNTIL dates that lapsed without being noticed. Pulse also tracks query-level resource usage per user so you can spot the account that is silently eating the cluster. Connect your ClickHouse cluster to Pulse and let it watch access control for you.

Frequently Asked Questions

Q: What is the default authentication method when I write IDENTIFIED BY '...'?

sha256_password. The password is hashed server-side and stored as a hex digest. For stronger protection switch to bcrypt_password.

Q: Can a single user have multiple authentication methods?

Yes, on modern ClickHouse versions. You can attach multiple IDENTIFIED WITH clauses, for example to allow both an LDAP-managed identity and a fallback SSH key. Older clients may break on downgrade; the docs explicitly note "older ClickHouse versions lack support for multiple authentication methods per user".

Q: How do I change a user's password?

ALTER USER alice IDENTIFIED WITH bcrypt_password BY 'new-secret';

ALTER USER accepts the same syntax shape as CREATE USER. For a quick rotation across many accounts, script the ALTER USER statements and run them through ON CLUSTER.

Q: Where are SQL-defined users stored?

In the access storage configured in your server (commonly local_directory on disk, but it can be replicated ZooKeeper-backed for clusters or memory for ephemeral testing). Inspect with SELECT * FROM system.users and SELECT * FROM system.user_directories.

Q: How does this interact with users.xml?

XML-defined users and SQL-defined users coexist in the same access namespace. If you create an SQL user with the same name as an XML user, the XML one wins or the SQL one is rejected depending on the access storage configuration. For new deployments, prefer SQL-managed access entities, they are easier to audit and version.

Q: Can I create a user in ClickHouse Cloud the same way?

Yes. CREATE USER works on ClickHouse Cloud with the same syntax, with two notes: the ALL privilege is not supported (grant specific privileges instead), and the default cluster is the conventional name for ON CLUSTER statements. The web console offers a UI for the common cases.

Q: How do I list users and inspect their privileges?

SELECT name, default_roles_list FROM system.users;
SHOW GRANTS FOR alice;
SHOW CREATE USER alice;

The third form is the cleanest way to extract a reproducible definition you can replay on another cluster.