ClickHouse Migrations That Manage Access: ch-migrate 0.9

2026-11-17 - 10 min read
Daniel Young
Daniel Young
Founder, DRYCodeWorks

A grant added by hand to debug something, then forgotten. ch-migrate 0.9 declares ClickHouse users, roles and grants in a file, and access plan shows the drift.

ClickHouse migrations keep the tables in step across environments, but the logins that read and write those tables usually live somewhere else. Almost every ClickHouse engagement I take on ends up with the same set: an app that reads, an ingest service that writes, a role for the analysts' BI tool, and a role per tenant that row policies filter on.

An isometric grid of keyhole tiles inside a dashed outline; one orange tile sits outside the outline, apart from the rest.

I reproduced the usual way that set drifts on a local ClickHouse 26.3 server. The analysts' role, analyst, reads one summary table. To debug a revenue report, someone logged in as the admin gives it the raw orders table too:

sql
GRANT SELECT ON shop_prod.orders TO analyst;

The report gets fixed and the grant stays. The BI tool's login holds analyst, so it can now read every order, customer emails included, and nothing records that. No migration ran and no file changed.

The version table records which migrations ran. It says nothing about who can read the tables they made.

On ClickHouse Cloud, the console's Users and roles page manages who can use the console and its services. Since ClickHouse 26.4, a custom role there can carry a Data policy, which creates a cloud:-prefixed role in each service you select. Database users and their grants are created with SQL, CREATE USER and GRANT, one service at a time. What's missing is a file, per environment, that says which users and roles a service should have and what each one may read, so you can compare the service against it.

ch-migrate is my open-source ClickHouse migration tool, and 0.9 adds that file. access.yaml declares users, roles and grants, ch-migrate access plan compares a server with it, and access apply makes the server match, logged in as the admin. On the server above, plan finds the debug grant:

ch-migrate access plan prodtext
~ adopt role analyst: it exists on the server, and access.yaml now manages it
- revoke SELECT ON shop_prod.orders from role analyst
→ 1 change pending for prod (access.yaml).

It exits 1. ch-migrate access apply prod sends the revoke, the BI login gets ACCESS_DENIED on orders again, and the next plan prints ✓ In sync for prod (access.yaml): nothing to change. and exits 0. (New to the tool? Start with ClickHouse migrations with Alembic, then ClickHouse migrations that catch schema drift, the 0.8 post.)

What's new in 0.9

Building it in public

ch-migrate is MIT-licensed and built in the open, one release at a time, each with a post and a short demo. I want it to be useful to any team running ClickHouse in more than one environment, on ClickHouse Cloud or self-hosted, not only the teams I work with.

0.9 started from my own projects. Almost all of them need more than one user and role, and the ones that serve several customers from one table need row-level security too. Until now, ch-migrate's answer was a migration role strong enough to create users and grant to them, which left access spread across the version history. For 0.9 I wanted one file per project that says who can do what in each environment, and a command that says where a server differs from it. If you manage ClickHouse access some other way, I'd like to hear how. Bug reports and ideas are welcome on GitHub.

Declaring ClickHouse users and roles in access.yaml

ch-migrate init writes an access.yaml next to config.yaml. Here's one for the logins above:

access.yamlyaml
migration_role:
  profile: core
  grants:
    - CREATE ROW POLICY, ALTER ROW POLICY, DROP ROW POLICY ON {db}.*

roles:
  app_reader:
    grants:
      - SELECT ON {db}.orders
      - SELECT ON {db}.daily_revenue
  ingest_writer:
    grants:
      - INSERT ON {db}.events
      - INSERT ON {db}.orders
  analyst:
    grants:
      - SELECT ON {db}.daily_revenue
  tenant_a:
    grants:
      - SELECT ON {db}.events
  tenant_b:
    grants:
      - SELECT ON {db}.events

users:
  app:
    password: {secret: app_password}
    roles: [app_reader]
  ingest:
    password: {secret: ingest_password}
    roles: [ingest_writer]
  bi:
    password: {secret: bi_password}
    roles: [analyst]
  portal_a:
    password: {secret: portal_a_password}
    roles: [tenant_a]
  portal_b:
    password: {secret: portal_b_password}
    roles: [tenant_b]

environments:
  dev:
    roles:
      analyst:
        grants:
          - SELECT ON {db}.*     # dev holds only test data
    users:
      portal_b: {absent: true}   # dev has one test tenant

