xref: /dports/databases/postgresql13-contrib/postgresql-13.5/doc/src/sgml/user-manag.sgml

<!-- doc/src/sgml/user-manag.sgml -->

<chapter id="user-manag">
 <title>Database Roles</title>

 <para>
  <productname>PostgreSQL</productname> manages database access permissions
  using the concept of <firstterm>roles</firstterm>.  A role can be thought of as
  either a database user, or a group of database users, depending on how
  the role is set up.  Roles can own database objects (for example, tables
  and functions) and can assign privileges on those objects to other roles to
  control who has access to which objects.  Furthermore, it is possible
  to grant <firstterm>membership</firstterm> in a role to another role, thus
  allowing the member role to use privileges assigned to another role.
 </para>

 <para>
  The concept of roles subsumes the concepts of <quote>users</quote> and
  <quote>groups</quote>.  In <productname>PostgreSQL</productname> versions
  before 8.1, users and groups were distinct kinds of entities, but now
  there are only roles.  Any role can act as a user, a group, or both.
 </para>

 <para>
  This chapter describes how to create and manage roles.
  More information about the effects of role privileges on various
  database objects can be found in <xref linkend="ddl-priv"/>.
 </para>

 <sect1 id="database-roles">
  <title>Database Roles</title>

  <indexterm zone="database-roles">
   <primary>role</primary>
  </indexterm>

  <indexterm zone="database-roles">
   <primary>user</primary>
  </indexterm>

  <indexterm>
   <primary>CREATE ROLE</primary>
  </indexterm>

  <indexterm>
   <primary>DROP ROLE</primary>
  </indexterm>

  <para>
   Database roles are conceptually completely separate from
   operating system users. In practice it might be convenient to
   maintain a correspondence, but this is not required. Database roles
   are global across a database cluster installation (and not
   per individual database). To create a role use the <xref
   linkend="sql-createrole"/> SQL command:
<synopsis>
CREATE ROLE <replaceable>name</replaceable>;
</synopsis>
   <replaceable>name</replaceable> follows the rules for SQL
   identifiers: either unadorned without special characters, or
   double-quoted.  (In practice, you will usually want to add additional
   options, such as <literal>LOGIN</literal>, to the command.  More details appear
   below.)  To remove an existing role, use the analogous
   <xref linkend="sql-droprole"/> command:
<synopsis>
DROP ROLE <replaceable>name</replaceable>;
</synopsis>
  </para>

  <indexterm>
   <primary>createuser</primary>
  </indexterm>

  <indexterm>
   <primary>dropuser</primary>
  </indexterm>

  <para>
   For convenience, the programs <xref linkend="app-createuser"/>
   and <xref linkend="app-dropuser"/> are provided as wrappers
   around these SQL commands that can be called from the shell command
   line:
<synopsis>
createuser <replaceable>name</replaceable>
dropuser <replaceable>name</replaceable>
</synopsis>
  </para>

  <para>
   To determine the set of existing roles, examine the <structname>pg_roles</structname>
   system catalog, for example
<synopsis>
SELECT rolname FROM pg_roles;
</synopsis>
   The <xref linkend="app-psql"/> program's <literal>\du</literal> meta-command
   is also useful for listing the existing roles.
  </para>

  <para>
   In order to bootstrap the database system, a freshly initialized
   system always contains one predefined role. This role is always
   a <quote>superuser</quote>, and by default (unless altered when running
   <command>initdb</command>) it will have the same name as the
   operating system user that initialized the database
   cluster. Customarily, this role will be named
   <literal>postgres</literal>. In order to create more roles you
   first have to connect as this initial role.
  </para>

  <para>
   Every connection to the database server is made using the name of some
   particular role, and this role determines the initial access privileges for
   commands issued in that connection.
   The role name to use for a particular database
   connection is indicated by the client that is initiating the
   connection request in an application-specific fashion. For example,
   the <command>psql</command> program uses the
   <option>-U</option> command line option to indicate the role to
   connect as.  Many applications assume the name of the current
   operating system user by default (including
   <command>createuser</command> and <command>psql</command>).  Therefore it
   is often convenient to maintain a naming correspondence between
   roles and operating system users.
  </para>

  <para>
   The set of database roles a given client connection can connect as
   is determined by the client authentication setup, as explained in
   <xref linkend="client-authentication"/>. (Thus, a client is not
   limited to connect as the role matching
   its operating system user, just as a person's login name
   need not match his or her real name.)  Since the role
   identity determines the set of privileges available to a connected
   client, it is important to carefully configure privileges when setting up
   a multiuser environment.
  </para>
 </sect1>

 <sect1 id="role-attributes">
  <title>Role Attributes</title>

   <para>
    A database role can have a number of attributes that define its
    privileges and interact with the client authentication system.

    <variablelist>
     <varlistentry>
      <term>login privilege<indexterm><primary>login privilege</primary></indexterm></term>
      <listitem>
       <para>
        Only roles that have the <literal>LOGIN</literal> attribute can be used
        as the initial role name for a database connection.  A role with
        the <literal>LOGIN</literal> attribute can be considered the same
        as a <quote>database user</quote>.  To create a role with login privilege,
        use either:
