user-manag.sgml

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

<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</> RESET <replaceable>varname</>;</literal>.
   Note that role-specific defaults attached to roles without
   <literal>LOGIN</> privilege are fairly useless, since they will never
   be invoked.
  </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>

  <para>
   When an object is created, it is assigned an owner. The
   owner is normally the role that executed the creation statement.
   For most kinds of objects, the initial state is that only the owner
   (or a superuser) can do anything with the object. To allow
   other roles to use it, <firstterm>privileges</firstterm> must be
   granted.
   There are several different kinds of privilege: <literal>SELECT</>,
   <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
   <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
   <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
   and <literal>USAGE</>. For more
   information on the different types of privileges supported by
   <productname>PostgreSQL</productname>, see the
   <xref linkend="sql-grant" endterm="sql-grant-title"> reference page.
  </para>

  <para>
   To assign privileges, the <command>GRANT</command> command is
   used. So, if <literal>joe</literal> is an existing role, 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 special name <literal>PUBLIC</literal> can
   be used to grant a privilege to every role on the system. Writing
   <literal>ALL</literal> in place of a specific privilege specifies that all
   privileges that apply to the object will be granted.
  </para>

  <para>
   To revoke a privilege, use the fittingly named
   <xref linkend="sql-revoke" endterm="sql-revoke-title"> command:
<programlisting>
REVOKE ALL ON accounts FROM PUBLIC;
</programlisting>
  </para>

  <para>
   The special privileges of an object's owner (i.e., the right to modify
   or destroy the object) are always implicit in being the owner,
   and cannot be granted or revoked.  But the owner can choose
   to revoke his own ordinary privileges, for example to make a
   table read-only for himself as well as others.
  </para>

  <para>
   An object can be assigned to a new owner with an <command>ALTER</command>
   command of the appropriate kind for the object.  Superusers can always do
   this; ordinary roles can only do it if they are both the current owner
   of the object (or a member of the owning role) and a member of the new
   owning role.
  </para>
 </sect1>

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

  <indexterm zone="role-membership">
   <primary>role</><secondary>membership in</>
  </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</> 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</>
   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" endterm="sql-grant-title"> and
   <xref linkend="sql-revoke" endterm="sql-revoke-title"> 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
   only restriction is that you can't set up circular membership loops.
  </para>

  <para>
   The members of a role can use the privileges of the group role in two
   ways.  First, every member of a group can explicitly do
   <xref linkend="sql-set-role" endterm="sql-set-role-title"> to
   temporarily <quote>become</> 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</> attribute automatically have use of
   privileges of roles they are members of.  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</>, a database
   session will have use of privileges granted directly to <literal>joe</>
   plus any privileges granted to <literal>admin</>, because <literal>joe</>
   <quote>inherits</> <literal>admin</>'s privileges.  However, privileges
   granted to <literal>wheel</> are not available, because even though
   <literal>joe</> is indirectly a member of <literal>wheel</>, the
   membership is via <literal>admin</> which has the <literal>NOINHERIT</>
   attribute.  After
<programlisting>
SET ROLE admin;
</programlisting>
   the session would have use of only those privileges granted to
   <literal>admin</>, and not those granted to <literal>joe</>.  After
<programlisting>
SET ROLE wheel;
</programlisting>
   the session would have use of only those privileges granted to
   <literal>wheel</>, and not those granted to either <literal>joe</>
   or <literal>admin</>.  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 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</> before becoming <literal>wheel</>.
   </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</> attribute, while
    giving roles being used as SQL users the <literal>NOINHERIT</> attribute.
    However, <productname>PostgreSQL</productname> defaults to giving all roles
    the <literal>INHERIT</> attribute, for backwards 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>SUPERUSER</>,
   <literal>CREATEDB</>, and <literal>CREATEROLE</> 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</> to a
   specific role having one of these attributes in order to make use of
   the attribute.  Continuing the above example, we might well choose to
   grant <literal>CREATEDB</> and <literal>CREATEROLE</> to the
   <literal>admin</> role.  Then a session connecting as role <literal>joe</>
   would not have these privileges immediately, only after doing
   <command>SET ROLE admin</>.
  </para>

  <para>
  </para>

  <para>
   To destroy a group role, use <xref
   linkend="sql-droprole" endterm="sql-droprole-title">:
<synopsis>
DROP ROLE <replaceable>name</replaceable>;
</synopsis>
   Any memberships in the group role are automatically revoked (but the
   member roles are not otherwise affected).  Note however that any objects
   owned by the group role must first be dropped or reassigned to other
   owners; and any permissions granted to the group role must be 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 unintentionally. Hence, both
   mechanisms permit users to <quote>Trojan horse</quote>
   others with relative ease. The only real protection is tight
   control over who can define functions.
  </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</>, and
   <productname>PostgreSQL</productname> allows only superusers to
   create functions written in those languages.
  </para>
 </sect1>

</chapter>