Home • Explore • Blog • CI
☰



postgresql
7th chunk of `doc/src/sgml/ref/create_view.sgml`
240cb9fe9ac60868a491d03bad3f8cbf51cc816456f003900000000100000b00

    If an automatically updatable view is marked with the
    <literal>security_barrier</literal> property then all the view's <literal>WHERE</literal>
    conditions (and any conditions using operators which are marked as <literal>LEAKPROOF</literal>)
    will always be evaluated before any conditions that a user of the view has
    added.   See <xref linkend="rules-privileges"/> for full details.  Note that,
    due to this, rows which are not ultimately returned (because they do not
    pass the user's <literal>WHERE</literal> conditions) may still end up being locked.
    <command>EXPLAIN</command> can be used to see which conditions are
    applied at the relation level (and therefore do not lock rows) and which are
    not.
   </para>

   <para>
    A more complex view that does not satisfy all these conditions is
    read-only by default: the system will not allow an <command>INSERT</command>,
    <command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>
    on the view.  You can get the effect of an updatable view by
    creating <literal>INSTEAD OF</literal> triggers on the view, which must
    convert attempted inserts, etc. on the view into appropriate actions
    on other tables.  For more information see <xref
    linkend="sql-createtrigger"/>.  Another possibility is to create rules
    (see <xref linkend="sql-createrule"/>), but in practice triggers are
    easier to understand and use correctly.  Also note that <command>MERGE</command>
    is not supported on relations with rules.
   </para>

   <para>
    Note that the user performing the insert, update or delete on the view
    must have the corresponding insert, update or delete privilege on the
    view.  In addition, by default, the view's owner must have the relevant
    privileges on the underlying base relations, whereas the user performing
    the update does not need any permissions on the underlying base relations
    (see <xref linkend="rules-privileges"/>).  However, if the view has
    <literal>security_invoker</literal> set to <literal>true</literal>, the
    user performing the update, rather than the view owner, must have the
    relevant privileges on the underlying base relations.
   </para>
  </refsect2>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   Create a view consisting of all comedy films:

<programlisting>
CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';
</programlisting>
   This will create a view containing the columns that are in the
   <literal>film</literal> table at the time of view creation.  Though
   <literal>*</literal> was used to create the view, columns added later to
   the table will not be part of the view.
  </para>

  <para>
   Create a view with <literal>LOCAL CHECK OPTION</literal>:
Title: Security Barriers, Read-Only Views, INSTEAD OF Triggers, and User Privileges
Summary
This section describes how the 'security_barrier' property on updatable views ensures that the view's WHERE conditions and LEAKPROOF operators are evaluated before user-added conditions, potentially locking rows even if they're ultimately filtered out. It explains that complex views not meeting updatable criteria are read-only by default and how to achieve updatability using INSTEAD OF triggers (preferred over rules). It also details that users need appropriate privileges on the view for insert, update, or delete operations. It mentions that the view owner usually needs privileges on the underlying base relations, unless the security_invoker option is used.