Home • Explore • Blog • CI
☰



postgresql
6th chunk of `doc/src/sgml/ref/create_policy.sgml`
9850e30b3a13bf5fb347f3af6af9a96fc01f1986765963e60000000100000fa0
 <literal>ALL</literal> policy.
       </para>

       <para>
         When an <literal>INSERT</literal> command has an auxiliary
         <literal>ON CONFLICT DO UPDATE</literal> clause, if the
         <literal>UPDATE</literal> path is taken, the row to be updated is
         first checked against the <literal>USING</literal> expressions of
         any <literal>UPDATE</literal> policies, and then the new updated row
         is checked against the <literal>WITH CHECK</literal> expressions.
         Note, however, that unlike a standalone <literal>UPDATE</literal>
         command, if the existing row does not pass the
         <literal>USING</literal> expressions, an error will be thrown (the
         <literal>UPDATE</literal> path will <emphasis>never</emphasis> be silently
         avoided).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="sql-createpolicy-delete">
      <term><literal>DELETE</literal></term>
      <listitem>
       <para>
         Using <literal>DELETE</literal> for a policy means that it will apply
         to <literal>DELETE</literal> commands.  Only rows that pass this
         policy will be seen by a <literal>DELETE</literal> command.  There can
         be rows that are visible through a <literal>SELECT</literal> that are
         not available for deletion, if they do not pass the
         <literal>USING</literal> expression for
         the <literal>DELETE</literal> policy.
       </para>

       <para>
         In most cases a <literal>DELETE</literal> command also needs to read
         data from columns in the relation that it is deleting from (e.g.,
         in a <literal>WHERE</literal> clause or a
         <literal>RETURNING</literal> clause). In this case,
         <literal>SELECT</literal> rights are also required on the relation,
         and the appropriate <literal>SELECT</literal> or
         <literal>ALL</literal> policies will be applied in addition to
         the <literal>DELETE</literal> policies.  Thus the user must have
         access to the row(s) being deleted through a <literal>SELECT</literal>
         or <literal>ALL</literal> policy in addition to being granted
         permission to delete the row(s) via a <literal>DELETE</literal> or
         <literal>ALL</literal> policy.
       </para>

       <para>
         A <literal>DELETE</literal> policy cannot have a <literal>WITH
         CHECK</literal> expression, as it only applies in cases where
         records are being deleted from the relation, so that there is no
         new row to check.
       </para>
      </listitem>
     </varlistentry>

   </variablelist>

   <table id="sql-createpolicy-summary">
    <title>Policies Applied by Command Type</title>
    <tgroup cols="6">
     <colspec colnum="4" colname="update-using"/>
     <colspec colnum="5" colname="update-check"/>
     <spanspec namest="update-using" nameend="update-check" spanname="update"/>
     <thead>
      <row>
       <entry morerows="1">Command</entry>
       <entry><literal>SELECT/ALL policy</literal></entry>
       <entry><literal>INSERT/ALL policy</literal></entry>
       <entry spanname="update"><literal>UPDATE/ALL policy</literal></entry>
       <entry><literal>DELETE/ALL policy</literal></entry>
      </row>
      <row>
       <entry><literal>USING expression</literal></entry>
       <entry><literal>WITH CHECK expression</literal></entry>
       <entry><literal>USING expression</literal></entry>
       <entry><literal>WITH CHECK expression</literal></entry>
       <entry><literal>USING expression</literal></entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry><command>SELECT</command></entry>
       <entry>Existing row</entry>
       <entry>&mdash;</entry>
       <entry>&mdash;</entry>
       <entry>&mdash;</entry>
       <entry>&mdash;</entry>
      </row>
      <row>
       <entry><command>SELECT FOR UPDATE/SHARE</command></entry>
       <entry>Existing row</entry>
       <entry>&mdash;</entry>
Title: CREATE POLICY - DELETE Policies and Policy Summary Table
Summary
This section describes `DELETE` policies, which control which rows a `DELETE` command can see. `DELETE` also requires `SELECT` rights for operations like `WHERE` or `RETURNING` clauses. `DELETE` policies cannot have a `WITH CHECK` expression. The section concludes with a table summarizing which policies are applied based on the command type, including `SELECT`, `INSERT`, `UPDATE`, and `DELETE`, and specifies which expressions (`USING` and `WITH CHECK`) are relevant for each.