user-manag.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/user-manag.sgml,v 1.22 2003/08/31 17:32:20 petere Exp $
-->

<chapter id="user-manag">
 <title>Database Users and Privileges</title>

 <para>
  Every database cluster contains a set of database users.  Those
  users are separate from the users managed by the operating system on
  which the server runs.  Users own database objects (for example,
  tables) and can assign privileges on those objects to other users to
  control who has access to which object.
 </para>

 <para>
  This chapter describes how to create and manage users and introduces
  the privilege system.  More information about the various types of
  database objects and the effects of privileges can be found in <xref linkend="ddl">.
 </para>

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

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

  <indexterm>
   <primary>CREATE USER</primary>
  </indexterm>

  <indexterm>
   <primary>DROP USER</primary>
  </indexterm>

  <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 analogous
   <command>DROP USER</command> command:
<synopsis>
DROP USER <replaceable>name</replaceable>;
</synopsis>
  </para>

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

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

  <para>
   For convenience, the programs <command>createuser</command>
   and <command>dropuser</command> 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>
   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 database
   cluster. Customarily, this user will be named
   <literal>postgres</literal>. In order to create more users you
   first have to connect as this initial user.
  </para>

  <para>
   Exactly one user identity is active for a connection to the
   database server.  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.  Many applications assume the name of the current
   operating system user by default (including
   <command>createuser</> and <command>psql</>).  Therefore it
   is convenient to maintain a naming correspondence between the two
   user sets.
  </para>

  <para>
   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.)  Since the user
   identity determines the set of privileges available to a connected
   client, it is important to carefully configure this when setting up
   a multiuser environment.
  </para>
 </sect1>

 <sect1 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<indexterm><primary>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 <replaceable>name</replaceable>
        CREATEUSER</literal>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>database creation<indexterm><primary>database</><secondary>privilege to create</></></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
        <replaceable>name</replaceable> CREATEDB</literal>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>password<indexterm><primary>password</></></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>md5</>, and <option>crypt</> authentication methods
        make use of passwords. Database passwords are separate from
        operating system passwords. Specify a password upon user
        creation with <literal>CREATE USER
        <replaceable>name</replaceable> PASSWORD '<replaceable>string</>'</literal>. 
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

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

  <para>
   A user can also set personal 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 USER myname SET enable_indexscan TO off;
</programlisting>
   This will save the setting (but not set it immediately) and in
   subsequent connections it will appear as though <literal>SET enable_indexscan
   TO off;</literal> had been called right before the session started.
   You can still alter this setting during the session; it will only
   be the default. To undo any such setting, use <literal>ALTER USER
   <replaceable>username</> RESET <replaceable>varname</>;</literal>.
  </para>
 </sect1>

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

  <indexterm zone="groups">
   <primary>group</primary>
  </indexterm>

  <para>
   As in Unix, groups are a way of logically grouping users to ease
   management of privileges: privileges 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>

  <indexterm zone="privileges">
   <primary>privilege</primary>
  </indexterm>

  <indexterm zone="privileges">
   <primary>owner</primary>
  </indexterm>

  <indexterm zone="privileges">
   <primary>GRANT</primary>
  </indexterm>

  <indexterm zone="privileges">
   <primary>REVOKE</primary>
  </indexterm>

  <comment>Being moved to the DDL chapter.  Will eventually disappear here.</comment>

  <para>
   When a database object is created, it is assigned an owner. The
   owner is the user that executed the creation statement. To change
   the owner of a table, index, sequence, or view, use the
   <command>ALTER TABLE</command> command. 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>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
   <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
   <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
   <literal>USAGE</>, and <literal>ALL PRIVILEGES</>. For more
   information on the different types of privileges support by
   <productname>PostgreSQL</productname>, see the
   <xref linkend="sql-grant" endterm="sql-grant-title"> reference page.
   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, the privilege to
   update the table 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. Writing
   <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 (i.e., the right to do
   <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc)
   are always implicit in being the owner,
   and cannot be granted or revoked.  But the table owner can choose
   to revoke his own ordinary privileges, for example to make a
   table read-only for himself as well as others.
  </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 <quote>Trojan horse</quote>
   others with relative impunity. The only real protection is tight
   control over who can define functions.
  </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>