Managing Secrets in ClickHouse Schemas with Atlas

ClickHouse objects can hold credentials: a user's password, the password argument of a MySQL table engine, a kafka_sasl_password setting, or an access key inside an s3() call in a view. Atlas recognizes these secrets with the same rules ClickHouse uses to hide them, and masks them in what it prints and writes to migration files.

Apply secrets with care

Apply changes that write secrets by hand, run by an operator with elevated privileges on the database, rather than from routine automation.

Atlas masks the secrets it recognizes, but it cannot guarantee that every secret is masked in every case.
Atlas follows the rules ClickHouse uses to hide secrets, and catches up with each ClickHouse release after it ships. An engine, function, or setting that a new ClickHouse version adds is masked only from a later Atlas release.
If you automate these deployments, test your exact use case first, and check its output and logs for secrets before you rely on it.

To manage secrets with Atlas:

Pass each secret to the schema as an input variable, never as a value written in a file.
Configure the dev database to show secrets to Atlas.
Write secrets with atlas schema apply, which leaves no migration file behind. Fill them into migration files yourself only when you need versioned migrations.
Deploy from an environment you control, with Atlas Cloud reporting turned off.

Atlas v1.3.9 or later

This guide requires Atlas v1.3.9 or later. Earlier versions write the credential itself into generated migration files, where it can be committed with the file. Upgrade before you follow these steps.

Atlas Pro Feature

The ClickHouse driver is available only to Atlas Pro users. To use it, run:

atlas login

Passing Secrets to the Schema

Declare each secret as an input variable, and reference the variable where the secret goes:

schema.ch.hcl
variable "mysql_password" {
  type = string
}

variable "reader_password" {
  type = string
}

schema "default" {}

schema "app" {}

table "orders_mysql" {
  schema = schema.app
  engine = sql("MySQL('mysql:3306', 'shop', 'orders', 'reader', '${var.mysql_password}')")
  column "id" {
    null = false
    type = UInt64
  }
}

user "reader" {
  password = var.reader_password
}

reader is a server-level object, so Atlas connects at server scope, with no database in the URL. At this scope, Atlas plans DROP DATABASE for every database the schema does not declare. The schema therefore declares default next to app.

Load the schema in atlas.hcl, and pass the values from environment variables that your CI system or secret store sets at deploy time:

atlas.hcl
data "hcl_schema" "app" {
  path = "schema.ch.hcl"
  vars = {
    mysql_password  = getenv("MYSQL_PASSWORD")
    reader_password = getenv("CLICKHOUSE_READER_PASSWORD")
  }
}

To read a secret straight from AWS Secrets Manager, GCP Secret Manager, or HashiCorp Vault instead, use the runtimevar data source.

Configuring the Dev Database

To plan a change, Atlas loads the desired schema into the dev database and reads it back. By default, ClickHouse returns [HIDDEN] in place of each secret, so that is what Atlas reads back.

A schema written in SQL reaches Atlas only through the dev database. With a dev database that hides secrets, atlas schema apply creates the object on the target with the text [HIDDEN] as its credential, and reports no error.

To make the dev database show secrets to Atlas, all three of these must be in place:

The server setting display_secrets_in_show_and_select, turned on in the container's command.
The session setting format_display_secrets_in_show_and_select, passed as a query parameter on the dev URL. Atlas applies every ClickHouse setting in the URL to its session.
The displaySecretsInShowAndSelect privilege, for the user Atlas connects as. The default user of a dev database started by a docker block holds it. Grant it to any other user with GRANT displaySecretsInShowAndSelect ON *.* TO <user>.

If either setting is missing, ClickHouse still returns [HIDDEN], without an error.

Start the dev database with a docker block, and point the env at it:

atlas.hcl
docker "clickhouse" "dev" {
  image   = "clickhouse/clickhouse-server:23.11"
  command = ["--", "--display_secrets_in_show_and_select=1"]
}

env "prod" {
  url = getenv("DATABASE_URL")
  dev = "${docker.clickhouse.dev.url}?format_display_secrets_in_show_and_select=1"
  schema {
    src = data.hcl_schema.app.url
    mode {
      roles = true // Manage users and roles
    }
  }
}

Set image to the ClickHouse version of the target. The settings work the same in every version from 23.10 on.
Do not turn these settings on for the target database. Atlas does not need them there, and they would let the user Atlas connects as read every secret on the server.
User passwords are not covered by these settings: ClickHouse never returns a password, only its hash. Atlas takes each user's password from the HCL schema instead, and restores it after it reads the dev database back, so the plan has the password to write.
Named collection values need show_named_collections_secrets instead, as described in Enabling Secret Visibility.

This dev database shows every secret to anyone who connects to it. Run it on the machine that runs Atlas, as described in Deploying Secrets Securely, and never share it.

Declarative Workflow

atlas schema apply writes secrets from the schema directly. Statements that carry a secret print masked, with every identifier and literal replaced by (sensitive):

atlas schema apply --env prod

