Row-policy statements
Create, replace, and drop row policies for database roles.
Row-policy statements
This page describes statements accepted by the Query API in RawTree Cloud and RawTree BYOC. Its restrictions apply to the API, not to all RawTree SQL. See ClickHouse compatibility and API scope.
Syntax
CREATE ROW POLICY [IF NOT EXISTS | OR REPLACE] <name>
[ON CLUSTER 'default'] ON [<database>.]<table>
[AS PERMISSIVE | AS RESTRICTIVE] [FOR SELECT]
USING <expression> TO <existing role>, ...
DROP ROW POLICY [IF EXISTS] <name>
[ON CLUSTER 'default'] ON [<database>.]<table>Clauses
| Clause | Meaning |
|---|---|
OR REPLACE | Replace the named policy. |
AS PERMISSIVE | Combine matching policies with OR. |
AS RESTRICTIVE | Combine matching policies with AND. |
FOR SELECT | Apply the policy to reads. |
USING | The row predicate. |
Query API behavior
- Supported: One policy on one table per request, assigned to existing
roles; omit
ON CLUSTERfor replicated access storage. - Not exposed by the Query API:
ALTER ROW POLICY, subqueries, user targets,USING NONE, and statements for other clusters. - Query API scope: The selected database is mandatory for an
unqualified table, and
ON CLUSTERrequires a qualified table.
Errors
Error: Unsupported row policy statement.
Hint:
Use CREATE ROW POLICY [IF NOT EXISTS | OR REPLACE] <name> [ON CLUSTER 'default'] ON [database.]<table> [AS PERMISSIVE | AS RESTRICTIVE] [FOR SELECT] USING <expression> TO <existing roles>, or DROP ROW POLICY [IF EXISTS] <name> [ON CLUSTER 'default'] ON [database.]<table>. Select the target database with the database parameter; ON CLUSTER requires database.table. Submit one policy on one table; subqueries and user targets are not supported.Examples
Create a role for the policy.
CREATE ROLE acme_statement_readerGrant access to the event table.
GRANT SELECT ON analytics.events TO acme_statement_readerCreate a permissive policy.
CREATE ROW POLICY acme_statement_only ON events FOR SELECT USING __raw_data.tenant::String = 'acme' TO acme_statement_readerReplace the policy with a narrower condition.
CREATE ROW POLICY OR REPLACE acme_statement_only ON events FOR SELECT USING __raw_data.tenant::String = 'acme' AND __raw_data.environment::String = 'prod' TO acme_statement_readerDrop the policy.
DROP ROW POLICY IF EXISTS acme_statement_only ON eventsDrop the role after the policy.
DROP ROLE IF EXISTS acme_statement_readerThe parser rejects ALTER ROW POLICY.
ALTER ROW POLICY acme_statement_only ON events USING 1The parser rejects subqueries.
CREATE ROW POLICY tenant_filter ON events USING (SELECT 1) TO acme_statement_readerThe parser rejects user targets.
CREATE ROW POLICY tenant_filter ON events USING 1 TO USER analytics_userThe parser rejects USING NONE.
CREATE ROW POLICY tenant_filter ON events USING NONE TO acme_statement_readerThe parser rejects other clusters.
CREATE ROW POLICY tenant_filter ON CLUSTER 'other' ON analytics.events USING 1 TO acme_statement_reader