Home • Explore • Blog • CI
☰



postgresql
3rd chunk of `doc/src/sgml/ref/create_policy.sgml`
ee525069e35adbd0f37e5ab4fb1a7bf893d69171fef56b0b0000000100000fa3
 in addition to all the restrictive
      policies.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">command</replaceable></term>
    <listitem>
     <para>
      The command to which the policy applies.  Valid options are
      <command>ALL</command>, <command>SELECT</command>,
      <command>INSERT</command>, <command>UPDATE</command>,
      and <command>DELETE</command>.
      <command>ALL</command> is the default.
      See below for specifics regarding how these are applied.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">role_name</replaceable></term>
    <listitem>
     <para>
      The role(s) to which the policy is to be applied.  The default is
      <literal>PUBLIC</literal>, which will apply the policy to all roles.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">using_expression</replaceable></term>
    <listitem>
     <para>
      Any <acronym>SQL</acronym> conditional expression (returning
      <type>boolean</type>).  The conditional expression cannot contain
      any aggregate or window functions.  This expression will be added
      to queries that refer to the table if row-level security is enabled.
      Rows for which the expression returns true will be visible.  Any
      rows for which the expression returns false or null will not be
      visible to the user (in a <command>SELECT</command>), and will not be
      available for modification (in an <command>UPDATE</command>
      or <command>DELETE</command>).  Such rows are silently suppressed; no error
      is reported.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">check_expression</replaceable></term>
    <listitem>
     <para>
      Any <acronym>SQL</acronym> conditional expression (returning
      <type>boolean</type>).  The conditional expression cannot contain
      any aggregate or window functions.  This expression will be used in
      <command>INSERT</command> and <command>UPDATE</command> queries against
      the table if row-level security is enabled.  Only rows for which the
      expression evaluates to true will be allowed.  An error will be thrown
      if the expression evaluates to false or null for any of the records
      inserted or any of the records that result from the update.  Note that
      the <replaceable class="parameter">check_expression</replaceable> is
      evaluated against the proposed new contents of the row, not the
      original contents.
     </para>
    </listitem>
   </varlistentry>

  </variablelist>

  <refsect2>
   <title>Per-Command Policies</title>

   <variablelist>

     <varlistentry id="sql-createpolicy-all">
      <term><literal>ALL</literal></term>
      <listitem>
       <para>
         Using <literal>ALL</literal> for a policy means that it will apply
         to all commands, regardless of the type of command.  If an
         <literal>ALL</literal> policy exists and more specific policies
         exist, then both the <literal>ALL</literal> policy and the more
         specific policy (or policies) will be applied.
         Additionally, <literal>ALL</literal> policies will be applied to
         both the selection side of a query and the modification side, using
         the <literal>USING</literal> expression for both cases if only
         a <literal>USING</literal> expression has been defined.
       </para>
       <para>
         As an example, if an <literal>UPDATE</literal> is issued, then the
         <literal>ALL</literal> policy will be applicable both to what the
         <literal>UPDATE</literal> will be able to select as rows to be
         updated (applying the <literal>USING</literal> expression),
         and to the resulting updated rows, to check if they are permitted
         to be added to the table (applying the <literal>WITH CHECK</literal>
Title: CREATE POLICY - Command, Role, and Expression Parameters
Summary
This section details the parameters for the `CREATE POLICY` command, including specifying the command to which the policy applies (`ALL`, `SELECT`, `INSERT`, `UPDATE`, `DELETE`), the role(s) the policy applies to (defaulting to `PUBLIC`), a `USING` expression to control row visibility and modification availability based on a boolean SQL expression, and a `CHECK` expression to validate new or updated rows against a boolean SQL expression. It also explains the behavior of the `ALL` command, which applies to all command types and uses the `USING` expression for both selection and modification aspects of a query if only a `USING` expression is defined.