Skip to content

Users and grants

Create ClickHouse users with presets or grants and a settings profile, keep their passwords in Secrets, rotate and delete them.

Updated View as Markdown

Every connection to a warehouse uses a username and password. EZGH generates each user’s password and keeps it in a Secret in the warehouse’s project, ready for your app to read. You manage users and their grants through EZGH, not with CREATE USER or GRANT inside ClickHouse, and changes apply at once on every node, without a restart.

A warehouse comes with the user app, with the admin preset. User names match ^[a-z][a-z0-9_]{0,31}$; names starting ezgh_ are reserved.

Presets

Preset Can
admin Everything in every database: SELECT, INSERT, ALTER, table DDL, TRUNCATE, OPTIMIZE; use every connection; KILL QUERY on any user’s queries; read the system tables about your data and queries, such as system.query_log.
readWrite SELECT, INSERT, ALTER UPDATE and ALTER DELETE, and table DDL in every database.
readOnly SELECT and SHOW in every database.
custom What its grants allow.

A custom user lists grants, each { "database": "events", "table": "*", "privileges": ["SELECT"] }, where table is a table’s name or * for every table, and privileges are from SELECT, INSERT, ALTER, CREATE, DROP, TRUNCATE, OPTIMIZE and SHOW.

No user can create or drop databases (that’s the API’s), manage users and grants, or change server settings.

Settings profiles

Each user has a settings profile, which sets its limits per query:

Profile max_memory_usage max_execution_time
default Half of a node’s memory None
heavy Three quarters of a node’s memory None
light A quarter of a node’s memory 300 seconds

In every profile, max_threads is at most the node’s vCPUs. A user can SET other query settings within its profile’s limits, such as insert_quorum.

Create a user

Open Warehouse, then the warehouse, then its Users tab, and choose Create user. Enter a name, a preset or grants, and a settings profile. The user’s row links to its Secret.

ezgh warehouse users create whs_4k2m9x0a7qpe grafana --preset readOnly --settings-profile light --wait
ezgh warehouse users create whs_4k2m9x0a7qpe loader --preset custom \
  --grants '[{"database":"events","table":"*","privileges":["SELECT","INSERT"]}]' --wait
ezgh warehouse users get whs_4k2m9x0a7qpe grafana

ezgh warehouse users update … changes the preset, grants or profile, rotate-password writes a new password, and delete deletes the user.

A user exists from the start of its create, creating, with its secret set. Its password is written to the Secret as you, so creating a user also needs secrets.secrets.create and secrets.versions.create.

Passwords in Secrets

Each user’s Secret is named warehouse/<warehouse ID>/users/<user>, with the fields:

Field Holds
username The user’s name.
password Its password, 32 letters and digits.
host The warehouse’s name.
httpsPort 8443.
nativePort 9440.
plaintextHttpPort, plaintextNativePort 8123 and 9000, or empty while plaintext ports are off.
url https://<name>:8443.
database default.

The console, the CLI and the API never show a password: read it from the Secret.

Rotate a password

ezgh warehouse users rotate-password whs_4k2m9x0a7qpe grafana --wait

The Secret gets a new version with the new password, then the user takes it. Rotating needs secrets.versions.create.

Delete a user

ezgh warehouse users delete whs_4k2m9x0a7qpe grafana --wait

Deleting a user deletes its Secret as you, so it also needs secrets.secrets.delete.

Navigation

Type to search…

↑↓ navigate↵ selectEsc close