secrets.md

Secrets in introspected schemas

ClickHouse stores secrets — dictionary source passwords, named-collection values like kafka_broker_list — inside create_table_query / SHOW CREATE / system.named_collections. By default it redacts them to the placeholder [HIDDEN] when you read those, unless three conditions are all met:

1. the server is configured with display_secrets_in_show_and_select = 1 (a server-level setting in config.xml, not settable per session);
2. the connecting user holds the displaySecretsInShowAndSelect privilege (GRANT displaySecretsInShowAndSelect ON *.* TO <user>); and
3. the query enables the format_display_secrets_in_show_and_select session setting.

Production contract: credentials are resolved on the cluster

HCL and generated plans are review artifacts, so they must not carry runtime credentials. For dictionary sources, use ClickHouse's native named-collection reference instead of an inline password:

named_collection "demo_dict_source" {
  external = true
  comment  = "provisioned from the production secret manager"
}

database "posthog" {
  dictionary "demo_dict" {
    primary_key = ["k"]
    attribute "k" { type = "String" }

    source "clickhouse" {
      collection = "demo_dict_source"
      table      = "control_table"
    }

    layout "complex_key_hashed" {}
  }
}

collection is supported by clickhouse, mysql, postgresql, and http dictionary sources. It renders to the native ClickHouse NAME argument:

SOURCE(CLICKHOUSE(NAME demo_dict_source TABLE 'control_table'))

The deployment system must provision demo_dict_source before it executes the plan on each node. That provisioning step reads the password from the environment's secret manager and stores it in ClickHouse server config, a SQL named collection, or Keeper-backed named-collection storage. It is intentionally outside chschema: external = true makes the declaration valid for references but produces no collection DDL and ignores the collection's live values.

At execution, the executor sends the generated dictionary DDL unchanged. ClickHouse resolves NAME demo_dict_source locally and supplies the password; the credential never passes through HCL, plan JSON, logs, or SQL generated by chschema. A missing collection is a hard ClickHouse error, not a fallback to a passwordless dictionary.

For per-node execution, ensure the collection is present on every node (or use shared Keeper-backed storage), and grant the execution user NAMED COLLECTION ON demo_dict_source. Arguments after NAME are overrides. If the cluster sets allow_named_collection_override_by_default = 0, mark those keys overridable in the collection or store them there as well.

Default behavior: secrets are marked unknown, never overwritten

hclexp controls only #3 and leaves it off by default. With redaction on, a captured secret comes back as [HIDDEN]. hclexp retains that marker in an HCL dump so later comparisons can distinguish "a secret exists but is unknown" from "there is no secret". It does not re-emit [HIDDEN]:

• dictionary CREATE/CREATE OR REPLACE is blocked when its target contains a marker;
• an altered dictionary is also blocked when its live source contains a marker and the same target source would omit that credential;
• named-collection params with redacted values are skipped by diff, while whole-collection creates/recreates containing them are blocked.

Blocked dictionary rewrites produce no DDL and are reported unsafe. This covers both failure modes: writing the literal placeholder and rendering apparently clean SQL that silently omits a real-but-hidden credential. A visible target credential remains safe to write for backwards compatibility, and a target named-collection reference is safe because ClickHouse resolves its credential at execution.

[HIDDEN] is primarily an introspection marker, not the production authored-HCL pattern. Simply omitting a live inline credential is still a real difference. If the live value was redacted, hclexp reports it but refuses to remove it automatically. Provision an external named collection and set collection on the source to migrate the dictionary without exposing the password.

Capturing real secrets: -show-secrets

When you genuinely want real secret values in the output (for example, to recreate a database on a fresh cluster), pass -show-secrets to introspect or dump-sql:

hclexp introspect -host … -database posthog -show-secrets
hclexp dump-sql    -host … -database posthog -show-secrets -out posthog.sql

This enables format_display_secrets_in_show_and_select = 1 on the connection (condition #3). It only reveals secrets if the server and grant (conditions #1 and #2) also allow it; otherwise values stay [HIDDEN], remain marked unknown in the dump, and are refused at any whole-object emission boundary. The flag is always safe to pass — without the prerequisites it simply has no effect.

To enable the prerequisites on a cluster you control:

<!-- /etc/clickhouse-server/config.d/secrets.xml -->
<clickhouse>
    <display_secrets_in_show_and_select>1</display_secrets_in_show_and_select>
</clickhouse>

GRANT displaySecretsInShowAndSelect ON *.* TO <user>;

Security warning: -show-secrets writes real passwords and connection strings into the introspected HCL / SQL. Treat the output as sensitive — do not commit it to version control or share it. Leave the flag off for routine drift checks; use it only for a one-off capture you intend to handle securely.