Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/ref/create_policy.sgml`
450494d092c35ae06f06e01f4cbc43f66d9aed3a61aa16e50000000100000fa0
 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> commands.
         <literal>MERGE</literal> commands containing <literal>UPDATE</literal>
         actions are affected as well.  Since <literal>UPDATE</literal>
         involves pulling an existing record and replacing it with a new
         modified record, <literal>UPDATE</literal>
         policies accept both a <literal>USING</literal> expression and
         a <literal>WITH CHECK</literal> expression.
         The <literal>USING</literal> expression determines which records
         the <literal>UPDATE</literal> command will see to operate against,
         while the <literal>WITH CHECK</literal> expression defines which
         modified rows are allowed to be stored back into the relation.
       </para>

       <para>
         Any rows whose updated values do not pass the
         <literal>WITH CHECK</literal> expression will cause an error, and the
         entire command will be aborted.  If only a <literal>USING</literal>
         clause is specified, then that clause will be used for both
         <literal>USING</literal> and <literal>WITH CHECK</literal> cases.
       </para>

       <para>
         Typically an <literal>UPDATE</literal> command also needs to read
         data from columns in the relation being updated (e.g., in a
         <literal>WHERE</literal> clause or a <literal>RETURNING</literal>
         clause, or in an expression on the right hand side of the
         <literal>SET</literal> clause).  In this case,
         <literal>SELECT</literal> rights are also required on the relation
         being updated, and the appropriate <literal>SELECT</literal> or
         <literal>ALL</literal> policies will be applied in addition to
         the <literal>UPDATE</literal> policies.  Thus the user must have
         access to the row(s) being updated through a <literal>SELECT</literal>
         or <literal>ALL</literal> policy in addition to being granted
         permission to update the row(s) via an <literal>UPDATE</literal>
         or <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
Title: CREATE POLICY - UPDATE and DELETE Policies
Summary
This section describes `UPDATE` policies, which apply to `UPDATE`, `SELECT FOR UPDATE`, and `SELECT FOR SHARE` commands, as well as `MERGE` commands containing `UPDATE` actions. They use `USING` to determine visible records and `WITH CHECK` to validate modified rows. `UPDATE` requires both `SELECT` and `UPDATE` rights, applying corresponding policies. It also describes `DELETE` policies. When `INSERT` has `ON CONFLICT DO UPDATE`, existing rows are checked against `USING`, and new rows against `WITH CHECK`.