CREATE POLICY

Synopsis

Use the CREATE POLICY statement to create a new 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 thepolicies to take effect.

Syntax

  1. create_policy ::= CREATE POLICY name ON table_name 
  2.                   [ AS { PERMISSIVE | RESTRICTIVE } ] 
  3.                   [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ] 
  4.                   [ TO { role_name
  5.                          | PUBLIC
  6.                          | CURRENT_USER
  7.                          | SESSION_USER } [ , ... ] ] 
  8.                   [ USING ( using_expression ) ] 
  9.                   [ WITH CHECK ( check_expression ) ]

create_policy

CREATE POLICY - 图1

Where

  • name is the name of the new policy. This must be distinct from any other policy name for thattable.
  • 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 toreduce 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 thepolicy to all roles.
  • using_expression is a SQL conditional expression. Only rows for which the condition returns totrue 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 UPDATEqueries. Only rows for which the expression evaluates to true will be allowed in an INSERT orUPDATE. Note that unlike using_expression, this is evaluated against the proposed new contentsof the row.

Examples

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

See also