Output (abridged)
Planning migration statements (3 in total):

  -- create user "reader":
    -> CREATE USER `reader`;
  -- add new schema named "app":
    -> CREATE DATABASE `app` ENGINE Atomic;
  -- create "orders_mysql" table:
  -- atlas:sensitive:
    -> CREATE TABLE (sensitive).(sensitive) (
         (sensitive) UInt64
       ) ENGINE = MySQL((sensitive), (sensitive), (sensitive), (sensitive), (sensitive));

atlas schema inspect prints [HIDDEN] in place of each secret, in HCL and in SQL output:

atlas schema inspect --url $DATABASE_URL (abridged)
table "orders_mysql" {
  schema = schema.app
  engine = sql("MySQL('mysql:3306', 'shop', 'orders', 'reader', '[HIDDEN]')")
  column "id" {
    null = false
    type = UInt64
  }
}

Default behavior

Without sensitive = ALLOW, Atlas handles secrets as follows:

Engine arguments are written with their real values when an object is created.
A change to a secret alone is not planned. Atlas masks secrets on both sides before comparing, so changing a credential in the schema reports Schema is synced, no changes to be made.
User passwords are never written. In the output above, reader is created as CREATE USER reader, without a password.

warning

Unless the server disables allow_no_password, ClickHouse lets a user created without a password log in without one. Create users from an env that sets sensitive = ALLOW, described below, or set their passwords outside Atlas.

Writing and rotating secrets with `sensitive = ALLOW`

Set sensitive = ALLOW in the env's schema mode to let Atlas write user passwords and compare secrets by value. Keep it in a separate env, and run that env only to create users or to rotate a secret:

atlas.hcl
env "prod-secrets" {
  url = getenv("DATABASE_URL")
  dev = "${docker.clickhouse.dev.url}?format_display_secrets_in_show_and_select=1"
  schema {
    src = data.hcl_schema.app.url
    mode {
      roles     = true
      sensitive = ALLOW
    }
  }
}

Run it against the target from the example above, which already holds reader and orders_mysql:

atlas schema apply --env prod-secrets

Output (abridged)
Planning migration statements (3 in total):

  -- modify password for user "reader":
  -- atlas:sensitive:
    -> ALTER USER (sensitive) IDENTIFIED BY (sensitive);
  -- drop "orders_mysql" table:
    -> DROP TABLE `app`.`orders_mysql` SYNC;
  -- create "orders_mysql" table:
  -- atlas:sensitive:
    -> CREATE TABLE (sensitive).(sensitive) (
         (sensitive) UInt64
       ) ENGINE = MySQL((sensitive), (sensitive), (sensitive), (sensitive), (sensitive));

The plan shows what sensitive = ALLOW does, and why it belongs in a separate env:

ALTER USER sets the password of reader. ClickHouse never returns a password, so every run sets the password of each declared user again.
Every other secret is compared as ClickHouse returns it. The target returns the MySQL password as [HIDDEN], which never matches the schema, so orders_mysql is dropped and created again on every run. Tables whose engine cannot be altered, such as MySQL or Kafka, are recreated this way.
The plan analysis reports changing password for user "reader" should be handled manually, and DS102 for the dropped table. Neither stops atlas schema apply.

Versioned Workflow

atlas migrate diff never writes a secret's value. A statement that carries a secret is written with '[HIDDEN]' in its place, and marked with the -- atlas:sensitive directive. CREATE USER is written without a password. You fill in the values with template directory variables, in the steps below.

1. Generate the migration

Generate migrations from a separate env whose migration directory is a plain file:// URL. The template_dir data source that step 3 uses fails while the directory holds no .sql file, so this step cannot use it:

atlas.hcl
env "diff" {
  dev = "${docker.clickhouse.dev.url}?format_display_secrets_in_show_and_select=1"
  schema {
    src = data.hcl_schema.app.url
    mode {
      roles = true
    }
  }
  migration {
    dir = "file://migrations"
  }
}

atlas migrate diff add_orders_mysql --env diff

migrations/20261109131909_add_orders_mysql.sql
-- Create user "reader"
CREATE USER `reader`;
-- Add new schema named "app"
CREATE DATABASE `app` ENGINE Atomic;
-- Create "orders_mysql" table
-- atlas:sensitive
CREATE TABLE `app`.`orders_mysql` (
  `id` UInt64
) ENGINE = MySQL('mysql:3306', 'shop', 'orders', 'reader', '[HIDDEN]');

2. Fill in the secrets

Fill in every secret before the file is applied, in the same file:

Replace each '[HIDDEN]' with a template variable.
Give CREATE USER its password, mark it -- atlas:sensitive, and read the password from a template variable. The user then never exists without a password, even when a later statement in the file fails.

migrations/20261109131909_add_orders_mysql.sql
-- Create user "reader"
-- atlas:sensitive
CREATE USER `reader` IDENTIFIED BY '{{ .reader_password }}';
-- Add new schema named "app"
CREATE DATABASE `app` ENGINE Atomic;
-- Create "orders_mysql" table
-- atlas:sensitive
CREATE TABLE `app`.`orders_mysql` (
  `id` UInt64
) ENGINE = MySQL('mysql:3306', 'shop', 'orders', 'reader', '{{ .mysql_password }}');

