Home Explore Blog CI



postgresql

33th chunk of `doc/src/sgml/ddl.sgml`
16a315a2072f3d1274e541ab57b4bfd83300a67dfc5908880000000100000fa8
 <literal>true</literal> will not be processed.  Separate expressions
   may be specified to provide independent control over the rows which are
   visible and the rows which are allowed to be modified.  Policy
   expressions are run as part of the query and with the privileges of the
   user running the query, although security-definer functions can be used
   to access data not available to the calling user.
  </para>

  <para>
   Superusers and roles with the <literal>BYPASSRLS</literal> attribute always
   bypass the row security system when accessing a table.  Table owners
   normally bypass row security as well, though a table owner can choose to
   be subject to row security with <link linkend="sql-altertable">ALTER
   TABLE ... FORCE ROW LEVEL SECURITY</link>.
  </para>

  <para>
   Enabling and disabling row security, as well as adding policies to a
   table, is always the privilege of the table owner only.
  </para>

  <para>
   Policies are created using the <xref linkend="sql-createpolicy"/>
   command, altered using the <xref linkend="sql-alterpolicy"/> command,
   and dropped using the <xref linkend="sql-droppolicy"/> command.  To
   enable and disable row security for a given table, use the
   <xref linkend="sql-altertable"/> command.
  </para>

  <para>
   Each policy has a name and multiple policies can be defined for a
   table.  As policies are table-specific, each policy for a table must
   have a unique name.  Different tables may have policies with the
   same name.
  </para>

  <para>
   When multiple policies apply to a given query, they are combined using
   either <literal>OR</literal> (for permissive policies, which are the
   default) or using <literal>AND</literal> (for restrictive policies).
   The <literal>OR</literal> behavior is similar to the rule that a given
   role has the privileges
   of all roles that they are a member of.  Permissive vs. restrictive
   policies are discussed further below.
  </para>

  <para>
   As a simple example, here is how to create a policy on
   the <literal>account</literal> relation to allow only members of
   the <literal>managers</literal> role to access rows, and only rows of their
   accounts:
  </para>

<programlisting>
CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);
</programlisting>

  <para>
   The policy above implicitly provides a <literal>WITH CHECK</literal>
   clause identical to its <literal>USING</literal> clause, so that the
   constraint applies both to rows selected by a command (so a manager
   cannot <command>SELECT</command>, <command>UPDATE</command>,
   or <command>DELETE</command> existing rows belonging to a different
   manager) and to rows modified by a command (so rows belonging to a
   different manager cannot be created via <command>INSERT</command>
   or <command>UPDATE</command>).
  </para>

  <para>
   If no role is specified, or the special user name
   <literal>PUBLIC</literal> is used, then the policy applies to all
   users on the system.  To allow all users to access only their own row in
   a <literal>users</literal> table, a simple policy can be used:
  </para>

<programlisting>
CREATE POLICY user_policy ON users
    USING (user_name = current_user);
</programlisting>

  <para>
   This works similarly to the previous example.
  </para>

  <para>
   To use a different policy for rows that are being added to the table
   compared to those rows that are visible, multiple policies can be
   combined.  This pair of policies would allow all users to view all rows
   in the <literal>users</literal> table, but only modify their own:
  </para>

<programlisting>
CREATE POLICY user_sel_policy ON users
    FOR SELECT
    USING (true);
CREATE POLICY user_mod_policy ON users
    USING (user_name = current_user);
</programlisting>

  <para>
   In a <command>SELECT</command> command,

Title: Row Security Policies: Implementation, Management, and Examples
Summary
This section details the implementation and management of row security policies, including how to create, alter, and drop policies using SQL commands. It covers how policies are combined using OR (permissive) or AND (restrictive) logic when multiple policies apply, and provides examples of creating policies that restrict access to rows based on user roles. It demonstrates creating policies for managers accessing their accounts, users accessing their own rows, and combining policies for different access control scenarios like allowing users to view all rows but only modify their own.