CREATE POLICY

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)	`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`	✓	✓	—	—	—
`UPDATE`	✓	—	✓	✓	—
`DELETE`	✓	—	—	—	✓
`INSERT ... ON CONFLICT DO UPDATE`	✓	—	✓	✓	—
`UPSERT`	✓	—	✓	✓	—

Additional considerations include:

SELECT evaluation: CockroachDB always evaluates SELECT (USING) policies for INSERT, UPDATE, and DELETE, even when the statement doesn't reference table columns.
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. #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 the VALUES clause if the candidate row has a conflict. #35370.

Pricing

Contact us

Sign In