Keeping ClickHouse credentials out of SQL with named collections
Credentials should be defined once, by an administrator, and referenced by name everywhere else. In many ClickHouse estates, the S3 access key, the Kafka SASL username and password, or the MySQL or PostgreSQL password end up typed directly into CREATE TABLE statements or table function calls. Those statements then appear in query logs, in shared SQL scripts, in migration files and in tickets. Named collections are ClickHouse's mechanism for storing a set of connection parameters under a single name, so that a query or table definition refers to the collection instead of carrying the secret itself. Per the ClickHouse documentation, a named collection is a stored set of key-value parameters that can be used by table functions, table engines and dictionaries. Teams need them as soon as more than one person or pipeline connects to the same S3 bucket, Kafka cluster or source database, or when a secret must be rotated without editing every table definition.
This section covers how the collections are created and referenced, how access to them is granted and restricted, how to keep secrets out of query text and logs, and the limits you should confirm before relying on the feature. Creating a named collection is a privileged action, and the grants that let a user use a collection are separate from the grants that let a user read its contents, so the access model needs to be designed before the first CREATE NAMED COLLECTION runs. Check with your ClickHouse version's documentation which parameters are hidden from system tables and query logs, whether values are stored encrypted on disk, and how the query_masking_rules setting interacts with collection references. Those behaviors differ by version and deployment, so confirm them in a pre-production cluster that mirrors yours before you rely on them for audit or compliance evidence.
Where to define named collections and when each option fits
This table compares defining ClickHouse named collections in server configuration files with defining them through SQL statements. Treat each row as a question to confirm in the named collections and access control documentation for your version. The main takeaway is that file-based definitions suit change review through your existing pipeline, while SQL definitions depend on grants, logging and removing default-user access.
| Decision factor | Server configuration files | SQL statements |
|---|---|---|
| Where the definition lives | In server XML files, loaded when the server starts | Stored by the server and managed with CREATE, ALTER and DROP statements |
| Change control | Changes pass through your config management, code review and deployment pipeline | Changes are made by any account holding the matching grant, so review depends on access control |
| Auditability | Version control history shows who changed a file and when | Needs query or access logging to reconstruct changes; confirm what your version records |
| Who can change them | Anyone with write access to the config files on each node | Accounts with the relevant named collection privileges; confirm the exact grant names |
| Consistency across nodes | Every node needs identical files, so drift is possible if a deploy is missed | Confirm how a definition created on one node reaches the others in your cluster setup |
| Masking of secret values when displayed | Values sit in plain text in files, so restrict file permissions to the server account | Confirm whether displayed definitions mask secret keys in your version before granting read access |
| Keeping the default user out of administration | Config-defined collections may be usable by any session; confirm whether grants apply to them | The default user starts with all permissions, so restrict or disable it before any SQL-defined collection is created |
| Best fit | Stable infrastructure credentials that the platform team owns and changes rarely | Credentials that rotate often or teams that manage their own collections under explicit grants |
Setting up named collections for a first source
This sequence moves one source's credentials out of table definitions and query text and into a named collection that only approved roles can use. It finishes with a least-privilege account on the external system, a verified collection, and a record of who can use it.
Inventory every source that holds credentials
The data platform owner lists each table engine, table function and dictionary that contains a username, password, access key or SASL setting. Cover S3, Kafka, MySQL and PostgreSQL objects, and record the external system, the owning team and the ClickHouse roles that query or ingest through each object. Export the schema from the cluster so the list is complete. The step is done when every object in the export appears on the list with a named owner.
Create a least-privilege account on each system
On each external system, the platform administrator creates a dedicated account for this pipeline and grants only what it needs, such as read on specific tables, consume on specific topics or a single schema. Do not reuse administrator or shared application logins. Confirm the account by connecting with it directly, reading an allowed object and attempting an action outside its scope, which the system should refuse.
Define one named collection for the source
Create a named collection holding the host, port, database and credential fields for this source, using the method your team has approved, either a SQL statement or server configuration. Keep one collection per source so a rotation touches a single object. Then confirm, against the ClickHouse access control and named collection documentation, which listings reveal values. Run SHOW NAMED COLLECTIONS and query the relevant system tables, and confirm that the password does not appear in any output.
Grant use to the roles that need it
The ClickHouse administrator grants the collection's usage privilege only to the ingestion or query roles that depend on it. Make sure the default user and broad analyst roles receive no grant. Check the result with SHOW GRANTS for each role, and confirm that a user outside the granted roles receives a permission error when a query references the collection. Record each grant in the change log with its owner.
Rewrite table definitions to use the collection
The data engineer edits each table engine, table function or dictionary identified in the inventory so it refers to the collection by name, and removes inline credentials. Apply the change in a pre-production environment with representative data first, and keep the previous definitions saved for rollback. The step is complete when SHOW CREATE TABLE for each object shows the collection name, contains no literal secret, and the existing ingestion continues to flow.
Test under the intended role and scan for leaks
Connect as the intended role and run a representative query and an ingestion check against the source, confirming both succeed. Then search query_log and the server text logs for the credential string and for any fragment of it. If the secret appears anywhere, rotate it on the external system and the collection, and set up query masking before repeating the scan. The sequence is done when the test passes and the scan finds no matches.
Controlling who can use and read stored credentials
A named collection only keeps secrets out of query text if access to it is also controlled. The cards below cover the grants that let a user run queries against a collection, the grants that expose its contents, and the routine work that keeps both current as people, systems and credentials change.
Split the use grant from the read grant
Named collections use a separate privilege for referencing a collection in a table function or engine and for reading its stored values. Grant the use privilege to the roles that load data from S3, Kafka, MySQL or PostgreSQL, and keep the read privilege with a small administrative role. Before rollout, confirm in the access control documentation for your version which privilege names apply, so that an analyst who can query an S3 table cannot also print the access key.
Remove broad rights from the default user
The default user starts with all permissions, so any collection created while it is active can be referenced or inspected by whoever connects as default. Set a password or disable the account, then confirm that no application or script still connects as default before you grant collection access. Test with a non-admin login that a named collection cannot be read or altered through it.
Keep definitions out of shared scripts
A CREATE NAMED COLLECTION statement with literal passwords is stored in migration files, shell history and copied query logs. Create collections once through an administrative session, load secret values from the deployment's secret store or environment at provisioning time, and keep only the non-secret DDL in version control. Check your query log settings to confirm that statements are not written with plain values.
Review grants on a schedule
Grants accumulate as teams change, and a role that once needed a Kafka collection may keep it for years. Run a periodic review of which users and roles hold the use and read privileges on each collection, and compare the result with the owners recorded for each integration. Use SHOW ACCESS and the system tables for your version to produce the list, and record the review date and approver.
Plan rotation on both sides
Rotating a collection value does not rotate the credential in the external system, and the reverse also holds. Agree a rotation sequence for each source: create the new credential, alter the collection, confirm that queries and Kafka consumers still work, then revoke the old credential. Confirm with the MySQL, PostgreSQL, Kafka and object storage vendors which overlap window they support, and rehearse the change in a pre-production environment first.
Keeping secrets out of query_log and text logs
Secrets leak into logs before anyone notices, and named collections only help if the statements that create them never contained a password in the first place. A password typed into a CREATE TABLE ... ENGINE = S3 or CREATE NAMED COLLECTION statement can end up in the system.query_log table, in the server text log, in the client's command history, in a CI job's console output, and in any ticket or chat message where someone pasted the failing query. Each of these copies is a separate place to clean up, and most of them are outside the database's access controls. Start by listing every location where your team and pipelines have run ClickHouse statements that referenced S3, Kafka, MySQL or PostgreSQL credentials, including shell history on bastion hosts and the logs of deployment jobs.
For each location, check whether a literal secret appears. Query system.query_log for statements that contain keywords such as password, secret_access_key or access_key_id over the period before the named collection was introduced, and review the results with the people who ran them. Keep the check read-only and restrict who can see the output, because the search itself will display the secrets it finds. Then confirm the logging behavior for named collection statements in your target version, since whether those statements are recorded verbatim, and how the server text log treats them, is a version-specific detail. The ClickHouse production FAQ is a useful reference for choosing the version you will test against.
Treat any credential that has appeared in a statement or log as exposed. Rotate it at the source system, then remove or restrict the old log entries according to your retention policy. Query masking, which rewrites matching text in logs before it is stored, is a separate control with its own configuration and testing, and it is covered elsewhere in this group. Use it as a second layer, not as a substitute for keeping secrets out of statement text in the first place.
Limits to confirm before rolling named collections out
Which sources and parameters can named collections hold in my ClickHouse version?
Support for named collections depends on the table engines, table functions and parameter names your ClickHouse version accepts, so you cannot assume that every S3, Kafka, MySQL or PostgreSQL option can be moved into a collection. Check the named collections section of the ClickHouse documentation for the version you run, then list each parameter your pipelines use and confirm it is accepted. Keep a short matrix of source, parameter and version, and record which connection settings still have to be written in the query. Because the server is open source under the Apache License 2.0, the code and changelog for your exact release are the final reference when the documentation is unclear. Re-run this check on every upgrade, since the ClickHouse production FAQ recommends reading the changelog before upgrading because minor backward-incompatible changes can occur.
Can a user with a given grant see the contents of a named collection?
Whether a user can read a collection's stored values depends on the grants that control named collections in your version, so confirm the exact privileges before you give anyone access. Test with a non-admin account: create a collection, grant only the privilege needed to use it in a query, then check whether the same account can display its contents through system tables or SHOW statements. Treat any account that can read the stored secret as able to reuse it outside ClickHouse. Separate the people who create and rotate collections from the people who run queries that reference them. Access rules for users, roles and grants are described in the access control documentation, and that is the right place to confirm how these privileges behave in your release.
How do changes to a named collection reach other replicas?
Confirm how the collection definition is stored and synchronized in your deployment before you rely on it across a replicated cluster. If the collection is defined through configuration files, each node needs the same file and a reload, so a change applied on one server can leave others with old credentials. If it is defined through SQL, confirm whether the statement is applied across the cluster, for example with ON CLUSTER, and whether replicas that were offline pick up the change when they return. Check the behavior with a replica that is stopped during a credential rotation, then query each node to see which value it uses. The replication guide covers how replicated tables recover, but it does not replace testing for access objects, so verify this path yourself.
What happens to existing tables when a named collection is changed or dropped?
Confirm whether tables that already reference a collection read its values when they are created, when they are queried, or when the server starts, because that decides when a rotated password takes effect. Some table definitions may keep the connection details captured at creation time, while others may resolve the collection at each use. Dropping a collection that a table depends on can cause queries against that table to fail, and in some cases the table may still exist but stop working. Before any change in production, list every table, materialized view and Kafka or PostgreSQL integration that references the collection, then rehearse a rotation and a drop in a pre-production environment with representative data. Record the observed behavior for your version so the runbook reflects what actually happens.
Can a secret still appear in query text, query_log or server logs?
Yes, a named collection only removes secrets from the SQL you write if every statement and integration in your system references the collection rather than inline credentials. Check the query log, text logs and any tool that records statements, because a CREATE TABLE or ad hoc query with an inline password can still be stored in plain text. Run a few representative queries, then search the query_log and server logs for the password, token or access key you used in the test. Where a value must appear in a statement, plan query_masking_rules for it and confirm the masking pattern on every node. The access control documentation and the masking guidance in your version's documentation describe the controls, but the search test is what proves the result.
Does the backup of a ClickHouse cluster include named collection definitions?
Confirm this in your version before you rely on a restore, because a table backup and an access-control backup do not necessarily cover the same objects. Named collections are configuration and access objects, not table data, so a BACKUP of a database may not carry them to a new cluster. Write down where each collection is defined, whether in configuration files, SQL or a secrets system, and make sure that source is backed up and version controlled. Then rebuild a spare cluster from those sources and run a restore test that reads from an S3 or PostgreSQL source using only the restored collection. The backup documentation warns that a backup never test-restored will probably not restore properly, so schedule this test rather than assume it works.
How much effort does rolling out named collections take, and who does it?
The effort depends on how many pipelines hold inline credentials, how many replicas and environments you run, and whether your team already has a secrets process. Most of the cost is one-time: inventory of sources, testing of each supported parameter, rewriting of statements, and a rehearsed rotation runbook. Recurring cost comes from upgrades, new sources and periodic restore tests. Your own platform team can do this work if they have time to test in pre-production. DeployOpen can plan and implement the change, including the inventory, pre-production tests and runbook, as a fixed-scope engagement with scope and deliverables agreed in writing before work starts, and the quote follows from that written scope. Ask any provider to show how they will test each parameter on your version, and who owns credential rotation afterward.
Which of these answers should I trust without testing them myself?
None of them, until you have confirmed it in the documentation for your exact version and reproduced it in a pre-production environment with representative data. Behavior around collection visibility, replica synchronization and table dependencies is the kind of detail that changes between releases, and the documentation may not cover every edge case. Use the version your production cluster runs, not the latest release, and repeat the tests after each upgrade. Keep the results in your change record so that auditors and your own team can see what was tested, when, and against which version. Any vendor or partner should be able to show you the same evidence.
Official ClickHouse sources to read alongside this page
Read the ClickHouse access control documentation first, because named collections only protect credentials if the users and roles around them are locked down. The access control documentation covers users, roles, grants and row policies, and it recommends managing access through SQL and restricting the default user, which starts with all permissions. Before you decide who may use or read a named collection, check how your grants are structured today and confirm that the default user cannot reach the collections you create for S3, Kafka, MySQL or PostgreSQL.
Next, read the external authenticators documentation if your organization wants people and services to authenticate through LDAP, Kerberos, SSL X.509 certificates, an HTTP authenticator or JWT rather than local passwords. The external authenticators documentation explains how those sources fit into ClickHouse authentication. Use it to decide which secrets remain in a named collection, such as a service account password for a remote database, and which can be replaced by an identity provider flow that never touches SQL text.
Named collection syntax, parameter names and the exact options for each integration change between releases, so take them from the reference for the ClickHouse version you run, not from memory or from older blog posts. Confirm the supported version line against the ClickHouse security policy before you build, and test every named collection definition on a pre-production cluster with the same version you will run in production.
How DeployOpen scopes and delivers named collection work
The sequence turns a set of external sources and SQL access patterns into a written scope, a tested credential model and a handover that your team can review and run. Each step ends with a named artifact or check so you can confirm progress before the next one starts.
Agree a written scope of sources and roles
DeployOpen and your team list every external source that needs credentials, including S3 buckets, Kafka clusters, MySQL and PostgreSQL databases, along with the tables, dictionaries and table functions that read them. The scope also names the roles and users who will create, use and read each collection. The step closes when both sides sign the written scope, which defines deliverables and timelines before any configuration starts.
Inventory existing SQL and log exposure
Before changing anything, DeployOpen reviews the statements already in use, including DDL that embeds keys or passwords, scheduled jobs, client scripts and BI connections. The review also checks query log and text log settings to see where secrets may already be recorded. The output is an inventory of each credential location and its owner, which becomes the change list for the rest of the work.
Design the collection and access model
DeployOpen drafts one named collection per source and per environment, with credentials set through server configuration or a supported secret mechanism rather than inline SQL. It then maps each collection to a role that grants only the use it needs, and decides which principals may read collection definitions. Your security team reviews the draft, and the step ends with an approved access matrix that shows who can create, use and read each collection.
Test against representative queries in pre-production
DeployOpen builds a pre-production cluster that holds representative data and runs the queries your analysts and pipelines use against each named collection. Tests cover successful reads and writes, denied access for roles outside the matrix, and a check that query text and logs no longer contain credential values. Changes are promoted only after these tests pass, following the production FAQ's advice to validate with representative data and queries.
Plan the change for existing tables and jobs
Tables and dictionaries that already embed credentials are rebuilt or re-pointed to named collections according to a written change plan. The plan states the order of changes, the rollback path, the owners who confirm each dependent job still runs, and the points where credentials are rotated. Your team confirms the plan before production work starts, so no table is changed without an agreed fallback.
Apply changes and verify in production
DeployOpen applies the approved change plan in production, following the sequence agreed in the previous step. After each change it re-runs the access checks and confirms that the query log and text logs show no credential values for the affected statements. The step closes with a verification record that lists each collection, the role grants and the checks passed.
Hand over the access model with a review checklist
DeployOpen hands over the collection inventory, the role grants, the rotation steps and a review checklist that your team can run on its own schedule. The checklist covers new sources, changed grants, log masking settings and credential rotation. Your team confirms receipt and the first walkthrough, and the handover is complete when your administrators can reproduce the checks without DeployOpen.
Schedule ongoing reviews under a retainer
Access reviews after ClickHouse upgrades, new sources, or staff changes can be handled under a retainer agreed in writing with DeployOpen. The retainer scope defines which reviews are covered, how changes are requested and how results are reported. Your team should confirm the scope and response expectations before signing, since these terms are set in that agreement rather than in this page.