Then update the directory checksum:

atlas migrate hash --env diff

warning

ClickHouse accepts '[HIDDEN]' as a literal value. A file applied without its placeholders filled in creates an object whose credential is the text [HIDDEN], which cannot connect to its source.

3. Apply the directory

Load the directory through the template_dir data source, which fills in the variables:

atlas.hcl
data "template_dir" "migrations" {
  path = "migrations"
  vars = {
    mysql_password  = getenv("MYSQL_PASSWORD")
    reader_password = getenv("CLICKHOUSE_READER_PASSWORD")
  }
}

env "prod" {
  url = getenv("DATABASE_URL")
  dev = "${docker.clickhouse.dev.url}?format_display_secrets_in_show_and_select=1"
  schema {
    src = data.hcl_schema.app.url
    mode {
      roles = true
    }
  }
  migration {
    dir = data.template_dir.migrations.url
  }
}

atlas migrate apply --env prod

Output (abridged)
Migrating to version 20261109131909 (1 migrations in total):

  -- migrating version 20261109131909
    -> CREATE USER (sensitive) IDENTIFIED BY (sensitive);
    -> CREATE DATABASE `app` ENGINE Atomic;
    -> CREATE TABLE (sensitive).(sensitive) (
         (sensitive) UInt64
       ) ENGINE = MySQL((sensitive), (sensitive), (sensitive), (sensitive), (sensitive));
  -- ok (67.468617ms)

The statements print masked, and the target receives the real values. A later atlas migrate diff --env prod renders the directory with the same variables, and reports The migration directory is synced with the desired state, no changes to be made.

4. Rotate a user password

A changed password in the schema is never planned in the versioned workflow, with or without sensitive = ALLOW. To rotate one, add a migration file of your own. Run atlas migrate new after atlas migrate hash, as it refuses a directory whose checksum does not match:

atlas migrate new rotate_reader_password --env diff

Mark the file -- atlas:sensitive, and read the new password from the template variable:

migrations/20261110031543_rotate_reader_password.sql
-- atlas:sensitive
ALTER USER `reader` IDENTIFIED BY '{{ .reader_password }}';

Run atlas migrate hash --env diff again, set the new password in CLICKHOUSE_READER_PASSWORD, and apply the directory as in step 3.

Without sensitive = ALLOW, named collections are written with their keys and empty values: see the named collections guide.

Deploying Secrets Securely

A deployment that writes secrets carries their real values along its whole path: the workflow you choose, the machine that runs Atlas, the dev database, and the logs and reports the run produces. Secure each of them:

Prefer the declarative workflow when it fits. atlas schema apply writes secrets in place, from the schema's variables, and leaves no migration file behind. In the versioned workflow, every secret passes through a migration file that is kept in the repository, and you must keep that file free of real values.
Run Atlas by hand, as an operator with elevated privileges on the database. If you automate the deployment, run it from a trusted job inside your network, such as one that only your default branch can start, and never give secrets to a job that runs pull request code.
Secure the dev database like the target: Atlas applies your secrets to it too. Start it from the docker block in Configuring the Dev Database, or from the official clickhouse/clickhouse-server image, since the container receives your secrets. Run it on the machine that runs Atlas. It shows secrets to anyone who connects, so do not share it with other teams or environments.
Read secrets from your CI secret store or a secret manager at deploy time. Do not print the variables that hold them in CI logs.
Turn off Atlas Cloud reporting for the env with skip_report = true, so the execution log of these deployments stays out of Atlas Cloud.

An env that deploys from the Atlas Registry, with an atlas:// URL or a repo block, reports every deployment to Atlas Cloud. Turn that off in the env's migration block:

atlas.hcl
env "prod-secrets" {
  url = getenv("DATABASE_URL")
  dev = "${docker.clickhouse.dev.url}?format_display_secrets_in_show_and_select=1"
  schema {
    src = data.hcl_schema.app.url
    mode {
      roles     = true
      sensitive = ALLOW
    }
  }
  migration {
    // Takes effect when the env deploys from the Atlas Registry.
    skip_report = true
  }
}

skip_report applies to both atlas schema apply and atlas migrate apply. See Skipping Cloud Reporting for details.

Next Steps

Security as Code (Declarative)

Define ClickHouse roles, users, and permissions as code

Named Collections

Manage ClickHouse named collections and their values

Secrets in Deployments

Read database credentials from a secret store at deploy time

Passing Secrets to the Schema​

Configuring the Dev Database​

Declarative Workflow​

Default behavior​

Writing and rotating secrets with sensitive = ALLOW​

Versioned Workflow​

1. Generate the migration​

2. Fill in the secrets​

3. Apply the directory​

4. Rotate a user password​

Deploying Secrets Securely​

Next Steps​