CREATE POLICY

​

Syntax

create_policy ::= CREATE POLICY policy_name ON table_name
                 [ AS { PERMISSIVE | RESTRICTIVE } ]
                 [ FOR command ]
                 [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
                 [ USING ( using_expression ) ]
                 [ WITH CHECK ( check_expression ) ]

alter_rls ::= ALTER TABLE table_name ENABLE ROW LEVEL SECURITY
            | ALTER TABLE table_name DISABLE ROW LEVEL SECURITY
            | ALTER TABLE table_name FORCE ROW LEVEL SECURITY
            | ALTER TABLE table_name NO FORCE ROW LEVEL SECURITY

policy_name ::= identifier

table_name ::= [schema.]table

command ::= ALL | SELECT | INSERT | UPDATE | DELETE

using_expression ::= sql_expression

check_expression ::= sql_expression

• Policy names: Unique identifiers for Row Level Security policies
• Table references: Schema-qualified table names where policies apply
• Policy type:
  • PERMISSIVE (default) - allows access when policy evaluates to true
  • RESTRICTIVE - denies access unless policy evaluates to true
• Commands:
  • ALL - applies to all commands
  • SELECT - applies to SELECT queries
  • INSERT - applies to INSERT commands
  • UPDATE - applies to UPDATE commands
  • DELETE - applies to DELETE commands
• Roles: Specific roles, PUBLIC, CURRENT_USER, or SESSION_USER that the policy applies to
• USING clause: Boolean expression checked for existing rows (SELECT, UPDATE, DELETE)
• WITH CHECK clause: Boolean expression checked for new rows (INSERT, UPDATE)

​

Canonical Format

CREATE POLICY policy_name ON [schema.]table_name
[ FOR command ]
[ TO role_name[, ...] ]
[ USING expression ]
[ WITH CHECK expression ];

• Schema qualification is omitted for tables in the target schema
• The AS PERMISSIVE/RESTRICTIVE clause is omitted when PERMISSIVE (the default)
• FOR ALL is omitted as it’s the default command
• Roles are listed in a consistent order
• USING and WITH CHECK expressions are preserved exactly as defined
• For ALTER operations that only change roles, USING, or WITH CHECK:
  ALTER POLICY policy_name ON [schema.]table_name
  [ TO role_name[, ...] ]
  [ USING expression ]
  [ WITH CHECK expression ];
  
• For changes requiring recreation (command or permissive/restrictive changes):
  DROP POLICY IF EXISTS policy_name ON [schema.]table_name;
  CREATE POLICY policy_name ON [schema.]table_name ...;
  

​

Row Level Security

-- Enable RLS (policies apply to non-owner users)
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

-- Force RLS (policies apply even to table owners)
ALTER TABLE table_name FORCE ROW LEVEL SECURITY;

• ALTER TABLE ... ENABLE ROW LEVEL SECURITY - enables RLS for the table
• ALTER TABLE ... DISABLE ROW LEVEL SECURITY - disables RLS
• ALTER TABLE ... FORCE ROW LEVEL SECURITY - forces RLS to apply even to table owners
• ALTER TABLE ... NO FORCE ROW LEVEL SECURITY - removes the force option