Home Explore Blog CI



postgresql

31th chunk of `doc/src/sgml/ddl.sgml`
df2745fa672e02ec90eab79985d0067aaca1745ee9ee48e50000000100000fa5
 by one-letter abbreviations from
   <xref linkend="privilege-abbrevs-table"/>, with <literal>*</literal>
   appended if the privilege was granted with grant option.  For example,
   <literal>calvin=r*w/hobbes</literal> specifies that the role
   <literal>calvin</literal> has the privilege
   <literal>SELECT</literal> (<literal>r</literal>) with grant option
   (<literal>*</literal>) as well as the non-grantable
   privilege <literal>UPDATE</literal> (<literal>w</literal>), both granted
   by the role <literal>hobbes</literal>.  If <literal>calvin</literal>
   also has some privileges on the same object granted by a different
   grantor, those would appear as a separate <type>aclitem</type> entry.
   An empty grantee field in an <type>aclitem</type> stands
   for <literal>PUBLIC</literal>.
  </para>

  <para>
   As an example, suppose that user <literal>miriam</literal> creates
   table <literal>mytable</literal> and does:
<programlisting>
GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
</programlisting>
   Then <application>psql</application>'s <literal>\dp</literal> command
   would show:
<programlisting>
=&gt; \dp mytable
                                  Access privileges
 Schema |  Name   | Type  |   Access privileges    |   Column privileges   | Policies
--------+---------+-------+------------------------+-----------------------+----------
 public | mytable | table | miriam=arwdDxtm/miriam+| col1:                +|
        |         |       | =r/miriam             +|   miriam_rw=rw/miriam |
        |         |       | admin=arw/miriam       |                       |
(1 row)
</programlisting>
  </para>

  <para>
   If the <quote>Access privileges</quote> column is empty for a given
   object, it means the object has default privileges (that is, its
   privileges entry in the relevant system catalog is null).  Default
   privileges always include all privileges for the owner, and can include
   some privileges for <literal>PUBLIC</literal> depending on the object
   type, as explained above.  The first <command>GRANT</command>
   or <command>REVOKE</command> on an object will instantiate the default
   privileges (producing, for
   example, <literal>miriam=arwdDxt/miriam</literal>) and then modify them
   per the specified request.  Similarly, entries are shown in <quote>Column
   privileges</quote> only for columns with nondefault privileges.
   (Note: for this purpose, <quote>default privileges</quote> always means
   the built-in default privileges for the object's type.  An object whose
   privileges have been affected by an <command>ALTER DEFAULT
   PRIVILEGES</command> command will always be shown with an explicit
   privilege entry that includes the effects of
   the <command>ALTER</command>.)
  </para>

  <para>
   Notice that the owner's implicit grant options are not marked in the
   access privileges display.  A <literal>*</literal> will appear only when
   grant options have been explicitly granted to someone.
  </para>

  <para>
   The <quote>Access privileges</quote> column
   shows <literal>(none)</literal> when the object's privileges entry is
   non-null but empty.  This means that no privileges are granted at all,
   even to the object's owner &mdash; a rare situation.  (The owner still
   has implicit grant options in this case, and so could re-grant her own
   privileges; but she has none at the moment.)
  </para>
 </sect1>

 <sect1 id="ddl-rowsecurity">
  <title>Row Security Policies</title>

  <indexterm zone="ddl-rowsecurity">
   <primary>row-level security</primary>
  </indexterm>

  <indexterm zone="ddl-rowsecurity">
   <primary>policy</primary>
  </indexterm>

  <para>
   In addition to the SQL-standard <link linkend="ddl-priv">privilege
   system</link> available through <xref linkend="sql-grant"/>,
   tables can have <firstterm>row security policies</firstterm> that restrict,
   on a per-user basis, which

Title: Examples of Access Privileges Display and Row Security Policies
Summary
This section provides examples of how access privileges are displayed using the `\dp` command in psql, including scenarios involving PUBLIC, specific users, and column-level privileges. It clarifies how default privileges are handled, how explicit grants and revocations affect the displayed privileges, and how the absence of any privileges is indicated. It also introduces the concept of row security policies, which allow for restrictions on which rows can be accessed on a per-user basis.