<programlisting>
CREATE ROLE <replaceable>name</replaceable> LOGIN;
CREATE USER <replaceable>name</replaceable>;
</programlisting>
        (<command>CREATE USER</command> is equivalent to <command>CREATE ROLE</command>
        except that <command>CREATE USER</command> includes <literal>LOGIN</literal> by
        default, while <command>CREATE ROLE</command> does not.)
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>superuser status<indexterm><primary>superuser</primary></indexterm></term>
      <listitem>
       <para>
        A database superuser bypasses all permission checks, except the right
        to log in.  This is a dangerous privilege and should not be used
        carelessly; it is best to do most of your work as a role that is not a
        superuser.  To create a new database superuser, use <literal>CREATE
        ROLE <replaceable>name</replaceable> SUPERUSER</literal>.  You must do
        this as a role that is already a superuser.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>database creation<indexterm><primary>database</primary><secondary>privilege to create</secondary></indexterm></term>
      <listitem>
       <para>
        A role must be explicitly given permission to create databases
        (except for superusers, since those bypass all permission
        checks). To create such a role, use <literal>CREATE ROLE
        <replaceable>name</replaceable> CREATEDB</literal>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>role creation<indexterm><primary>role</primary><secondary>privilege to create</secondary></indexterm></term>
      <listitem>
       <para>
        A role must be explicitly given permission to create more roles
        (except for superusers, since those bypass all permission
        checks). To create such a role, use <literal>CREATE ROLE
        <replaceable>name</replaceable> CREATEROLE</literal>.
        A role with <literal>CREATEROLE</literal> privilege can alter and drop
        other roles, too, as well as grant or revoke membership in them.
        However, to create, alter, drop, or change membership of a
        superuser role, superuser status is required;
        <literal>CREATEROLE</literal> is insufficient for that.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>initiating replication<indexterm><primary>role</primary><secondary>privilege to initiate replication</secondary></indexterm></term>
      <listitem>
       <para>
        A role must explicitly be given permission to initiate streaming
        replication (except for superusers, since those bypass all permission
        checks). A role used for streaming replication must
        have <literal>LOGIN</literal> permission as well. To create such a role, use
        <literal>CREATE ROLE <replaceable>name</replaceable> REPLICATION
        LOGIN</literal>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>password<indexterm><primary>password</primary></indexterm></term>
      <listitem>
       <para>
        A password is only significant if the client authentication
        method requires the user to supply a password when connecting
        to the database. The <option>password</option> and
        <option>md5</option> authentication methods
        make use of passwords. Database passwords are separate from
        operating system passwords. Specify a password upon role
        creation with <literal>CREATE ROLE
        <replaceable>name</replaceable> PASSWORD '<replaceable>string</replaceable>'</literal>.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

    A role's attributes can be modified after creation with
    <command>ALTER ROLE</command>.<indexterm><primary>ALTER ROLE</primary></indexterm>
    See the reference pages for the <xref linkend="sql-createrole"/>
    and <xref linkend="sql-alterrole"/> commands for details.
   </para>

  <tip>
   <para>
    It is good practice to create a role that has the <literal>CREATEDB</literal>
    and <literal>CREATEROLE</literal> privileges, but is not a superuser, and then
    use this role for all routine management of databases and roles.  This
    approach avoids the dangers of operating as a superuser for tasks that
    do not really require it.
   </para>
  </tip>

  <para>
   A role can also have role-specific defaults for many of the run-time
   configuration settings described in <xref
   linkend="runtime-config"/>.  For example, if for some reason you
   want to disable index scans (hint: not a good idea) anytime you
   connect, you can use:
