When ClickHouse row policies restrict which rows each user sees
Row policies are the right tool when several tenants, regions or business units share one self-managed ClickHouse cluster and must not see each other's rows. Instead of creating a separate table or database for each group, you keep one copy of the data and attach a filter to a table that applies to named users or roles. A row policy is that filter. When a user queries the table, ClickHouse silently adds the policy condition, so the user only receives rows that match it. The filter is defined once on the table, so teams do not have to remember to add a WHERE clause in every query or dashboard.
Separate tables or databases still make sense when groups need different schemas, different retention, or hard isolation for audit or regulatory reasons. Row policies are a better fit when the schema is shared and the difference between groups is a column value such as tenant_id or region. The access control documentation covers row policies alongside users, roles, settings profiles and quotas, so they should be designed together with the rest of the access model rather than bolted on later. Confirm with your security team whether a filter on shared data meets your isolation requirement, since that is a policy decision the platform cannot make for you.
Before you write any policy, decide which column identifies each group, which users or roles it applies to, and who is allowed to bypass it, such as an administrative role. Also confirm how the default user is handled, because it starts with broad permissions and can undermine every policy if left unrestricted. A policy that is correct on paper still fails if a user can query through a path you did not filter, so plan the test cases that prove the filter holds before you go live.
How permissive and restrictive row policies combine
Row policies decide which rows a user can read, and the combination rules decide whether a policy grants access or narrows it. The cards below cover the logic a designer must get right before writing any CREATE ROW POLICY statement, and the checks that prove the result matches the intent.
Permissive policies grant access
A permissive policy adds rows to what a user can see. When several permissive policies apply to the same table and user, a row is visible if any one of them matches. Designers often write one policy per tenant or region, so each new grant widens access. Before rollout, list every permissive policy attached to each role and confirm that no grant is broader than the business rule it implements.
Restrictive policies narrow access
A restrictive policy must also match before a row is returned. It cannot grant rows on its own, so it works as an additional filter applied on top of the permissive grants. A common use is a region or data residency condition that applies to every role, including future ones. Test that a restrictive policy still applies when a new permissive policy is added later.
Combining permissive and restrictive logic
The effective filter is the permissive set, combined with all restrictive conditions. A row must satisfy at least one permissive policy and every applicable restrictive policy. Write the expected result as a truth table for a small set of sample rows, then compare it with what each test user actually receives. Confirm the exact evaluation rules for your version in the access control documentation before relying on them.
Policies that apply to no one
A table with row policies defined can return no rows to a user who matches none of the permissive policies. This is the failure that surprises teams most, because a newly created policy can hide data from existing reports. Check which roles and users each policy targets, and confirm that analysts and service accounts still see the rows they need after every change.
Role and user assignment
Policies attach to users and roles, and a user inherits policies from the roles granted to them. Map each policy to the role that owns the business rule, not to individual people, so that reassignment does not require editing policies. Review assignments with SHOW ROW POLICIES and SHOW ACCESS output, and record who approved each grant.
Validating the combined result
Test the combination with at least one user per role, each querying the same table and comparing row counts and sample keys with the expected output. Include a user who holds two overlapping permissive policies and a user who holds a restrictive policy with no permissive grant. Repeat these tests after every upgrade, since evaluation details must be confirmed for each version you run.
Row policy design options and when each fits
This table compares six common ClickHouse row policy designs, with the typical use, main risk and questions to settle before building each one. Read each row as a starting point, because the right design depends on how the filter column is populated and which accounts read the table. The main takeaway is that a policy is only as strong as the column it filters and the roles it is assigned to.
| Design | Typical use | Main risk | Confirm before building |
|---|---|---|---|
| One permissive policy per tenant | Shared multi-tenant table where each tenant role sees only its own tenant_id rows | Policy sprawl, and a new tenant that is onboarded without its policy sees nothing or is missed in reviews | How tenant_id is set at ingestion, and whether each tenant maps to exactly one role |
| Region filter | Data residency or regional analyst access, such as EU staff limited to EU rows | A mis-tagged or empty region value silently hides rows or exposes them to the wrong group | The region source of truth, and the behavior for null or unknown region values |
| Restrictive mandatory tenant filter | A baseline condition that must hold for every role, combined with AND against permissive policies | Locking out reporting or maintenance roles that were never excluded from the baseline | Which roles the restrictive policy applies to, and the explicit exclusion list for service accounts |
| Permissive policies with separate team roles | Teams such as finance and operations each receive a role with its own row filter | A user granted several roles gets the union of their visibility, which can widen access | A periodic review of multi-role grants, and who approves each new role assignment |
| Policies for ingestion and ETL accounts | Loaders and pipelines that insert data and sometimes read back to verify it | Read-side filters can break verification queries or reconciliation jobs that expect full counts | Which accounts write, which read, and whether reconciliation needs a separately scoped role |
| Policies across derived and distributed objects | Distributed tables, materialized views or copies used for dashboards and exports | Users query an unfiltered source table or underlying shard table and bypass the intended policy | That every readable table and view is covered, and that direct grants to base tables are removed |
Setting up tenant and region row policies step by step
This sequence turns an agreed access model into enforced row filters on a self-managed ClickHouse cluster, with a check at each stage so the result can be shown to a reviewer.
Confirm the access model and filter columns
Before any SQL runs, the data owner and the platform team agree which principals need which rows, such as a tenant, a region or a business unit. Map each rule to a concrete column in the target tables, note its data type, and confirm that every table holding the protected data has that column populated consistently. Record the mapping in a short written matrix that reviewers can check later. The step is done when every protected table has a documented filter column and the owner has signed off on the matrix.
Restrict the default user
The default user starts with broad permissions, so the platform team restricts it before creating anything else. Set a password or disable the account according to the access design, and limit where it can connect in the server configuration. Confirm the result with a login attempt from an unapproved network and from an approved one. The step is done when the default user can no longer read protected tables by default and the connection test results are recorded.
Create named users and roles through SQL
Using an administrative account, create one named user per person or service, then create roles that match the access matrix, such as a regional analyst role and a tenant reporting role. Grant the roles only the table privileges they need and assign them to users. Keep administrative rights in a separate role. Run SHOW ACCESS and review the output against the matrix. The step is done when every user and role appears in the review with the grants you expect and nothing extra.
Create row policies with USING conditions
Write a CREATE ROW POLICY statement for each protected table, with a USING expression that compares the filter column to the value the principal is entitled to see. Start with permissive policies for the normal read path. Use a restrictive policy only where a rule must always apply regardless of other grants. Test each expression on a sample query by an administrator first. The step is done when each policy compiles and the administrator's test query returns the expected row count for a known filter value.
Assign policies to roles and confirm the bindings
Attach each row policy to the role that should be subject to it, and make sure the roles that must be filtered are not also granted a broader policy that defeats the intent. Run SHOW ROW POLICIES to list every policy, its table, its condition and the roles it applies to. Compare the output with the access matrix line by line. The step is done when the listed bindings match the matrix exactly and no protected table lacks a policy for a role that needs one.
Verify with test users in each scope
Create test users with the same roles as production users, using data with known values in each tenant and region. Query each protected table as each test user and confirm that only permitted rows return, including aggregations and counts. Attempt to read the same rows through joins, subqueries and views, and confirm that the filter still applies. Record the results as evidence. The step is done when every test user returns only its own rows and the bypass attempts return no protected data.
Review bypass paths and document the final state
Check that no other grant gives the same roles access to the underlying tables through another route, such as a broader role, a direct privilege on a different database, or a table function. Repeat SHOW ACCESS and SHOW ROW POLICIES after any change and store the output with the access matrix. Schedule the same review whenever users, roles or tables change. The step is done when the reviewed outputs, test results and matrix are stored together and the owner accepts the record.
Testing that row policies cannot be bypassed
Treat row policy testing as proof, not configuration review. Create one test user per tenant or region, plus a user with no policy attached and a user that should be denied entirely. Log in as each identity, then compare count() and sample rows against a known result set you built from the source data. Run the same checks through the HTTP interface and the native client, because each path authenticates and applies settings in its own way. Record the expected and actual results in a file that can be rerun, so a failed check is visible rather than a matter of memory.
Policies apply to the table they are defined on, so the bypass tests must cover every way that table can be read. Run aggregates, GROUP BY queries, joins against unrestricted tables, subqueries and IN filters, and confirm the restricted user never sees another tenant's keys through any of them. Then test every view and materialized view that reads the protected table, including the grants on those objects. Confirm that the view user cannot select from the base table directly, and that a user without a policy sees nothing or is denied as designed. Check grants for SELECT on the base table and on system tables, and confirm which role holds ACCESS MANAGEMENT, since that privilege can change policies. Review the default user too: it starts with all permissions, so a password-less or unrestricted default account can read every row regardless of the policies you wrote. Ask your implementation team to show the SHOW ACCESS output for each identity as evidence.
Repeat the full suite after every ClickHouse upgrade, every change to a role or grant, and every new view or materialized view. Upgrades can change how settings and access checks behave, so the ClickHouse production FAQ advice to read the changelog before upgrading applies to access behavior too. Store the suite in version control, run it in a pre-production environment with representative data, and fail the release if any tenant count differs from the expected value. Confirm with your vendor or partner which checks run automatically and who owns the failing-test response.
Security risks in row-level filtering
A row policy only restricts the users it applies to, so most failures come from who holds broad rights, which role the policy is attached to, and what happens when new tables appear. Each card names one risk and the control to review before go-live.
Broad grants bypass policies
Users with administrative rights or broad SELECT grants on the base table can read rows that a policy would hide from analysts. Row policies filter what a restricted user sees, so any account that is not covered by the policy keeps full access to the underlying data. Review every grant on the table and list which accounts can read it without going through the filtered path. Confirm that analyst roles are the only route to the filtered data.
Default user starts with all permissions
The default user begins with all permissions, so a server left with that account reachable is an unfiltered path to every row. The access control documentation recommends restricting the default user and managing access through SQL. Before attaching any policy, confirm the default account has a password or is disabled, and that no client application connects with it.
Policies attached to the wrong role
A policy only applies to the users and roles named in its TO clause, so a policy written for one team can leave another team unfiltered. Check that each policy targets the intended role, and that a user who holds several roles gets the filtering you expect. Test each role separately with a sample of rows that should be hidden, rather than assuming the grant structure is correct.
Missing policies on new tables
Each table needs its own policy, so a new table or view created without one is readable by every user who has a grant on it. This often happens when a team adds a derived table for a dashboard. Make policy creation part of the table deployment checklist, and run a periodic query against system tables to list tables that have no row policy for sensitive columns.
Ingestion roles reading restricted data
Ingestion accounts often receive wide rights so pipelines can insert and check results, which can also expose rows from other tenants or regions. An insert-only role should not hold SELECT on tables it does not need to verify. Review the grants of every pipeline account and confirm which tables each one can read, and keep those accounts separate from analyst roles.
Filter columns users can change
A policy filter based on a column that a user can update, or on a session setting the user controls, can be altered to reveal other rows. Confirm that filter columns are written only by trusted ingestion paths and that users cannot grant themselves settings that feed the filter. Review ALTER and UPDATE rights on tables with policies, and test that a user cannot change their own tenant or region value.
Row policy questions teams ask before hiring help
How much effort does a ClickHouse row policy design take?
Effort depends on how many tenants, tables, roles and exceptions the design has to cover, and on how much of the access model is already written down. A small design with one filter column, a few roles and a single fact table is a short, bounded piece of work. A design that spans many tables, regions and service accounts takes longer because each combination needs a policy decision and a test. Ask any partner for a written scope that lists the tables, roles and exception cases before accepting a quote, since that list is what the estimate is built from.
What makes row policy work more expensive?
The main cost drivers are the count and shape of the access rules. Each additional tenant, table, role and exception multiplies the policies to write and the cases to verify. Other factors include:
- Tenant models that rely on several columns or lookup tables instead of one tenant key
- Mixed permissive and restrictive policies that must interact correctly
- Existing users, grants or
users.xmlsettings that must be migrated without locking anyone out - Integrations, such as dashboards or ingestion jobs, that run under shared accounts and need separate handling
Ask the quote to show the effort for each of these items separately.
What does our team need to provide before the work starts?
The work goes faster when your team supplies three things: a clear tenant model, representative test data and named access owners. The tenant model states which column identifies a tenant or region, how users map to tenants, and which roles may see across tenants. Test data should include rows for every tenant and edge case, such as users with no tenant or accounts that belong to several regions. Access owners are the people who approve who may see which rows and who can change those decisions later. Without them, policies are written against assumptions that often need to be redone.
Can an internal team set up ClickHouse row policies alone?
Yes, a team with ClickHouse administration skills can create row policies with SQL, and the access control documentation describes the SQL-driven workflow. The harder part is proving the result. Policies interact with roles, default grants and the default user, which starts with all permissions and should be restricted. An internal team should plan time for reviewing grants, testing with each role and confirming that no path reads the base table around the policy. If your team has a security reviewer who can check the tests, the work is well within reach. If not, an outside review of the design and tests is a common middle option.
How long does testing a row policy design take?
Testing time scales with the number of roles, tables and policy combinations, not with the number of rows in the table. A useful test plan covers each role against each tenant, checks that restrictive policies cannot be widened by a permissive one, and confirms that common bypasses are closed, such as direct queries against a base table, joins that expose filtered columns and queries run by service accounts. Ask any provider to state which of these cases are in scope and how each one will be recorded as evidence. The duration should follow from that list, and you should not accept a testing estimate that does not name the cases it covers.
How are changes to a row policy scope handled?
Most DeployOpen engagements are fixed-scope, which means the scope, deliverables and timelines are agreed in writing before work starts. When a new tenant, table or role appears during the work, the change is written up as a change to that agreement, with its effect on deliverables and timing stated before the extra work begins. This keeps decisions about who sees which rows in your hands. For changes after handover, ongoing support and policy reviews can be arranged as a retainer. Ask for the change process in writing before signing so you know how requests are priced and approved.
What should we check in a row policy design before approving it?
Engineers should confirm that every role has an explicit, tested answer for each tenant, that the default user is restricted, and that the policies cover every table that holds tenant data, including materialized views and copies. Ask the reviewer to show the query log entries that prove a restricted role cannot read rows outside its tenant. Also check that the policy definitions are kept in version control and that a change to one policy triggers the same tests as the original. A design that cannot show this evidence is not yet ready for production.
Where to check exact row policy syntax and behavior
Treat the ClickHouse access control documentation as the reference for row policy syntax, how policies attach to users and roles, and how grants interact with them. The ClickHouse access control documentation covers users, roles, row policies, settings profiles and quotas in one place. Before you write policies, read the sections on row policies and on granting privileges, and confirm the exact clause order and the permissive and restrictive behavior against the version you run. Syntax and defaults are the first things to verify, because a policy written from memory can compile and still filter differently than intended.
Read the release notes for your own server version before you finalize a policy design or an upgrade. Row policy behavior can change between releases, and the ClickHouse releases page lists each version's changes. Find your running version with a query against the server, then check the notes for that line and for every release between it and the version you plan to deploy. If you run an LTS line, confirm that it is still within its support window, since the ClickHouse security policy lists which versions receive security updates.
Keep a short internal record of the behavior you verified: the version, the policy definitions, and the test queries that confirmed each filter. Ask your implementation partner or your own DBA to show the same evidence for the version in your environment, not only the documentation text. When the documentation and your test results disagree, trust the tests on your version and raise the difference with ClickHouse before you rely on the policy for tenant isolation.
How DeployOpen scopes and delivers row-level security
DeployOpen treats ClickHouse row policies as part of the access model, not an afterthought. We scope which tenants, regions and roles must see which rows, then build, test and document the policies inside your own infrastructure so the controls can be reviewed and maintained by your team.
How we scope
- Map every user, role and service account to the rows it may read, including ingestion and admin access.
- Document tenant and region filter logic, and which tables the policies must cover before any build starts.
- Agree the scope in writing, with deliverables and timelines fixed before work begins, and quote from that scope.
How we build
- Write row policies in SQL with permissive and restrictive combinations reviewed against the access design.
- Restrict the default user and pair policies with roles and settings so grants cannot be widened unnoticed.
- Test that queries from each role return only permitted rows, and attempt bypass paths such as direct table access.
How we hand over
- Deliver the policy definitions, role grants and test scripts as versioned files your team can rerun.
- Document the access model, the reasoning behind each filter and the steps to add a tenant or region.
- Walk your operators through a review routine using SHOW ACCESS and policy queries so changes stay visible.
How we support
- Provide ongoing support, upgrades and continuous testing as a retainer when your team wants them.
- Re-run the isolation tests after ClickHouse upgrades, since version changes can affect access behavior; confirm this against the release changelog.
- Review policy changes as tenants, regions and staff roles change, so the access model stays aligned with your requirements.