The migration_role block comes up again under row policies. The rest of the file follows a few rules:

  • Grants use ClickHouse's own words: <privileges> ON <target>, with WITH GRANT OPTION where you want it. {db} becomes each environment's database, and {project}, {env} and {migration_role} work too.
  • Roles held. roles: on a user or a role grants it those roles. default_roles: picks which of a user's roles are active at login; without it, all of them are.
  • Passwords are {secret: NAME}, read from the SSM path at environments.<env>.ssm.NAME in config.yaml when there is one, and otherwise from CH_<ENV>_<NAME>, so {secret: bi_password} in prod reads CH_PROD_BI_PASSWORD. A password is set only when its user is created, or again with access apply --reset-passwords, and ch-migrate never reads one back.
  • A block per environment merges over the rest of the file: mappings merge and lists replace. Here, analysts on dev read the whole database, and dev, which has one test tenant, drops portal_b.
  • absent: true drops a user or role, in one environment when it sits in that environment's block. Renames aren't supported: declare the old name absent and add the new one.

ch-migrate access validate ENV checks the file offline, with no server and no secrets. With --sql, it also prints the SQL the file stands for, so a DBA can review it. For dev, the end of that output shows the environment block at work:

ch-migrate access validate dev --sql (end)sql
GRANT SELECT ON shop_dev.* TO analyst;
GRANT SELECT ON shop_dev.events TO tenant_a;
GRANT SELECT ON shop_dev.events TO tenant_b;
DROP USER IF EXISTS portal_b;

Each password in that SQL is an unquoted placeholder, such as IDENTIFIED BY <secret:bi_password>, so it fails to parse until someone puts the real secret in.

Plan, then apply

ch-migrate access plan ENV logs in as the admin and prints one line per change without making any: + for a create or grant, - for a revoke or drop, and ~ for a declared user or role that already exists and has changes pending. ch-migrate keeps no state, so adopting a role and finding drift on one look the same. That's why the opening's plan says "adopt" about a role an earlier apply created.

The exit code is meant for CI: 0 in sync, 1 with changes pending, 2 on an error, so a server that can't be reached never passes as in sync. A CI job that runs plan logs in as the admin, so it needs the admin's password as a secret.

ch-migrate access apply ENV makes the changes plan lists, as the admin, in a fixed order: creates, role memberships, grants, replaces, default roles, revokes, then drops. (A replace is one GRANT ... WITH REPLACE OPTION, sent when a grant narrows, because ClickHouse folds a narrower grant into the broader one.) Three rules keep a run that fails halfway from cutting anyone off:

  • Grants before revokes. A user moving from role A to role B is granted B before apply revokes A.
  • Revokes wait for success. Revokes, replaces and default-role changes run only if every earlier change succeeded, for every user and role. If one failed, they're all skipped and each is named in a skipped: warning, so old access survives, and apply exits 1.
  • Undeclared means untouched. Users and roles the file doesn't declare are never touched, and one is dropped only when it's declared absent: true.

On ClickHouse Cloud, the third rule means apply leaves alone the cloud:-prefixed roles that a console Data policy creates. The file does own the role memberships of each user it declares, though. On my local server, I granted a hand-made cloud:data_viewer role to the declared bi user, and the next plan listed - revoke role cloud:data_viewer from user bi. Listing the role under bi's roles: doesn't help: names in access.yaml are letters, digits and underscores, so validate rejects the colon. A user that should keep a cloud: role belongs outside the file.

Adopting users and roles that already exist

Declaring a user or role that already exists adopts it, and the first apply revokes from it every grant the file leaves out. So start from what the server holds:

terminalbash
ch-migrate access show prod analyst bi > access.show.yaml
access.show.yamlyaml
# What the server holds for prod, in access.yaml's form (`ch-migrate access show`).
# Passwords are never shown. For ch-migrate to create a user, add its
# `password: {secret: NAME}`.
roles:
  analyst:
    grants:
      - SELECT ON {db}.daily_revenue
users:
  bi:
    roles:
      - analyst

Copy what you keep into access.yaml and review the first plan before the first apply. Never redirect show into access.yaml itself: the shell empties the file before show runs.

Here's the opening story at half speed, on a demo server: the analysts' role in access.yaml, the grant someone adds by hand, plan listing the revoke and exiting 1, apply sending it, and plan in sync.

Row policies stay in ClickHouse migrations

0.9 doesn't manage row policies. It manages the users and roles they're attached to. The policy that limits tenant_a to its own rows stays a migration, written to be safe to run twice:

…_tenant_row_policies.up.sqlsql
CREATE ROW POLICY IF NOT EXISTS tenant_a_rows ON {db}.events
FOR SELECT USING tenant_id = 'a' TO tenant_a;

CREATE ROW POLICY IF NOT EXISTS tenant_b_rows ON {db}.events
FOR SELECT USING tenant_id = 'b' TO tenant_b;

In a new project, that migration fails, because the core migration role has no row-policy rights:

ch-migrate up prodtext
Code: 497. DB::Exception: migration_prod: Not enough privileges. To execute this query, it's necessary to have the grant CREATE ROW POLICY ON shop_prod.events. (ACCESS_DENIED)

The migration_role.grants entry at the top of the example file gives them to the migration role: access apply adds them, and so does bootstrap on a new environment. The other way is profile: legacy, which keeps 0.8's grants, row-policy management among them.

