<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title> Practical PostgreSQL</title>
<meta name="keywords" content="postgresql, support, recovery, design, software, hosting, expterise, programming, postgres, pgsql, replication, mysql, firebird, database, sql, free, open source, software" />
<meta name="description" content="Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. " />
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="../css/layout.css" />
<link rel="stylesheet" type="text/css" href="../css/cmd4.css" />
<script language="javascript" type="text/javascript"><!--
greymenuhomewideLit = new Image();
greymenuhomewideLit.src = "/images/greymenu_homewide_lit.gif";
greymenuaboutLit = new Image();
greymenuaboutLit.src = "/images/greymenu_about_lit.gif";
greymenuproductsLit = new Image();
greymenuproductsLit.src = "/images/greymenu_products_lit.gif";
greymenuservicesLit = new Image();
greymenuservicesLit.src = "/images/greymenu_services_lit.gif";
greymenusupportLit = new Image();
greymenusupportLit.src = "/images/greymenu_support_lit.gif";
greymenucommunityLit = new Image();
greymenucommunityLit.src = "/images/greymenu_community_lit.gif";
//--></script>
</head>
<body>
<div align="center">
<div id="widecontainer">
<div id="widetop">
<div class="throwleft" style="width: 300px; text-align: left;"><a href="http://www.commandprompt.com/"><img src="../images/cmd4logo_wide.gif" border="0" alt="Command Prompt, Inc." /></a></div>
<div class="throwright" style="width: 400px; text-align: right;">
<div id="topright">
<form action="index.html" name="cmdform" method="post">
<ul>
<li><input type="text" name="search" value="" /></li>
<li><input type="submit" name="action" value="Search Book" /></li>
</ul>
</form>
</div>
</div>
</div>
<ul id="greymenu_wide">
<li><a href="http://www.commandprompt.com/home/"><img onmouseover="this.src = greymenuhomewideLit.src;" onmouseout="this.src = '/images/greymenu_homewide.gif';" src="../images/greymenu_homewide.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/about/"><img onmouseover="this.src = greymenuaboutLit.src;" onmouseout="this.src = '/images/greymenu_about.gif';" src="../images/greymenu_about.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/products/"><img onmouseover="this.src = greymenuproductsLit.src;" onmouseout="this.src = '/images/greymenu_products.gif';" src="../images/greymenu_products.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/services/"><img onmouseover="this.src = greymenuservicesLit.src;" onmouseout="this.src = '/images/greymenu_services.gif';" src="../images/greymenu_services.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/support/"><img onmouseover="this.src = greymenusupportLit.src;" onmouseout="this.src = '/images/greymenu_support.gif';" src="../images/greymenu_support.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/community/"><img onmouseover="this.src = greymenucommunityLit.src;" onmouseout="this.src = '/images/greymenu_community.gif';" src="../images/greymenu_community.gif" border="0" height="22" title="" alt="" /></a></li> </ul>
<div style="padding-left: 16px; width: 96%;">
<HTML
><HEAD
><TITLE
>Managing Groups</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.72
"><LINK
REL="HOME"
TITLE="Practical PostgreSQL"
href="book1"><LINK
REL="UP"
TITLE="User and Group Management"
href="c18591"><LINK
REL="PREVIOUS"
TITLE="User and Group Management"
href="c18591"><LINK
REL="NEXT"
TITLE="Granting Privileges"
href="x19270"></HEAD
><BODY
CLASS="SECT1"
BGCOLOR="#FFFFFF"
TEXT="#000000"
LINK="#0000FF"
VLINK="#840084"
ALINK="#0000FF"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>Practical PostgreSQL</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
href="c18591"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
>Chapter 10. User and Group Management</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
href="x19270"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="AEN19123"
>Managing Groups</A
></H1
><P
>Groups serve to simplify the assignment of rights. Ordinary privileges must be granted to a
single user, one at a time. This can be tedious if several users need to be assigned the same access to a variety of
database objects.</P
><P
>Groups are created to avoid this problem. A group simply requires a name, and can be created empty (without users).
Once created, users who are intended to share common access
privileges are added into the group together, and are henceforth associated by their membership in that group.
Rights on database objects are then granted to the <SPAN
><I
CLASS="EMPHASIS"
>group</I
></SPAN
>, rather than to each
<SPAN
><I
CLASS="EMPHASIS"
>member</I
></SPAN
> of the group. For a system with many users and databases, groups make managing rights
less of an administrative chore.</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>Users may belong to any number of groups, or no groups at all.</P
><A
NAME="AEN19134"
></A
><BLOCKQUOTE
CLASS="BLOCKQUOTE"
><P
></P
></BLOCKQUOTE
></BLOCKQUOTE
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="CREATINGGROUPS"
>Creating and Removing Groups</A
></H2
><P
>Before you get started managing groups, you should first understand how to create and remove them from the system.
Each of these procedures requires superuser privileges. See <A
href="c18591#MANAGINGUSERS"
>the Section called <I
>Managing Users</I
></A
>" earlier in this chapter for more
about superusers.</P
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="CREATINGAGROUP"
>Creating a group</A
></H3
><P
>Any superuser may create a new group in PostgreSQL with the <TT
CLASS="LITERAL"
>CREATE GROUP</TT
>
command. Here is the syntax for <TT
CLASS="LITERAL"
>CREATE GROUP</TT
>:</P
><PRE
CLASS="SCREEN"
> CREATE GROUP <TT
CLASS="REPLACEABLE"
><I
>groupname</I
></TT
>
[ WITH
[ SYSID <TT
CLASS="REPLACEABLE"
><I
>groupid</I
></TT
> ]
[ USER <TT
CLASS="REPLACEABLE"
><I
>username</I
></TT
> [, ...] ] ]</PRE
><P
>In this syntax, <TT
CLASS="REPLACEABLE"
><I
>groupname</I
></TT
> is the name of the group that you wish to create. A group's name
must start with an alphabetical character, and may not exceed 31 characters in length.
Providing the <TT
CLASS="LITERAL"
>WITH</TT
> keyword allows for either of the optional attributes to be specified.
If you wish to specify the system ID to use for the new group, use the SYSID keyword to specify the <TT
CLASS="REPLACEABLE"
><I
>groupid</I
></TT
>
value. Use the <TT
CLASS="LITERAL"
>USER</TT
> keyword to include one or more users to the group at creation time. Separate usernames by commas.</P
><P
> Additionally, the PostgreSQL user and group tables operate separately from each other. This separation does allow
a user's <TT
CLASS="LITERAL"
>usesysid</TT
> and a group's <TT
CLASS="LITERAL"
>grosysid</TT
>
to be identical within the PostgreSQL system.</P
><P
>As an example, <A
href="x19123#CREATINGAGROUPEXAMPLE"
>Example 10-11</A
> creates the <TT
CLASS="LITERAL"
>sales</TT
> group, and
adds two users to it upon its creation. These users are <TT
CLASS="LITERAL"
>allen</TT
>, and
<TT
CLASS="LITERAL"
>vincent</TT
> (presumably, members of Book Town's sales department).</P
><DIV
CLASS="EXAMPLE"
><A
NAME="CREATINGAGROUPEXAMPLE"
></A
><P
><B
>Example 10-11. Creating a group</B
></P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>CREATE GROUP sales</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> WITH USER allen, vincent;</B
></TT
>
CREATE GROUP</PRE
></DIV
><P
>The <TT
CLASS="LITERAL"
>CREATE GROUP</TT
> server message indicates that the group was created successfully.
You may verify the creation of a group, as well as view all existing groups, with a query on the
<TT
CLASS="LITERAL"
>pg_group</TT
> system table. <A
href="x19123#VERIFYINGAGROUP"
>Example 10-12</A
> executes such a query.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="VERIFYINGAGROUP"
></A
><P
><B
>Example 10-12. Verifying a group</B
></P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>SELECT * FROM pg_group;</B
></TT
>
groname | grosysid | grolist
------------+----------+-------------
sales | 1 | {7017,7016}
accounting | 2 |
marketing | 3 |
(3 rows)</PRE
></DIV
><P
>Notice that the <TT
CLASS="LITERAL"
>grolist</TT
> column is an array, containing the PostgreSQL user ID of
each user in the group. These are the same user IDs which can be seen in the <TT
CLASS="LITERAL"
>pg_user</TT
>
view. For example:</P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>SELECT usename FROM pg_user</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> WHERE usesysid = 7017 OR usesysid = 7016;</B
></TT
>
usename
---------
allen
vincent
(2 rows)</PRE
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN19181"
>Removing a group</A
></H3
><P
>Any superuser may also remove a group with the <TT
CLASS="LITERAL"
>DROP GROUP</TT
> SQL command. You should
exercise caution with this command, as it is irreversible, and you will not be prompted to verify the removal of the
group (even if there are users still in the group). Unlike <TT
CLASS="LITERAL"
>DROP DATABASE</TT
>, <TT
CLASS="LITERAL"
>DROP GROUP</TT
>
may be performed within a transaction block.</P
><P
>Here is the syntax for <TT
CLASS="LITERAL"
>DROP GROUP</TT
>:</P
><PRE
CLASS="SCREEN"
> DROP GROUP <TT
CLASS="REPLACEABLE"
><I
>groupname</I
></TT
></PRE
><P
>The <TT
CLASS="REPLACEABLE"
><I
>groupname</I
></TT
> is the name of the group to be permanently removed. <A
href="x19123#REMOVINGAGROUP"
>Example 10-13</A
> removes an outdated <TT
CLASS="LITERAL"
>marketing</TT
> group from the Book Town
database.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="REMOVINGAGROUP"
></A
><P
><B
>Example 10-13. Removing a group</B
></P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>DROP GROUP marketing;</B
></TT
>
DROP GROUP</PRE
></DIV
><P
>The <TT
CLASS="LITERAL"
>DROP GROUP</TT
> server message returned from <A
href="x19123#REMOVINGAGROUP"
>Example 10-13</A
>
indicates that the group was successfully destroyed. Note that removing a group does not remove
permissions placed on it, but rather "disembodies" them. Any permissions placed on a database object which have rights assigned to a dropped
group will appear to be assigned to a group <SPAN
><I
CLASS="EMPHASIS"
>system ID</I
></SPAN
>, rather than to a group.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>Inadvertently dropped groups can be restored to their previous functionality by creating a new group with the
same system ID as the dropped group. This involves the <TT
CLASS="LITERAL"
>SYSID</TT
> keyword, as documented
in <A
href="x19123#CREATINGAGROUP"
>the Section called <I
>Creating a group</I
></A
>." If you assign group permissions to a table and then drop the
group, the group permissions on the table will be retained. However, you will need to add the appropriate users to
the newly recreated group for the table permissions to be effective for members of that group.
</P
></BLOCKQUOTE
></DIV
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="ADDINGUSERSTOGROUPS"
>Associating Users with Groups</A
></H2
><P
>Users are both added and removed from groups in PostgreSQL through the <TT
CLASS="LITERAL"
>ALTER GROUP</TT
>
SQL command. Here is the syntax for the <TT
CLASS="LITERAL"
>ALTER GROUP</TT
> command:</P
><PRE
CLASS="SCREEN"
> ALTER GROUP <TT
CLASS="REPLACEABLE"
><I
>groupname</I
></TT
> { ADD | DROP } USER <TT
CLASS="REPLACEABLE"
><I
>username</I
></TT
> [, ... ]</PRE
><P
>The <TT
CLASS="REPLACEABLE"
><I
>groupname</I
></TT
> is the name of the group to be modified, while the <TT
CLASS="REPLACEABLE"
><I
>username</I
></TT
> is
the name of the user to be added or removed, depending on whether the <TT
CLASS="LITERAL"
>ADD</TT
>
or <TT
CLASS="LITERAL"
>DROP</TT
> keyword is specified.</P
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN19220"
>Adding a user to a group</A
></H3
><P
>Suppose that Booktown hires two new sales associates, David and Ben, and gives them usernames <TT
CLASS="LITERAL"
>david</TT
> and <TT
CLASS="LITERAL"
>ben</TT
>, respectively.
<A
href="x19123#ADDINGAUSERTOAGROUP"
>Example 10-14</A
> uses the <TT
CLASS="LITERAL"
>ALTER GROUP</TT
> command adds these new users to
the <TT
CLASS="LITERAL"
>sales</TT
> group.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="ADDINGAUSERTOAGROUP"
></A
><P
><B
>Example 10-14. Adding a user to a group</B
></P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>ALTER GROUP sales ADD USER david, ben;</B
></TT
>
ALTER GROUP</PRE
></DIV
><P
>The <TT
CLASS="LITERAL"
>ALTER GROUP</TT
> server message returned in <A
href="x19123#ADDINGAUSERTOAGROUP"
>Example 10-14</A
>
indicates that the users <TT
CLASS="LITERAL"
>david</TT
> and <TT
CLASS="LITERAL"
>ben</TT
> were successfully
added to the <TT
CLASS="LITERAL"
>sales</TT
> group. <A
href="x19123#VERIFYINGUSERADDITION"
>Example 10-15</A
> demonstrates another
query to the <TT
CLASS="LITERAL"
>pg_ group</TT
> table to verify the addition of those new users to the group. Note
that there are now four system IDs in the
<TT
CLASS="LITERAL"
>grolist</TT
> column for the sales group.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="VERIFYINGUSERADDITION"
></A
><P
><B
>Example 10-15. Verifying user addition</B
></P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>SELECT * FROM pg_group WHERE groname = 'sales';</B
></TT
>
groname | grosysid | grolist
---------+----------+-----------------------
sales | 1 | {7019,7018,7017,7016}
(1 row)</PRE
></DIV
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN19245"
>Removing a user from a group</A
></H3
><P
>Suppose that some time later David is transferred from sales to accounting. In order to maintain the correct group
association, and to make sure that David does not have any rights granted exclusively to the
<TT
CLASS="LITERAL"
>sales</TT
> group, his user (<TT
CLASS="LITERAL"
>david</TT
>) should be removed from
that group; <A
href="x19123#REMOVINGAUSERFROMAGROUP"
>Example 10-16</A
> achieves this.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="REMOVINGAUSERFROMAGROUP"
></A
><P
><B
>Example 10-16. Removing a user from a group</B
></P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>ALTER GROUP sales DROP USER david;</B
></TT
>
ALTER GROUP</PRE
></DIV
><P
>The <TT
CLASS="LITERAL"
>ALTER GROUP</TT
> message returned from <A
href="x19123#REMOVINGAUSERFROMAGROUP"
>Example 10-16</A
>
indicates that the <TT
CLASS="LITERAL"
>david</TT
> user was successfully removed from the
<TT
CLASS="LITERAL"
>sales</TT
> group.</P
><P
>To complete his transition to the accounting department, David must then have his user added to the
<TT
CLASS="LITERAL"
>accounting</TT
> group. The following statements use similar syntax as the statements in
<A
href="x19123#ADDINGAUSERTOAGROUP"
>Example 10-14</A
> and <A
href="x19123#VERIFYINGUSERADDITION"
>Example 10-15</A
>. The net effect is that the
<TT
CLASS="LITERAL"
>david</TT
> user is added into the <TT
CLASS="LITERAL"
>accounting</TT
> group. This means
that any special rights granted to this group will be implicitly granted to <TT
CLASS="LITERAL"
>david</TT
> for as long as he
is a member of the group.</P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>ALTER GROUP accounting ADD USER david;</B
></TT
>
ALTER GROUP
booktown=# <TT
CLASS="USERINPUT"
><B
>SELECT * FROM pg_group;</B
></TT
>
groname | grosysid | grolist
------------+----------+------------------
sales | 1 | {7016,7017,7019}
accounting | 2 | {7018}
(2 rows)</PRE
></DIV
></DIV
></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="c18591"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="book1"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
href="x19270"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>User and Group Management</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="c18591"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Granting Privileges</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
</div>
<br />
<div id="thefineprint">
<a href="http://www.commandprompt.com/products/mammothpostgresql/"><img src="../images/powered_by_mammoth.gif" alt="Powered by Mammoth PostgreSQL" title="Powered by Mammoth PostgreSQL" border="0" /></a>
<br />
Copyright © 2000-2007 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.
</div>
</div>
</div>
</body>
</html>