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:

CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name1 [ON CLUSTER cluster_name1] ON [db1.]table1|db1.*
        [, policy_name2 [ON CLUSTER cluster_name2] ON [db2.]table2|db2.* ...]
    [IN access_storage_type]
    [FOR SELECT] USING condition
    [AS {PERMISSIVE | RESTRICTIVE}]
    [TO {role1 [, role2 ...] | ALL | ALL EXCEPT role1 [, role2 ...]}]

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;

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

USING Clause

TO Clause

AS Clause

Distributed and remote-backed tables

ON CLUSTER Clause

Examples

​USING Clause

​TO Clause

​AS Clause

​Distributed and remote-backed tables

​ON CLUSTER Clause

​Examples

USING Clause

TO Clause

AS Clause

Distributed and remote-backed tables

ON CLUSTER Clause

Examples