On this page 
The CREATE POLICY statement defines a new row-level security (RLS) policy on a table.
Syntax
CREATE POLICY 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 |
|---|---|
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, policies are combined using OR. A row is accessible if any permissive policy grants access. For RESTRICTIVE, policies are combined using AND. The overall policy enforcement is determined by evaluating a logical expression of the form: (permissive policies) AND (restrictive policies). This means that all restrictive policies must grant access for a row to be accessible, and restrictive policies are evaluated after permissive policies. 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). |
TO { role_name, ...} |
(Default: PUBLIC.) 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, UPDATE. |
Note:
THe USING and WITH CHECK expressions can reference table columns and use session-specific functions (e.g., current_user(), session_user()) and variables.
Examples
In this example, we only allow users to see or modify their own rows in an orders table.
-- Minimal schema for the 'orders' table example.
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 );
See also
- Row-level security (RLS) overview
ALTER POLICYDROP POLICYSHOW POLICIESALTER TABLE {ENABLE, DISABLE} ROW LEVEL SECURITYALTER TABLE {FORCE, UNFORCE} ROW LEVEL SECURITYALTER ROLE ... WITH BYPASSRLSCREATE ROLE ... WITH BYPASSRLS
Was this helpful?
