~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security : contents of doc/src/sgml/html/role-membership.html at revision 14

~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security : (revision 14)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Role Membership</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.8 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Database Roles"
HREF="user-manag.html"><LINK
REL="PREVIOUS"
TITLE="Role Attributes"
HREF="role-attributes.html"><LINK
REL="NEXT"
TITLE="Function and Trigger Security"
HREF="perm-functions.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2013-02-04T21:38:53"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.8 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Role Attributes"
HREF="role-attributes.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="user-manag.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 20. Database Roles</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Function and Trigger Security"
HREF="perm-functions.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="ROLE-MEMBERSHIP"
>20.3. Role Membership</A
></H1
><P
>   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 <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
   this is done by creating a role that represents the group, and then
   granting <I
CLASS="FIRSTTERM"
>membership</I
> in the group role to individual user
   roles.
  </P
><P
>   To set up a group role, first create the role:
</P><PRE
CLASS="SYNOPSIS"
>CREATE ROLE <TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
>;</PRE
><P>
   Typically a role being used as a group would not have the <TT
CLASS="LITERAL"
>LOGIN</TT
>
   attribute, though you can set it if you wish.
  </P
><P
>   Once the group role exists, you can add and remove members using the
   <A
HREF="sql-grant.html"
>GRANT</A
> and
   <A
HREF="sql-revoke.html"
>REVOKE</A
> commands:
</P><PRE
CLASS="SYNOPSIS"
>GRANT <TT
CLASS="REPLACEABLE"
><I
>group_role</I
></TT
> TO <TT
CLASS="REPLACEABLE"
><I
>role1</I
></TT
>, ... ;
REVOKE <TT
CLASS="REPLACEABLE"
><I
>group_role</I
></TT
> FROM <TT
CLASS="REPLACEABLE"
><I
>role1</I
></TT
>, ... ;</PRE
><P>
   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
   <TT
CLASS="LITERAL"
>PUBLIC</TT
>.
  </P
><P
>   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
   <A
HREF="sql-set-role.html"
>SET ROLE</A
> to
   temporarily <SPAN
CLASS="QUOTE"
>"become"</SPAN
> 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 <TT
CLASS="LITERAL"
>INHERIT</TT
> 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:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;</PRE
><P>
   Immediately after connecting as role <TT
CLASS="LITERAL"
>joe</TT
>, a database
   session will have use of privileges granted directly to <TT
CLASS="LITERAL"
>joe</TT
>
   plus any privileges granted to <TT
CLASS="LITERAL"
>admin</TT
>, because <TT
CLASS="LITERAL"
>joe</TT
>
   <SPAN
CLASS="QUOTE"
>"inherits"</SPAN
> <TT
CLASS="LITERAL"
>admin</TT
>'s privileges.  However, privileges
   granted to <TT
CLASS="LITERAL"
>wheel</TT
> are not available, because even though
   <TT
CLASS="LITERAL"
>joe</TT
> is indirectly a member of <TT
CLASS="LITERAL"
>wheel</TT
>, the
   membership is via <TT
CLASS="LITERAL"
>admin</TT
> which has the <TT
CLASS="LITERAL"
>NOINHERIT</TT
>
   attribute.  After:
</P><PRE
CLASS="PROGRAMLISTING"
>SET ROLE admin;</PRE
><P>
   the session would have use of only those privileges granted to
   <TT
CLASS="LITERAL"
>admin</TT
>, and not those granted to <TT
CLASS="LITERAL"
>joe</TT
>.  After:
</P><PRE
CLASS="PROGRAMLISTING"
>SET ROLE wheel;</PRE
><P>
   the session would have use of only those privileges granted to
   <TT
CLASS="LITERAL"
>wheel</TT
>, and not those granted to either <TT
CLASS="LITERAL"
>joe</TT
>
   or <TT
CLASS="LITERAL"
>admin</TT
>.  The original privilege state can be restored
   with any of:
</P><PRE
CLASS="PROGRAMLISTING"
>SET ROLE joe;
SET ROLE NONE;
RESET ROLE;</PRE
><P>
  </P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>    The <TT
CLASS="COMMAND"
>SET ROLE</TT
> 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
    <TT
CLASS="LITERAL"
>admin</TT
> before becoming <TT
CLASS="LITERAL"
>wheel</TT
>.
   </P
></BLOCKQUOTE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>    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 <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> by giving
    roles being used as SQL roles the <TT
CLASS="LITERAL"
>INHERIT</TT
> attribute, while
    giving roles being used as SQL users the <TT
CLASS="LITERAL"
>NOINHERIT</TT
> attribute.
    However, <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> defaults to giving all roles
    the <TT
CLASS="LITERAL"
>INHERIT</TT
> attribute, for backward compatibility with pre-8.1
    releases in which users always had use of permissions granted to groups
    they were members of.
   </P
></BLOCKQUOTE
></DIV
><P
>   The role attributes <TT
CLASS="LITERAL"
>LOGIN</TT
>, <TT
CLASS="LITERAL"
>SUPERUSER</TT
>,
   <TT
CLASS="LITERAL"
>CREATEDB</TT
>, and <TT
CLASS="LITERAL"
>CREATEROLE</TT
> can be thought of as
   special privileges, but they are never inherited as ordinary privileges
   on database objects are.  You must actually <TT
CLASS="COMMAND"
>SET ROLE</TT
> 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 <TT
CLASS="LITERAL"
>CREATEDB</TT
> and <TT
CLASS="LITERAL"
>CREATEROLE</TT
> to the
   <TT
CLASS="LITERAL"
>admin</TT
> role.  Then a session connecting as role <TT
CLASS="LITERAL"
>joe</TT
>
   would not have these privileges immediately, only after doing
   <TT
CLASS="COMMAND"
>SET ROLE admin</TT
>.
  </P
><P
>  </P
><P
>   To destroy a group role, use <A
HREF="sql-droprole.html"
>DROP ROLE</A
>:
</P><PRE
CLASS="SYNOPSIS"
>DROP ROLE <TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
>;</PRE
><P>
   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.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="role-attributes.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="perm-functions.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Role Attributes</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user-manag.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Function and Trigger Security</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>