CREATE POLICY

Synopsis

Use the CREATE POLICY statement to create a row level security policy for a table. A policy grants the permission to select, insert, update, or delete rows that match the relevant policy expression. Row level security must be enabled on the table using ALTER TABLE for the policies to take effect.

Syntax

create_policy ::= CREATE 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 ) ]

create_policy

CREATEPOLICYnameONtable_nameASPERMISSIVERESTRICTIVEFORALLSELECTINSERTUPDATEDELETETO,role_namePUBLICCURRENT_USERSESSION_USERUSING(using_expression)WITHCHECK(check_expression)

Where

  • name is the name of the new policy. This must be distinct from any other policy name for that table.
  • table_name is the name of the table that the policy applies to.
  • PERMISSIVE / RESTRICTIVE specifies that the policy is permissive or restrictive. While applying policies to a table, permissive policies are combined together using a logical OR operator, while restrictive policies are combined using logical AND operator. Restrictive policies are used to reduce the number of records that can be accessed. Default is permissive.
  • role_name is the role(s) to which the policy is applied. Default is PUBLIC which applies the policy to all roles.
  • using_expression is a SQL conditional expression. Only rows for which the condition returns to true will be visible in a SELECT and available for modification in an UPDATE or DELETE.
  • check_expression is a SQL conditional expression that is used only for INSERT and UPDATE queries. Only rows for which the expression evaluates to true will be allowed in an INSERT or UPDATE. Note that unlike using_expression, this is evaluated against the proposed new contents of the row.

Examples

  • Create a permissive policy.
yugabyte=# CREATE POLICY p1 ON document
  USING (dlevel <= (SELECT level FROM user_account WHERE ybuser = current_user));
  • Create a restricive policy.
yugabyte=# CREATE POLICY p_restrictive ON document AS RESTRICTIVE TO user_bob
    USING (cid <> 44);
  • Create a policy with a CHECK condition for inserts.
yugabyte=# CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);

See also