The policy names tenant_a, so on a fresh environment the role has to exist first. When I ran up straight after bootstrap, it stopped at the policy with There is no role `tenant_a` in `user directories`. (UNKNOWN_ROLE). Run bootstrap, then access apply, then up; ClickHouse accepts grants on tables that don't exist yet. Afterwards plan still reports in sync, because it compares users, roles and grants and leaves policies to the migrations.

access.yaml decides who the tenant roles are and what they can read. The migration decides which rows they see.

A migration role that can't hand out access

0.8 gave every project's migration role the same grants. Among them were the rights to create users and roles, grant any role, and manage row policies, all with grant option, so the migration login could hand out access, including to itself.

In 0.9, access.yaml names one of two built-in profiles for the migration role:

  • core, the default for new projects, holds what migrations need on the project's database, plus SELECT on each system table ch-migrate reads, with no grant option and no user or role management. A few grants, such as CLUSTER, are best-effort: they land as far as the admin can give them, and a shortfall is a warning.
  • legacy is 0.8's migration role exactly. Use it when your migrations create users or roles, or grant to other users.

On my test server, ClickHouse refused both GRANT SELECT ON shop_prod.orders TO analyst and CREATE USER from a core migration user.

migration_role.grants adds to either profile. bootstrap and apply only ever add to the migration role, so two project directories can share one, and a project adopting access.yaml keeps the 0.8 grants its old migrations rely on. plan marks a grant the role holds beyond what's declared with !, without changing the exit code; revoke those by hand.

bootstrap now runs on the same engine as apply. It creates the database and the migration role and user, gives the role its profile and migration_role.grants, then logs in as the migration user to check the role's required grants. Every other user and role is access apply's job.

Some servers give ch-migrate no admin login, and a DBA runs the SQL from access validate --sql instead. There, ch-migrate access check ENV logs in as the migration user and uses CHECK GRANT to confirm it holds every grant of its profile and migration_role.grants:

ch-migrate access check prodtext
✓ user migration_prod holds every required grant of role shop_migration_role (9).

It checks only the migration role, not the rest of access.yaml. It exits 0 when every required grant is held and 1 when one is missing, naming each.

Tested on ClickHouse Cloud

As with every release since 0.5, I ran the integration suite against my ClickHouse Cloud test service as well as local containers. Three things in 0.9 come from Cloud, where users and roles are replicated:

  • Every replica is read at once. access show, plan, apply and bootstrap read every replica through clusterAllReplicas. A row missing on every replica is absent at once, with no wait, and replicas that disagree are read again until they agree, for up to access_read_timeout seconds (30 by default). The admin needs READ ON REMOTE for this.
  • CLUSTER is already held. Cloud grants CLUSTER to every login, so the core profile's best-effort CLUSTER ON *.* counts as held: plan, apply, check and bootstrap don't warn about it, and apply doesn't try it on every run.
  • Users go where they can be written. Cloud lists its read-only jwt user directory first. ch-migrate creates users and roles in the first directory that can take one, and that's the one the access commands read.

1,167 unit tests pass on each of Python 3.10 through 3.14. The integration suites ran against local ClickHouse containers, a cluster, ClickHouse 25.8 and my ClickHouse Cloud test service.

Upgrading from 0.8

There's no upgrade-env this time, and a project without an access.yaml keeps 0.8's grants. A loose requirement such as ch-migrate-cli upgrades to 0.9, so check these first:

  1. Errors before any change exit 2, not 1. That covers a missing secret, a config.yaml problem and a server that can't be reached. Update CI jobs that test for exit 1.
  2. ClickHouse 25.8 or later. bootstrap, with or without an access.yaml, and the access commands that connect exit 2 on an older server, naming its version.
  3. init writes access.yaml. Running init again in an existing project adds one, and the next bootstrap then gives the core profile instead of 0.8's grants. Delete the file to keep 0.8's behavior.
  4. Without access.yaml, bootstrap reports differently. It creates the migration role and user first, tries every grant, and names each one that fails, exiting 1 if any did. A best-effort grant that lands short is now a warning. It ends by logging in as the migration user and exits 1 if it can't, for example because the user's password differs from its secret. Running ALTER USER <migration_user> IDENTIFIED BY '<secret>' as the admin fixes that.
  5. Adopting access.yaml. Use profile: legacy if your migrations create users or roles or grant to others, start from access show, and review the first plan. With an access.yaml, create_dictionary no longer grants to dict_reader: declare the reader and its SELECT there.

To stay on 0.8, pin ch-migrate-cli==0.8.0. The CHANGELOG lists every change.

Try it

0.9.0 is on PyPI. Try it on a test server:

terminalbash
pip install ch-migrate-cli==0.9.0
ch-migrate --version

Then follow the README's access section. And whatever manages your ClickHouse access today, run SHOW GRANTS FOR one role you think you know, and compare it with what you'd write down. If you'd like help getting ClickHouse migrations and access control in place, get in touch.