<programlisting>
ALTER ROLE myname SET enable_indexscan TO off;
</programlisting>
   This will save the setting (but not set it immediately).  In
   subsequent connections by this role it will appear as though
   <literal>SET enable_indexscan TO off</literal> had been executed
   just before the session started.
   You can still alter this setting during the session; it will only
   be the default. To remove a role-specific default setting, use
   <literal>ALTER ROLE <replaceable>rolename</replaceable> RESET <replaceable>varname</replaceable></literal>.
   Note that role-specific defaults attached to roles without
   <literal>LOGIN</literal> privilege are fairly useless, since they will never
   be invoked.
  </para>
 </sect1>

 <sect1 id="role-membership">
  <title>Role Membership</title>

  <indexterm zone="role-membership">
   <primary>role</primary><secondary>membership in</secondary>
  </indexterm>

  <para>
   It is frequently convenient to group users together to ease
   management of privileges: that way, privileges can be granted to, or
   revoked from, a group as a whole.  In <productname>PostgreSQL</productname>
   this is done by creating a role that represents the group, and then
   granting <firstterm>membership</firstterm> in the group role to individual user
   roles.
  </para>

  <para>
   To set up a group role, first create the role:
<synopsis>
CREATE ROLE <replaceable>name</replaceable>;
</synopsis>
   Typically a role being used as a group would not have the <literal>LOGIN</literal>
   attribute, though you can set it if you wish.
  </para>

  <para>
   Once the group role exists, you can add and remove members using the
   <xref linkend="sql-grant"/> and
   <xref linkend="sql-revoke"/> commands:
<synopsis>
GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ;
REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ;
</synopsis>
   You can grant membership to other group roles, too (since there isn't
   really any distinction between group roles and non-group roles).  The
   database will not let you set up circular membership loops.  Also,
   it is not permitted to grant membership in a role to
   <literal>PUBLIC</literal>.
  </para>

  <para>
   The members of a group role can use the privileges of the role in two
   ways.  First, every member of a group can explicitly do
   <xref linkend="sql-set-role"/> to
   temporarily <quote>become</quote> the group role.  In this state, the
   database session has access to the privileges of the group role rather
   than the original login role, and any database objects created are
   considered owned by the group role not the login role.  Second, member
   roles that have the <literal>INHERIT</literal> attribute automatically have use
   of the privileges of roles of which they are members, including any
   privileges inherited by those roles.
   As an example, suppose we have done:
<programlisting>
CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;
</programlisting>
   Immediately after connecting as role <literal>joe</literal>, a database
   session will have use of privileges granted directly to <literal>joe</literal>
   plus any privileges granted to <literal>admin</literal>, because <literal>joe</literal>
   <quote>inherits</quote> <literal>admin</literal>'s privileges.  However, privileges
   granted to <literal>wheel</literal> are not available, because even though
   <literal>joe</literal> is indirectly a member of <literal>wheel</literal>, the
   membership is via <literal>admin</literal> which has the <literal>NOINHERIT</literal>
   attribute.  After:
<programlisting>
SET ROLE admin;
</programlisting>
   the session would have use of only those privileges granted to
   <literal>admin</literal>, and not those granted to <literal>joe</literal>.  After:
<programlisting>
SET ROLE wheel;
</programlisting>
   the session would have use of only those privileges granted to
   <literal>wheel</literal>, and not those granted to either <literal>joe</literal>
   or <literal>admin</literal>.  The original privilege state can be restored
   with any of:
<programlisting>
SET ROLE joe;
SET ROLE NONE;
RESET ROLE;
</programlisting>
  </para>

  <note>
   <para>
    The <command>SET ROLE</command> command always allows selecting any role
    that the original login role is directly or indirectly a member of.
    Thus, in the above example, it is not necessary to become
    <literal>admin</literal> before becoming <literal>wheel</literal>.
   </para>
  </note>

  <note>
   <para>
    In the SQL standard, there is a clear distinction between users and roles,
    and users do not automatically inherit privileges while roles do.  This
    behavior can be obtained in <productname>PostgreSQL</productname> by giving
    roles being used as SQL roles the <literal>INHERIT</literal> attribute, while
    giving roles being used as SQL users the <literal>NOINHERIT</literal> attribute.
    However, <productname>PostgreSQL</productname> defaults to giving all roles
    the <literal>INHERIT</literal> attribute, for backward compatibility with pre-8.1
    releases in which users always had use of permissions granted to groups
    they were members of.
   </para>
  </note>

  <para>
   The role attributes <literal>LOGIN</literal>, <literal>SUPERUSER</literal>,
   <literal>CREATEDB</literal>, and <literal>CREATEROLE</literal> can be thought of as
   special privileges, but they are never inherited as ordinary privileges
   on database objects are.  You must actually <command>SET ROLE</command> to a
   specific role having one of these attributes in order to make use of
   the attribute.  Continuing the above example, we might choose to
   grant <literal>CREATEDB</literal> and <literal>CREATEROLE</literal> to the
   <literal>admin</literal> role.  Then a session connecting as role <literal>joe</literal>
   would not have these privileges immediately, only after doing
   <command>SET ROLE admin</command>.
  </para>

  <para>
  </para>

  <para>
   To destroy a group role, use <xref
   linkend="sql-droprole"/>:
<synopsis>
DROP ROLE <replaceable>name</replaceable>;
</synopsis>
   Any memberships in the group role are automatically revoked (but the
   member roles are not otherwise affected).
  </para>
 </sect1>

 <sect1 id="role-removal">
  <title>Dropping Roles</title>

  <para>
   Because roles can own database objects and can hold privileges
   to access other objects, dropping a role is often not just a matter of a
   quick <xref linkend="sql-droprole"/>.  Any objects owned by the role must
   first be dropped or reassigned to other owners; and any permissions
   granted to the role must be revoked.
  </para>

  <para>
   Ownership of objects can be transferred one at a time
   using <command>ALTER</command> commands, for example:
<programlisting>
ALTER TABLE bobs_table OWNER TO alice;
</programlisting>
   Alternatively, the <xref linkend="sql-reassign-owned"/> command can be
   used to reassign ownership of all objects owned by the role-to-be-dropped
   to a single other role.  Because <command>REASSIGN OWNED</command> cannot access
   objects in other databases, it is necessary to run it in each database
   that contains objects owned by the role.  (Note that the first
   such <command>REASSIGN OWNED</command> will change the ownership of any
   shared-across-databases objects, that is databases or tablespaces, that
   are owned by the role-to-be-dropped.)
  </para>

  <para>
   Once any valuable objects have been transferred to new owners, any
   remaining objects owned by the role-to-be-dropped can be dropped with
   the <xref linkend="sql-drop-owned"/> command.  Again, this command cannot
   access objects in other databases, so it is necessary to run it in each
   database that contains objects owned by the role.  Also, <command>DROP
   OWNED</command> will not drop entire databases or tablespaces, so it is
   necessary to do that manually if the role owns any databases or
   tablespaces that have not been transferred to new owners.
  </para>

  <para>
   <command>DROP OWNED</command> also takes care of removing any privileges granted
   to the target role for objects that do not belong to it.
   Because <command>REASSIGN OWNED</command> does not touch such objects, it's
   typically necessary to run both <command>REASSIGN OWNED</command>
   and <command>DROP OWNED</command> (in that order!) to fully remove the
   dependencies of a role to be dropped.
  </para>

  <para>
   In short then, the most general recipe for removing a role that has been
   used to own objects is:
  </para>
