CREATE ROW POLICY 

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

Syntax:

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

USING Clause 

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

TO Clause 

In the section TO you can provide a list of users and roles this policy should work for. For example, CREATE ROW POLICY ... TO accountant, [email protected].

Keyword ALL means all the ClickHouse users including current user. Keyword ALL EXCEPT allow to exclude some users from the all users list, for example, CREATE ROW POLICY ... TO ALL EXCEPT accountant, [email protected]

AS Clause 

It's allowed to have more than one policy enabled on the same table for the same user at the 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

enables 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

enables the user peter to see rows only if both b=1 AND c=2.

ON CLUSTER Clause 

Allows creating row policies on a cluster, see Distributed DDL.

Examples 

CREATE ROW POLICY filter1 ON mydb.mytable USING a<1000 TO accountant, [email protected]

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

Rating: 2.8 - 6 votes

Was this content helpful?
★★★☆☆