CREATE ROW POLICY

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

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
    [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
    [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
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]

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

1. Multiple names, one target — pol1, pol2 ON table1 creates each listed name on that single table (or db.*).
2. One name, multiple targets — pol1 ON table1, table2 creates the same short name on each listed target.
3. 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.

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

Allows specifying a condition to filter rows. A user will see a row if the condition is calculated to non-zero for the row.

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

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 permissive policies' conditions are non-zero) AND
                 (all of the restrictive policies's conditions are non-zero)

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.

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;

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