<programlisting>
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- repeat the above commands in each database of the cluster
DROP ROLE doomed_role;
</programlisting>

  <para>
   When not all owned objects are to be transferred to the same successor
   owner, it's best to handle the exceptions manually and then perform
   the above steps to mop up.
  </para>

  <para>
   If <command>DROP ROLE</command> is attempted while dependent objects still
   remain, it will issue messages identifying which objects need to be
   reassigned or dropped.
  </para>
 </sect1>

 <sect1 id="default-roles">
  <title>Default Roles</title>

  <indexterm zone="default-roles">
   <primary>role</primary>
  </indexterm>

  <para>
   <productname>PostgreSQL</productname> provides a set of default roles
   which provide access to certain, commonly needed, privileged capabilities
   and information.  Administrators can GRANT these roles to users and/or
   other roles in their environment, providing those users with access to
   the specified capabilities and information.
  </para>

  <para>
   The default roles are described in <xref linkend="default-roles-table"/>.
   Note that the specific permissions for each of the default roles may
   change in the future as additional capabilities are added.  Administrators
   should monitor the release notes for changes.
  </para>

   <table tocentry="1" id="default-roles-table">
    <title>Default Roles</title>
    <tgroup cols="2">
     <colspec colname="col1" colwidth="1*"/>
     <colspec colname="col2" colwidth="2*"/>
     <thead>
      <row>
       <entry>Role</entry>
       <entry>Allowed Access</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>pg_read_all_settings</entry>
       <entry>Read all configuration variables, even those normally visible only to
       superusers.</entry>
      </row>
      <row>
       <entry>pg_read_all_stats</entry>
       <entry>Read all pg_stat_* views and use various statistics related extensions,
       even those normally visible only to superusers.</entry>
      </row>
      <row>
       <entry>pg_stat_scan_tables</entry>
       <entry>Execute monitoring functions that may take <literal>ACCESS SHARE</literal> locks on tables,
       potentially for a long time.</entry>
      </row>
      <row>
       <entry>pg_monitor</entry>
       <entry>Read/execute various monitoring views and functions.
       This role is a member of <literal>pg_read_all_settings</literal>,
       <literal>pg_read_all_stats</literal> and
       <literal>pg_stat_scan_tables</literal>.</entry>
      </row>
      <row>
       <entry>pg_signal_backend</entry>
       <entry>Signal another backend to cancel a query or terminate its session.</entry>
      </row>
      <row>
       <entry>pg_read_server_files</entry>
       <entry>Allow reading files from any location the database can access on the server with COPY and
       other file-access functions.</entry>
      </row>
      <row>
       <entry>pg_write_server_files</entry>
       <entry>Allow writing to files in any location the database can access on the server with COPY and
       other file-access functions.</entry>
      </row>
      <row>
       <entry>pg_execute_server_program</entry>
       <entry>Allow executing programs on the database server as the user the database runs as with
       COPY and other functions which allow executing a server-side program.</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
  The <literal>pg_monitor</literal>, <literal>pg_read_all_settings</literal>,
  <literal>pg_read_all_stats</literal> and <literal>pg_stat_scan_tables</literal>
  roles are intended to allow administrators to easily configure a role for the
  purpose of monitoring the database server. They grant a set of common privileges
  allowing the role to read various useful configuration settings, statistics and
  other system information normally restricted to superusers.
  </para>

  <para>
  The <literal>pg_signal_backend</literal> role is intended to allow
  administrators to enable trusted, but non-superuser, roles to send signals
  to other backends. Currently this role enables sending of signals for
  canceling a query on another backend or terminating its session. A user
  granted this role cannot however send signals to a backend owned by a
  superuser.  See <xref linkend="functions-admin-signal"/>.
  </para>

  <para>
  The <literal>pg_read_server_files</literal>, <literal>pg_write_server_files</literal> and
  <literal>pg_execute_server_program</literal> roles are intended to allow administrators to have
  trusted, but non-superuser, roles which are able to access files and run programs on the
  database server as the user the database runs as.  As these roles are able to access any file on
  the server file system, they bypass all database-level permission checks when accessing files
  directly and they could be used to gain superuser-level access, therefore
  great care should be taken when granting these roles to users.
  </para>

  <para>
  Care should be taken when granting these roles to ensure they are only used where
  needed and with the understanding that these roles grant access to privileged
  information.
  </para>

  <para>
   Administrators can grant access to these roles to users using the
   <xref linkend="sql-grant"/> command, for example:

<programlisting>
GRANT pg_signal_backend TO admin_user;
</programlisting>
  </para>

 </sect1>

 <sect1 id="perm-functions">
  <title>Function Security</title>

  <para>
   Functions, triggers and row-level security policies allow users to insert
   code into the backend server that other users might execute
   unintentionally. Hence, these mechanisms permit users to <quote>Trojan
   horse</quote> others with relative ease. The strongest protection is tight
   control over who can define objects. Where that is infeasible, write
   queries referring only to objects having trusted owners.  Remove
   from <varname>search_path</varname> the public schema and any other schemas
   that permit untrusted users to create objects.
  </para>

  <para>
   Functions run inside the backend
   server process with the operating system permissions of the
   database server daemon.  If the programming language
   used for the function allows unchecked memory accesses, it is
   possible to change the server's internal data structures.
   Hence, among many other things, such functions can circumvent any
   system access controls.  Function languages that allow such access
   are considered <quote>untrusted</quote>, and
   <productname>PostgreSQL</productname> allows only superusers to
   create functions written in those languages.
  </para>
 </sect1>

</chapter>