user-manag.sgml

<Chapter id="user-manag">
 <title>Database Users and Permissions</title>

 <para>
  Managing database users and their privileges is in concept similar
  to managing users of a Unix operating system, but the details are not
  identical.
 </para>

 <sect1 id="database-users">
  <title>Database Users</title>

  <para>
   Database users are conceptually completely separate from
   operating system users. In practice it might be convenient to
   maintain a correspondence, but this is not required. Database user
   names are global across a database cluster installation (and not
   per individual database). To create a user use the <command>CREATE
   USER</command> SQL command:
<synopsis>
CREATE USER <replaceable>name</replaceable>
</synopsis>
   <replaceable>name</replaceable> follows the rules for SQL
   identifiers: either unadorned without special characters, or
   double-quoted. To remove an existing user, use the analog
   <command>DROP USER</command> command.
  </para>

  <para>
   For convenience, the shell scripts <filename>createuser</filename>
   and <filename>dropuser</filename> are provided as wrappers around these SQL
   commands.
  </para>

  <para>
   In order to bootstrap the database system, a freshly initialized
   system always contains one predefined user. This user will have the
   fixed id 1, and by default (unless altered when running
   <command>initdb</command>) it will have the same name as the
   operating system user that initialized the area (and is presumably
   being used as the user that runs the server). Customarily, this user
   will be named <systemitem>postgres</systemitem>. In order to create more
   users you first have to connect as this initial user.
  </para>

  <para>
   The user 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 user to connect as. The set of
   database users a given client connection may connect as is
   determined by the client authentication setup, as explained in
   <xref linkend="client-authentication">. (Thus, a client is not
   necessarily limited to connect as the user with the same name as
   its operating system user, in the same way a person is not
   constrained in its login name by her real name.)
  </para>

  <sect2 id="user-attributes">
   <title>User attributes</title>

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

    <variablelist>
     <varlistentry>
      <term>superuser</term>
      <listitem>
       <para>
        A database superuser bypasses all permission checks. Also,
        only a superuser can create new users. To create a database
        superuser, use <literal>CREATE USER name
        CREATEUSER</literal>.
       </para>
      </listitem>
     </varlistentry>

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

     <varlistentry>
      <term>password</term>
      <listitem>
       <para>
        A password is only significant if password authentication is
        used for client authentication. Database passwords are separate
        from operating system passwords. Specify a password upon
        user creation with <literal>CREATE USER name PASSWORD
        'string'</literal>.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

    A user's attributes can be modified after creation with
    <command>ALTER USER</command>.
    See the reference pages for <command>CREATE USER</command> and
    <command>ALTER USER</command> for details.
   </para>
  </sect2>
 </sect1>

 <sect1 id="groups">
  <title>Groups</title>

  <para>
   As in Unix, groups are a way of logically grouping users to ease
   management of permissions: permissions can be granted to, or revoked
   from, a group as a whole.  To create a group, use
<synopsis>
CREATE GROUP <replaceable>name</replaceable>
</synopsis>
   To add users to or remove users from a group, use
<synopsis>
ALTER GROUP <replaceable>name</replaceable> ADD USER <replaceable>uname1</replaceable>, ...
ALTER GROUP <replaceable>name</replaceable> DROP USER <replaceable>uname1</replaceable>, ...
</synopsis>
  </para>
 </sect1>

 <sect1 id="privileges">
  <title>Privileges</title>

  <para>
   When a database object is created, it is assigned an owner. The
   owner is the user that executed the creation statement. There is
   currently no polished interface for changing the owner of a database
   object. By default, only an owner (or a superuser) can do anything
   with the object. In order to allow other users to use it,
   <firstterm>privileges</firstterm> must be granted.
  </para>

  <para>
   There are several different privileges: <literal>SELECT</literal>
   (read), <literal>INSERT</literal> (append), <literal>UPDATE</literal>
   (write), <literal>DELETE</literal>, <literal>RULE</literal>,
   <literal>REFERENCES</literal> (foreign key), and
   <literal>TRIGGER</literal>. (See the <command>GRANT</command> manual
   page for more detailed information.) The right to modify or destroy
   an object is always the privilege of the owner only. To assign
   privileges, the <command>GRANT</command> command is used. So, if
   <literal>joe</literal> is an existing user, and
   <literal>accounts</literal> is an existing table, write access can be
   granted with
<programlisting>
GRANT UPDATE ON accounts TO joe;
</programlisting>
   The user executing this command must be the owner of the table. To
   grant a privilege to a group, use
<programlisting>
GRANT SELECT ON accounts TO GROUP staff;
</programlisting>
   The special <quote>user</quote> name <literal>PUBLIC</literal> can
   be used to grant a privilege to every user on the system. Using
   <literal>ALL</literal> in place of a specific privilege specifies that all
   privileges will be granted.
  </para>

  <para>
   To revoke a privilege, use the fittingly named
   <command>REVOKE</command> command:
<programlisting>
REVOKE ALL ON accounts FROM PUBLIC;
</programlisting>
   The special privileges of the table owner are always implicit
   and cannot be granted or revoked.
  </para>
 </sect1>

 <sect1 id="perm-functions">
  <title>Functions and Triggers</title>

  <para>
   Functions and triggers allow users to insert code into the backend
   server that other users may execute without knowing it. Hence, both
   mechanisms permit users to <firstterm>Trojan horse</firstterm>
   others with relative impunity. The only real protection is tight
   control over who can define functions (e.g., write to relations
   with SQL fields) and triggers. Audit trails and alerters on the
   system catalogs <literal>pg_class</literal>,
   <literal>pg_shadow</literal> and <literal>pg_group</literal> are also
   possible.
  </para>

  <para>
   Functions written in any language except SQL run inside the backend
   server process with the operating systems permissions of the
   database server daemon process. It is possible to change the
   server's internal data structures from inside of trusted functions.
   Hence, among many other things, such functions can circumvent any
   system access controls. This is an inherent problem with
   user-defined C functions.
  </para>

 </sect1>

</Chapter>