ClickHouse Migrations That Manage Access: ch-migrate 0.9
2026-11-17 - 10 min read
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.

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:
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:
~ 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
access.yamldeclares users, roles, the roles each one holds, and grants, with a block per environment.access planandaccess apply:planexits 0 in sync, 1 with changes pending and 2 on an error;applygrants before it revokes.- A new project's migration role can't hand out access: it holds what migrations need, with no right to manage users or roles.
- Tested on ClickHouse Cloud, where users and roles are replicated and every replica is read at once.
- Coming from 0.8? Errors before any change now exit 2, and
bootstrapneeds ClickHouse 25.8 or later. See upgrading from 0.8.
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:
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 tenantThe 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>, withWITH GRANT OPTIONwhere 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 atenvironments.<env>.ssm.NAMEinconfig.yamlwhen there is one, and otherwise fromCH_<ENV>_<NAME>, so{secret: bi_password}in prod readsCH_PROD_BI_PASSWORD. A password is set only when its user is created, or again withaccess apply --reset-passwords, andch-migratenever 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: truedrops 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:
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
Ato roleBis grantedBbeforeapplyrevokesA. - 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, andapplyexits 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:
ch-migrate access show prod analyst bi > access.show.yaml# 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:
- analystCopy 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:
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:
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.yamldecides 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, plusSELECTon each system tablech-migratereads, with no grant option and no user or role management. A few grants, such asCLUSTER, are best-effort: they land as far as the admin can give them, and a shortfall is a warning.legacyis 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:
✓ 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,applyandbootstrapread every replica throughclusterAllReplicas. 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 toaccess_read_timeoutseconds (30 by default). The admin needsREAD ON REMOTEfor this. CLUSTERis already held. Cloud grantsCLUSTERto every login, so thecoreprofile's best-effortCLUSTER ON *.*counts as held:plan,apply,checkandbootstrapdon't warn about it, andapplydoesn't try it on every run.- Users go where they can be written. Cloud lists its read-only
jwtuser directory first.ch-migratecreates 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:
- Errors before any change exit 2, not 1. That covers a missing secret, a
config.yamlproblem and a server that can't be reached. Update CI jobs that test for exit 1. - ClickHouse 25.8 or later.
bootstrap, with or without anaccess.yaml, and the access commands that connect exit 2 on an older server, naming its version. initwritesaccess.yaml. Runninginitagain in an existing project adds one, and the nextbootstrapthen gives thecoreprofile instead of 0.8's grants. Delete the file to keep 0.8's behavior.- Without
access.yaml,bootstrapreports 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. RunningALTER USER <migration_user> IDENTIFIED BY '<secret>'as the admin fixes that. - Adopting
access.yaml. Useprofile: legacyif your migrations create users or roles or grant to others, start fromaccess show, and review the firstplan. With anaccess.yaml,create_dictionaryno longer grants todict_reader: declare the reader and itsSELECTthere.
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:
pip install ch-migrate-cli==0.9.0
ch-migrate --versionThen 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.