Manage users
Every application that connects to Alauda Data Services Analytical Database E1 needs an account: a user name, a password, and a list of what that account may do. This page shows how to create such accounts, change their passwords, give them privileges, and remove them again, without restarting the database.
When you are done you have accounts that live in the instance manifest, so that they are versioned with it, re-created with it, and present on every replica the instance will ever have.
TOC
Steps at a glanceBefore you start: how accounts reach the databasePrerequisitesStep 1: choose where the account livesStep 2: prepare the passwordStep 3: declare the accountStep 4: check the loginWhere the password is keptChange a passwordGive an account more privilegesRemove an accountAccounts created with SQLNotesSteps at a glance
- Decide where the account lives: in the instance manifest, or inside the database.
- Turn the password into a hash, or store it in a Secret.
- Declare the account in the manifest and apply the manifest.
- Check that the account can log in.
Plan for about ten minutes. Changing a password, adding privileges and removing an account are each a one-line edit of the same manifest and are covered after step 4.
Before you start: how accounts reach the database
- The manifest is the source. Accounts, profiles and quotas are written under
spec.configurationof theClickHouseInstallation. The operator turns that block into a configuration file,users.d/chop-generated-users.xml, puts it into a ConfigMap namedchi-<instance>-common-usersd, and mounts that ConfigMap into every pod of the instance. - No restart. The database server watches its
users.ddirectory and reloads it when the file changes. The pods are not restarted when you change accounts; the StatefulSets do not change, so the operator leaves them alone. Expect the new content to be live within about a minute, which is how long Kubernetes takes to refresh a mounted ConfigMap. - The key syntax is a path. A key such as
app/networks/ipmeans: the accountapp, elementnetworks, elementipinside it. Everything before the first/names the account, profile or quota. A list value produces one element per item. - Two accounts are already there.
defaultexists but may only connect from inside its own pod, on purpose.clickhouse_operatoris the operator's own account; it reads metrics and copies schema to new replicas. Leave both alone. Access the instance explains them.
Prerequisites
Step 1: choose where the account lives
This page uses the manifest. The SQL form is covered at the end, for the cases where it fits better.
Step 2: prepare the password
The manifest never holds the password itself. It holds a SHA-256 hash of it, a 64-character hexadecimal string, and the database compares the hash of what the client sends with it. Produce the hash with:
Expect one line of 64 hexadecimal characters. Keep the plain password for your application and for step 4; the hash goes into the manifest.
If you would rather not have even the hash in the manifest, put it into a Secret in the instance's namespace and point the manifest at it:
The manifest then uses app/k8s_secret_password_sha256_hex: <namespace>/<instance>-users/app-sha256 instead of app/password_sha256_hex. The value is <namespace>/<secret-name>/<key>. The operator reads the Secret when it processes the instance and writes the hash into the ConfigMap, exactly as if it had been in the manifest. See "Change a password" below for what that means when you rotate it.
Step 3: declare the account
Edit the instance and add the account under spec.configuration.users. The example adds one account, app, that may connect from any pod in the cluster, may use only the database analytics, and may run ON CLUSTER statements in it. Replace the placeholders:
What each line does:
password_sha256_hexsets the password. Always set one. An account with no password key accepts an empty password.networks/ip: "::/0"lets the account connect from any address. Without it the operator adds only::1and127.0.0.1, which is the pod itself, and connections from other pods are refused. Narrow it to your application's address range when you can; the list may have several entries.profile: defaultattaches the built-in settings profile. Declare your own profile underspec.configuration.profilesto cap memory or make the account read-only; Access the instance shows the syntax.grants/querylistsGRANTstatements that run for this account every time the file is loaded. They are the same statements you would type as SQL, without theTO <user>part.GRANT CLUSTER ON *.*lets the account addON CLUSTERto a statement, which is how a replicated table is created on every replica at once. Without it, such a statement fails withCode: 497 ... it's necessary to have the grant CLUSTER ON *.*, even though the same statement withoutON CLUSTERworks. It does not widen what the account may touch: anON CLUSTERstatement still needs the account's privileges on the objects it names, soappremains limited toanalytics. Leave the line out if the account never runsON CLUSTERstatements.
Apply the manifest and wait for the operator:
The STATUS column goes through InProgress and returns to Completed; press Ctrl-C then. Confirm that nothing restarted and that the file reached a pod:
The pods show the same AGE as before, and the second command prints 1. If it prints 0, wait a minute and run it again; the ConfigMap has not been refreshed in the pod yet.
Step 4: check the login
From inside a pod of the instance, with the plain password:
Expect app. Then log in through the instance Service, which is what your application will use. From a pod of another replica:
On a single-replica instance, use 0-0-0 again; the connection still goes through the Service. The server image has no curl. From your application's pod you can check the HTTP port instead:
Expect app again. An Authentication failed here, after the first command worked, means the address is not allowed: check networks/ip.
Where the password is kept
Nowhere in the cluster, for an account declared with a hash. The manifest, the ConfigMap and the file in the pod all hold only the SHA-256 hash, which cannot be turned back into the password. Keep the plain password where your application keeps it. If it is lost, set a new one as described in the next section.
The exceptions: a plain password: key stays readable in the instance manifest; a Secret-backed account's reference, shown by kubectl -n <namespace> get chi <instance> -o jsonpath='{.spec.configuration.users}', points at the Secret, which holds whatever you stored there; and the operator's own account keeps a plain password in the Secret clickhouse-operator in the operators namespace.
Change a password
Replace the hash in the manifest with the hash of the new password and apply the manifest. Within one to two minutes the old password stops working and the new one works on every replica (about 75 seconds on the slowest of three replicas in testing); nothing restarts. Connections that are already open stay open until the client closes them.
The replicas do not switch at the same moment. Each pod reloads the file on its own, so for roughly 30 seconds one replica may accept only the old password while another accepts only the new one, and a login through the Service succeeds or fails depending on which replica it lands on. Switch your application to the new password only after the old one is refused on every replica, or give it both and let it retry.
If the hash lives in a Secret, update the Secret and then make the operator process the instance again. The operator does not watch Secrets, so a Secret change on its own changes nothing in the pods. Set spec.taskID to any new value to trigger the operator:
Then watch the instance return to Completed as in step 3 and check the login as in step 4.
Give an account more privileges
Add a line to its grants/query list and apply the manifest:
Any GRANT the database understands is accepted, including GRANT CLUSTER ON *.* for ON CLUSTER statements and GRANT <role> for a role created with SQL. An error at reload time appears in the server log, not at apply time, so after a change run a query that needs the new privilege, as the account.
Remove an account
Delete every key that starts with the account's name and apply the manifest. From the next reload on, the account no longer exists and new connections as that account are refused.
Accounts created with SQL
Give one manifest account the right to manage others:
That account can then create users, roles, row policies and quotas with SQL:
Three things to know before you rely on this:
ON CLUSTERneeds the coordination quorum. Without one the statement fails withNO_ELEMENTS_IN_CONFIG; see Configure a replicated cluster. WithoutON CLUSTERthe account exists on one replica only.- Replicas added later start without these accounts. When you raise
replicasCountorshardsCount, the operator copies tables to the new hosts, not accounts. Run the statements again, or declare the account in the manifest instead. - Do not create an SQL account with a name that the manifest declares. The manifest version is loaded from a file and cannot be altered or dropped with SQL; the statement fails.
Notes
- A plain
password:key is accepted and the operator hashes it before writing the ConfigMap, but the manifest itself is readable by anyone who can read the instance. Usepassword_sha256_hex, or a Secret. - Besides the address list, the operator adds a hostname pattern to every account that matches the instance's own pods. It only adds access. ClickHouse admits a connection that matches any entry under
networks, so the pattern never blocks a connection thatnetworks/ipallows, and with::/0declared it makes no difference. To keep an account to your application, narrownetworks/ipto the application's address range; Access the instance has more on the defaults. - Profiles and quotas follow the same key syntax under
spec.configuration.profilesandspec.configuration.quotas, and reload the same way.
ClickHouse is a registered trademark of ClickHouse, Inc. https://clickhouse.com
Alauda is an independent vendor. This product is not affiliated with, endorsed by, or sponsored by ClickHouse, Inc. All trademarks are the property of their respective owners and are used here for identification purposes only.