Home • Explore • Blog • CI
☰



postgresql
4th chunk of `doc/src/sgml/ref/create_policy.sgml`
b863cbaf6940ffcef4e4515328ece02cdcf4f5fdc3d83ba80000000100000fa1
 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>
         expression, if defined, and the <literal>USING</literal> expression
         otherwise).  If an <command>INSERT</command>
         or <command>UPDATE</command> command attempts to add rows to the
         table that do not pass the <literal>ALL</literal>
         policy's <literal>WITH CHECK</literal> expression, the entire
         command will be aborted.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="sql-createpolicy-select">
      <term><literal>SELECT</literal></term>
      <listitem>
       <para>
         Using <literal>SELECT</literal> for a policy means that it will apply
         to <literal>SELECT</literal> queries and whenever
         <literal>SELECT</literal> permissions are required on the relation the
         policy is defined for.  The result is that only those records from the
         relation that pass the <literal>SELECT</literal> policy will be
         returned during a <literal>SELECT</literal> query, and that queries
         that require <literal>SELECT</literal> permissions, such as
         <literal>UPDATE</literal>, will also only see those records
         that are allowed by the <literal>SELECT</literal> policy.
         A <literal>SELECT</literal> policy cannot have a <literal>WITH
         CHECK</literal> expression, as it only applies in cases where
         records are being retrieved from the relation.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="sql-createpolicy-insert">
      <term><literal>INSERT</literal></term>
      <listitem>
       <para>
         Using <literal>INSERT</literal> for a policy means that it will apply
         to <literal>INSERT</literal> commands and <literal>MERGE</literal>
         commands that contain <literal>INSERT</literal> actions.
         Rows being inserted that do
         not pass this policy will result in a policy violation error, and the
         entire <literal>INSERT</literal> command will be aborted.
         An <literal>INSERT</literal> policy cannot have
         a <literal>USING</literal> expression, as it only applies in cases
         where records are being added to the relation.
       </para>
       <para>
         Note that <literal>INSERT</literal> with <literal>ON CONFLICT DO
         UPDATE</literal> checks <literal>INSERT</literal> policies'
         <literal>WITH CHECK</literal> expressions only for rows appended
         to the relation by the <literal>INSERT</literal> path.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="sql-createpolicy-update">
      <term><literal>UPDATE</literal></term>
      <listitem>
       <para>
         Using <literal>UPDATE</literal> for a policy means that it will apply
         to <literal>UPDATE</literal>, <literal>SELECT FOR UPDATE</literal>
         and <literal>SELECT FOR SHARE</literal> commands, as well as
         auxiliary <literal>ON CONFLICT DO UPDATE</literal> clauses of
         <literal>INSERT</literal>
Title: CREATE POLICY - Per-Command Policies (ALL, SELECT, INSERT, UPDATE)
Summary
This section explains the application of policies based on specific commands: `ALL` policies apply universally to both selection and modification, with `USING` for both unless `WITH CHECK` is defined. `SELECT` policies filter rows retrieved during `SELECT` queries and operations requiring `SELECT` permissions. `INSERT` policies enforce conditions on inserted rows, aborting the command if violated. `UPDATE` policies apply to `UPDATE`, `SELECT FOR UPDATE`, and `SELECT FOR SHARE` commands, as well as auxiliary `ON CONFLICT DO UPDATE` clauses of `INSERT`.