CREATE ROW POLICY

Creates a row policy, i.e. a filter used to determine which rows a user can read from a table.

Row policies make sense only for users with readonly access. If a user can modify a table or copy partitions between tables, it defeats the restrictions of row policies.

Syntax:

-- Multiple names on one table target
CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name [, ...]
    [ON CLUSTER cluster_name]
    ON { [db.]table | db.* }
    [IN access_storage_type]
    [[FOR SELECT] USING {condition | NONE}]
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]

-- One name on multiple table targets
CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name
    [ON CLUSTER cluster_name]
    ON { [db.]table | db.* } [, ...]
    [IN access_storage_type]
    [[FOR SELECT] USING {condition | NONE}]
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]

-- Mixed packing: each name paired with its own table target
CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE]
    policy_name ON { [db.]table | db.* } [, policy_name ON { [db.]table | db.* } ...]
    [ON CLUSTER cluster_name]
    [IN access_storage_type]
    [[FOR SELECT] USING {condition | NONE}]
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]

ParserRowPolicyNames accepts three packing forms (not a full Cartesian product):

Multiple names, one target — pol1, pol2 ON table1 creates each listed name on that single table (or db.*).
One name, multiple targets — pol1 ON table1, table2 creates the same short name on each listed target.
Mixed pairs — p1 ON t1, p2 ON t2 creates each name only on its paired target.

A multi-name list cannot be combined with a multi-table ON list in one group: p1, p2 ON t1, t2 is rejected. After a multi-name group, you also cannot append another comma-separated name ON target group in the same statement. Optional ON CLUSTER applies to the whole statement (one cluster name). ClickHouse does not accept a different ON CLUSTER per policy name packed into a single create — run separate CREATE ROW POLICY statements when policies must be created on different clusters. CREATE ROW POLICY requires the CREATE ROW POLICY privilege on the table the policy is created on. OR REPLACE throws away an existing policy of the same name, including which roles it applies to, so it additionally requires the DROP ROW POLICY privilege on that table. The DROP ROW POLICY privilege is required whether or not the policy already exists, so the statement cannot be used to find out which policies exist.

Multiple names and tables

Valid:

-- Several policy names, one table
CREATE ROW POLICY pol1, pol2, pol3 ON table1
    FOR SELECT USING id = 1
    TO accountant;

-- One policy name, several tables
CREATE ROW POLICY IF NOT EXISTS pol1 ON table1, table2, table3
    FOR SELECT USING id = 1
    TO accountant;

-- Mixed packing: different name per table
CREATE ROW POLICY p4 ON db.table, p5 ON db2.table2
    USING a = b;

-- Same policy on several tables, on a cluster
CREATE ROW POLICY IF NOT EXISTS pol1 ON CLUSTER replicated_cluster ON table1, table2
    FOR SELECT USING id = 1
    TO accountant;

Invalid:

-- Multi-name × multi-table in one ON-group (not a Cartesian product)
CREATE ROW POLICY p1, p2 ON t1, t2
    FOR SELECT USING id = 1
    TO accountant;

-- Different clusters per name in one statement
CREATE ROW POLICY pol1 ON CLUSTER cluster1 ON table1, pol2 ON CLUSTER cluster2 ON table2

USING clause

Defines a filter condition for a table. A user can only see rows for which the condition is true (evaluates to a non-zero value). This is similar to adding an extra WHERE condition to every query the user runs against the table. For example, the following policy limits analyst_role to rows from the EU:

CREATE ROW POLICY region_filter ON db.orders
USING region = 'EU'
TO analyst_role;

With this policy, SELECT * FROM db.orders returns the same rows as SELECT * FROM db.orders WHERE region = 'EU' would.

TO Clause

In the TO section you can provide a list of users and roles this policy should work for. For example, CREATE ROW POLICY ... TO accountant, john@localhost. Keyword ALL means all the ClickHouse users, including current user. Keyword ALL EXCEPT allows excluding some users from the all users list, for example, CREATE ROW POLICY ... TO ALL EXCEPT accountant, john@localhost Roles named in the TO section, including those after ALL EXCEPT, are matched against the current user’s enabled roles (system.enabled_roles), not against every role granted to the user, so SET ROLE can change which policies apply.

AS Clause

It’s allowed to have more than one policy enabled on the same table for the same user at one time. So we need a way to combine the conditions from multiple policies. By default, policies are combined using the boolean OR operator. For example, the following policies:

CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 TO peter, antonio

enable the user peter to see rows with either b=1 or c=2. The AS clause specifies how policies should be combined with other policies. Policies can be either permissive or restrictive. By default, policies are permissive, which means they are combined using the boolean OR operator. A policy can be defined as restrictive as an alternative. Restrictive policies are combined using the boolean AND operator. Here is the general formula:

row_is_visible = (one or more of the conditions from the permissive policies that apply to the current user and their enabled roles are non-zero) AND
                 (all of the conditions from the restrictive policies that apply to the current user and their enabled roles are non-zero)

If no permissive condition applies, the first condition has no effect and only the restrictive policies decide, because access_control_improvements.users_without_row_policies_can_read_rows is enabled by default. A user to whom no condition applies therefore sees every row, and access_control_improvements.throw_on_unmatched_row_policies, disabled by default, raises an exception instead when the table does have conditions and none of them apply. For example, the following policies:

CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 AS RESTRICTIVE TO peter, antonio

enable the user peter to see rows only if both b=1 AND c=2. Database policies are combined with table policies. For example, the following policies:

CREATE ROW POLICY pol1 ON mydb.* USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 AS RESTRICTIVE TO peter, antonio

enable the user peter to see table1 rows only if both b=1 AND c=2, although any other table in mydb would have only b=1 policy applied for the user.

Tables that read from other tables

A row policy filters rows where the data is actually read. An Alias table returns the rows of its target table as its own, so the row policies of the target apply to reads through the alias as well, combined with the policies of the alias itself using a logical AND. A Merge table applies the policies of the tables it reads from. One exception: when a matched table reads remotely, such as a Distributed table, the remote server processes the query before the policy is applied, because the policy runs above that table’s read rather than at the read. Such a query can fail, when it aggregates without selecting the policy’s columns, or return fewer rows than the policy allows, when the remote server applies an ORDER BY ... LIMIT to rows the policy would have hidden. Define the policy on the underlying local tables of each remote server instead. This does not extend to every table that reads from another table. A Buffer table and a materialized view read through their destination or target table do not inherit that table’s row policies: the policy is written against the target’s schema and, for a view with SQL SECURITY DEFINER, is evaluated for a different user than the one running the read. Define the policy on the table users actually query in those cases.

Distributed and remote-backed tables

A row policy filters rows where the table data is actually read. A table that delegates reading to remote servers, such as a Distributed table or a wrapper over one (for example, a materialized view with a Distributed target), only ships the query text to the remote servers and cannot apply the policy filter to the remote read. To keep the filter from being silently dropped, queries to such a table by users the policy applies to are rejected with an ILLEGAL_PREWHERE error. Instead, define the policy on the underlying local tables on each remote server; it is applied there when the shipped query reads them:

-- Filters reads of local_table on this server, including reads shipped by a Distributed table over it.
CREATE ROW POLICY filter ON mydb.local_table USING a < 1000 TO john;

This works while the query is shipped as text, which is the default. With serialize_query_plan = 1 the initiator ships an already-built read plan instead, and a remote server executing such a plan does not apply its own row policies, so a read of a Distributed table over local_table returns unfiltered rows. Keep serialize_query_plan = 0 for users whose row policies must be enforced. See issue #112891.

ON CLUSTER Clause

Allows creating row policies on a cluster, see Distributed DDL. This is also the convenient way to create the policy on the local tables of every server of the cluster.

Examples

CREATE ROW POLICY filter1 ON mydb.mytable USING a<1000 TO accountant, john@localhost CREATE ROW POLICY filter2 ON mydb.mytable USING a<1000 AND b=5 TO ALL EXCEPT mira CREATE ROW POLICY filter3 ON mydb.mytable USING 1 TO admin CREATE ROW POLICY filter4 ON mydb.* USING 1 TO admin

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Multiple names and tables

USING clause

TO Clause

AS Clause

Tables that read from other tables

Distributed and remote-backed tables

ON CLUSTER Clause

Examples

​Multiple names and tables

​USING clause

​TO Clause

​AS Clause

​Tables that read from other tables

​Distributed and remote-backed tables

​ON CLUSTER Clause

​Examples

Multiple names and tables

USING clause

TO Clause

AS Clause

Tables that read from other tables

Distributed and remote-backed tables

ON CLUSTER Clause

Examples