CREATE POLICY

The CREATE POLICY statement defines a new policy on a .

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 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: (, , , ). For details, refer to Policies by statement type.
TO role\_name, ...	(Default: PUBLIC, which means the policy applies to all roles.) Specifies the database 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 , , , and (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 and . If this expression is omitted, it will default to the USING expression for new rows in an UPDATE or INSERT.

The USING and WITH CHECK expressions can reference table columns and use session-specific (e.g., current_user(), session_user()) and . 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 USING policy failure causes the statement to fail. Normally, USING filters out rows silently.
• (b): Like ✓(a), but only applied when the statement references row columns ( WHERE, SET, or RETURNING ). If the USING policy is violated, the statement fails.

Additional considerations include:

• ON CONFLICT... DO NOTHING: CockroachDB does not run the constraint and row-level policy checks on the VALUES clause if the candidate row has a conflict.. 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 the VALUES clause if the candidate row has a conflict..

See also