On this page 
The CREATE POLICY statement defines a new row-level security (RLS) policy on a table.
Syntax
CREATE POLICY [ IF NOT EXISTS ] policy_name ON table_name
[ AS ( PERMISSIVE | RESTRICTIVE ) ]
[ FOR ( ALL | SELECT | INSERT | UPDATE | DELETE ) ]
[ TO ( role_name | PUBLIC | CURRENT_USER | SESSION_USER ) [, ...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ];
Parameters
| Parameter | Description |
|---|---|
IF NOT EXISTS |
Used to specify that the policy will only be created if one with the same policy_name does not already exist on table_name. If a policy with that name does already exist, the statement will not return an error if this parameter is used. |
policy_name |
Unique identifier for the policy on the table. |
table_name |
The table to which the policy applies. |
AS ( PERMISSIVE, RESTRICTIVE ) |
(Default: PERMISSIVE.) For PERMISSIVE, combine policies using OR: a row is accessible if any permissive policy grants access. For RESTRICTIVE, combine policies using AND: a row is accessible if all restrictive policies grant access. The overall policy enforcement is determined logically as: {permissive policies} AND {restrictive policies}: restrictive policies are evaluated after permissive policies. This means that at least one PERMISSIVE policy must be in place before RESTRICTIVE policies are applied. If any restrictive policy denies access, the row is inaccessible, regardless of the permissive policies. |
FOR ( ALL, SELECT, INSERT, UPDATE, DELETE ) |
(Default: ALL.) Specifies the SQL statement(s) the policy applies to: (SELECT, INSERT, UPDATE, DELETE). For details, refer to Policies by statement type. |
TO role_name, ... |
(Default: PUBLIC, which means the policy applies to all roles.) Specifies the database role(s) to which the policy applies. |
USING ( using_expression ) |
Defines the filter condition such that only rows for which the using_expression evaluates to TRUE are visible or available for modification. Rows evaluating to FALSE or NULL are silently excluded. Note this the expression is evaluated before any data modifications are attempted. The filter condition applies to SELECT, UPDATE, DELETE, and INSERT (for INSERT ... ON CONFLICT DO UPDATE). |
WITH CHECK ( check_expression ) |
Defines a constraint condition such that rows being inserted or updated must satisfy check_expression (i.e., must evaluate to TRUE). This expression is evaluated after the row data is prepared but before it is written. If the expression evaluates to FALSE or NULL, the operation fails with an RLS policy violation error. Applies to INSERT and UPDATE. If this expression is omitted, it will default to the USING expression for new rows in an UPDATE or INSERT. |
Note:
The USING and WITH CHECK expressions can reference table columns and use session-specific functions (e.g., current_user(), session_user()) and variables. However, these expressions cannot contain a subexpression.
Policies by statement type
The following table shows which policies are applied to which statement types, with additional considerations listed after the table.
| Command / clause pattern | SELECT policy - USING (row that already exists) |
SELECT policy - USING (row being added) |
INSERT policy - WITH CHECK (row being added) |
UPDATE policy - USING (row before the change) |
UPDATE policy - WITH CHECK (row after the change) |
DELETE policy - USING (row to be removed) |
|---|---|---|---|---|---|---|
SELECT |
✓ | — | — | — | — | — |
SELECT ... FOR UPDATE / FOR SHARE |
✓ | — | — | ✓ | — | — |
INSERT |
— | — | ✓ | — | — | — |
INSERT ... RETURNING |
— | ✓(b) | ✓ | — | — | — |
UPDATE |
✓ | ✓(b) | — | ✓ | ✓ | — |
DELETE |
✓ | — | — | — | — | ✓ |
INSERT ... ON CONFLICT DO UPDATE |
✓(a) | ✓(a) | — | ✓ | ✓ | — |
UPSERT |
✓(a) | ✓(a) | — | ✓ | ✓ | — |
- ✓: Always applied.
- (a): A
USINGpolicy failure causes the statement to fail. Normally,USINGfilters out rows silently. - (b): Like ✓(a), but only applied when the statement references row columns (
WHERE,SET, orRETURNING). If theUSINGpolicy is violated, the statement fails.
Additional considerations include:
ON CONFLICT ... DO NOTHING: CockroachDB does not run the constraint and row-level policy checks on theVALUESclause if the candidate row has a conflict. #35370. This is a known limitation.
Examples
In this example, you will allow users to see or modify only their own rows in an orders table.
CREATE TABLE orders (user_id TEXT PRIMARY KEY, order_details TEXT);
-- Assume 'orders' table has a 'user_id' column matching logged-in user names.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_orders_policy ON orders
FOR ALL
TO PUBLIC -- Applies to all roles
USING ( user_id = CURRENT_USER )
WITH CHECK ( user_id = CURRENT_USER );
Known limitations
ON CONFLICT ... DO NOTHING: CockroachDB does not run the constraint and row-level policy checks on theVALUESclause if the candidate row has a conflict. #35370.
