user-manag.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

<!--
$PostgreSQL: pgsql/doc/src/sgml/user-manag.sgml,v 1.33 2005/10/20 19:18:00 tgl Exp $
-->

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

 <para>
  <productname>PostgreSQL</productname> manages database access permissions
  using the concept of <firstterm>roles</>.  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 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</> in a role to another role, thus
  allowing the member role use of privileges assigned to the role it is
  a member of.
 </para>

 <para>
  The concept of roles subsumes the concepts of <quote>users</> and
  <quote>groups</>.  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 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-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" endterm="sql-createrole-title"> 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</>, to the command.  More details appear
   below.)  To remove an existing role, use the analogous
   <xref linkend="sql-droprole" endterm="sql-droprole-title"> 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</>
   system catalog, for example
<synopsis>
SELECT rolname FROM pg_roles;
</synopsis>
   The <xref linkend="app-psql"> program's <literal>\du</> 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</>, 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 in the name of some
   particular role, and this role determines the initial access privileges for
   commands issued on 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</> and <command>psql</>).  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 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 role with the same name as
   its operating system user, just as a person's login name 
   need not match her real name.)  Since the role
   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="role-attributes">
  <title>Role Attributes</title>

   <para>
    A database role may 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</></></term>
      <listitem>
       <para>
        Only roles that have the <literal>LOGIN</> attribute can be used
        as the initial role name for a database connection.  A role with
        the <literal>LOGIN</> attribute can be considered the same thing
        as a <quote>database user</>.  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</> is equivalent to <command>CREATE ROLE</>
        except that <command>CREATE USER</> assumes <literal>LOGIN</> by
        default, while <command>CREATE ROLE</> does not.)
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>superuser status<indexterm><primary>superuser</></></term>
      <listitem>
       <para>
        A database superuser bypasses all permission checks.  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</><secondary>privilege to create</></></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</><secondary>privilege to create</></></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</> 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</> is not sufficient for that.
       </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 role
        creation with <literal>CREATE ROLE
        <replaceable>name</replaceable> PASSWORD '<replaceable>string</>'</literal>. 
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

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

  <tip>
   <para>
    It is good practice to create a role that has the <literal>CREATEDB</>
    and <literal>CREATEROLE</> 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