An analytics service receives a dedicated ClickHouse user from Stronghold. The user’s privileges come from a predefined ClickHouse role, so the SQL statements in Stronghold stay short and the privilege set is managed in one place, in ClickHouse.

Goal

Configure issuing dynamic ClickHouse credentials to the reports service: the user gets the analytics_reader ClickHouse role with read access to the analytics database, lives for 1 hour with renewal up to 24 hours, and is dropped after the lease is revoked.

Prerequisites

To follow this example, you need:

  • Stronghold and a token with permissions to configure secrets engines and policies.
  • A ClickHouse server with SQL-driven access control enabled, reachable from Stronghold over the native protocol.
  • An analytics database.
  • The clickhouse-client client and the jq utility on your workstation for verification.

Step 1. Prepare a role and a service account in ClickHouse

Run the following in ClickHouse as an administrator:

CREATE ROLE analytics_reader;
GRANT SELECT ON analytics.* TO analytics_reader;

CREATE USER stronghold IDENTIFIED BY '<INITIAL_PASSWORD>';
GRANT CREATE USER, ALTER USER, DROP USER ON *.* TO stronghold;
GRANT analytics_reader TO stronghold WITH ADMIN OPTION;

<INITIAL_PASSWORD> is the initial password of the service account. In step 2, Stronghold replaces it with a generated one.

WITH ADMIN OPTION allows the stronghold account to grant the analytics_reader role to the users it creates.

The stronghold account needs privileges to create, alter, and drop users (CREATE USER, ALTER USER, DROP USER): they are used when issuing credentials, during rotate-root, and when revoking a lease by default. To grant a role, the account also needs the right to assign it, for example WITH ADMIN OPTION, as in the example above.

In a ClickHouse cluster, add ON CLUSTER '<CLUSTER_NAME>' to the statements, as in the example in ClickHouse.

Step 2. Configure the connection

Enable the database secrets engine and connect it to ClickHouse:

  1. Enable the secrets engine if it is not enabled yet:

    d8 stronghold secrets enable database

  2. Configure the connection to ClickHouse:

    d8 stronghold write database/config/reports-clickhouse plugin_name="clickhouse-database-plugin" allowed_roles="reports" connection_url="clickhouse://clickhouse.example.com:9440?username={{username}}&password={{password}}&secure=true" username="stronghold" password="<INITIAL_PASSWORD>"

    secure=true enables TLS. Port 9440 is the standard ClickHouse native protocol port with TLS.

    The connection_url format is clickhouse://<HOST>:<PORT>?username=...&password=.... The port is required; the secure and skip_verify parameters control TLS.

  3. Rotate the service account password so that only Stronghold knows it:

    d8 stronghold write -force database/rotate-root/reports-clickhouse

Step 3. Create a role

Create a Stronghold role that issues users with the analytics_reader ClickHouse role:

d8 stronghold write database/roles/reports db_name="reports-clickhouse" creation_statements="CREATE USER '{{name}}' IDENTIFIED BY '{{password}}'; GRANT analytics_reader TO '{{name}}'; SET DEFAULT ROLE analytics_reader TO '{{name}}';" revocation_statements="DROP USER IF EXISTS '{{name}}';" default_ttl="1h" max_ttl="24h"
  • creation_statements: create the user, grant the analytics_reader role, and make it the default role so that the privileges apply right after login.
  • revocation_statements: drop the user when the lease is revoked.
  • default_ttl and max_ttl: the lease duration on issue and renewal, and the maximum credential lifetime.

If revocation_statements are set in the role, the plugin runs them when a lease is revoked. If they are not set, the plugin runs DROP USER IF EXISTS.

Step 4. Create a policy

Create a policy that allows reading credentials of the reports role:

d8 stronghold policy write reports-clickhouse - <<'POLICY'
path "database/creds/reports" {
  capabilities = ["read"]
}
POLICY

Assign the policy to the auth method role the service logs in with, for example a Kubernetes auth role as in Dynamic PostgreSQL credentials.

Step 5. Get credentials

Request credentials of the reports role and manage their lease:

  1. Request credentials:

    d8 stronghold read database/creds/reports

    Example output:

    Key                Value
---                -----
lease_id           database/creds/reports/2f6a614c-4aa2-7b19-24b9-ad944a8d4de6
lease_duration     1h
lease_renewable    true
password           SsnoaA-8Tv4t34f41baD
username           v-token-reports-Xk29fLq7Pz3mWn8a

  2. Renew the lease before lease_duration expires:

    d8 stronghold lease renew database/creds/reports/2f6a614c-4aa2-7b19-24b9-ad944a8d4de6

  3. When the credentials are no longer needed, revoke the lease:

    d8 stronghold lease revoke database/creds/reports/2f6a614c-4aa2-7b19-24b9-ad944a8d4de6

To deliver the credentials to the service and renew them automatically, use Stronghold Agent with a {{ with secret "database/creds/reports" }} template. The Agent configuration is in Delivering secrets to Kubernetes pods.

Verification

Check the result:

  1. Request credentials and store them in variables:

    creds="$(d8 stronghold read -format=json database/creds/reports)"
CH_USER="$(echo "$creds" | jq -r '.data.username')"
CH_PASSWORD="$(echo "$creds" | jq -r '.data.password')"
LEASE_ID="$(echo "$creds" | jq -r '.lease_id')"

  2. Connect with clickhouse-client and check the current user and its grants:

    clickhouse-client --host clickhouse.example.com --port 9440 --secure --user "$CH_USER" --password "$CH_PASSWORD" --query "SELECT currentUser(); SHOW GRANTS;" --multiquery

    The output must contain the analytics_reader role.

  3. Make sure writes are denied:

    clickhouse-client --host clickhouse.example.com --port 9440 --secure --user "$CH_USER" --password "$CH_PASSWORD" --query "CREATE TABLE analytics.t (x UInt8) ENGINE = Memory"

    The command must fail with ACCESS_DENIED.

  4. Revoke the lease and check that the user is dropped:

    d8 stronghold lease revoke "$LEASE_ID"
clickhouse-client --host clickhouse.example.com --port 9440 --secure --user "$CH_USER" --password "$CH_PASSWORD" --query "SELECT 1"

    The connection must fail with an authentication error.

Cleanup

Delete the objects created in this example:

  1. Revoke all credentials issued for the role:

    d8 stronghold lease revoke -prefix database/creds/reports/

  2. Delete the role, the policy, and the connection:

    d8 stronghold policy delete reports-clickhouse
d8 stronghold delete database/roles/reports
d8 stronghold delete database/config/reports-clickhouse

  3. If needed, drop the service account and the role in ClickHouse: DROP USER stronghold; DROP ROLE analytics_reader;.

Additional resources

←
Dynamic credentials / Dynamic MySQL and MariaDB credentials
Dynamic credentials / Dynamic RabbitMQ users
→