Managing Groups

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.

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 group, rather than to each member of the group. For a system with many users and databases, groups make managing rights less of an administrative chore.

Note: Users may belong to any number of groups, or no groups at all.

Creating and Removing Groups

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 the Section called Managing Users" earlier in this chapter for more about superusers.

Creating a group

Any superuser may create a new group in PostgreSQL with the CREATE GROUP command. Here is the syntax for CREATE GROUP:

  CREATE GROUP groupname
         [ WITH
              [ SYSID groupid ]
              [ USER  username [, ...] ] ]

In this syntax, groupname 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 WITH 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 groupid value. Use the USER keyword to include one or more users to the group at creation time. Separate usernames by commas.

Additionally, the PostgreSQL user and group tables operate separately from each other. This separation does allow a user's usesysid and a group's grosysid to be identical within the PostgreSQL system.

As an example, Example 10-11 creates the sales group, and adds two users to it upon its creation. These users are allen, and vincent (presumably, members of Book Town's sales department).

Example 10-11. Creating a group

booktown=# CREATE GROUP sales
booktown-#              WITH USER allen, vincent;

The CREATE GROUP 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 pg_group system table. Example 10-12 executes such a query.

Example 10-12. Verifying a group

booktown=# SELECT * FROM pg_group;
  groname   | grosysid |   grolist
 sales      |        1 | {7017,7016}
 accounting |        2 |
 marketing  |        3 |
(3 rows)

Notice that the grolist 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 pg_user view. For example:

booktown=# SELECT usename FROM pg_user
booktown-#                WHERE usesysid = 7017 OR usesysid = 7016;
(2 rows)

Removing a group

Any superuser may also remove a group with the DROP GROUP 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 DROP DATABASE, DROP GROUP may be performed within a transaction block.

Here is the syntax for DROP GROUP:

  DROP GROUP groupname

The groupname is the name of the group to be permanently removed. Example 10-13 removes an outdated marketing group from the Book Town database.

Example 10-13. Removing a group

booktown=# DROP GROUP marketing;

The DROP GROUP server message returned from Example 10-13 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 system ID, rather than to a group.

Note: 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 SYSID keyword, as documented in the Section called Creating a group." 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.

Associating Users with Groups

Users are both added and removed from groups in PostgreSQL through the ALTER GROUP SQL command. Here is the syntax for the ALTER GROUP command:

  ALTER GROUP groupname { ADD | DROP } USER username [, ... ]

The groupname is the name of the group to be modified, while the username is the name of the user to be added or removed, depending on whether the ADD or DROP keyword is specified.

Adding a user to a group

Suppose that Booktown hires two new sales associates, David and Ben, and gives them usernames david and ben, respectively. Example 10-14 uses the ALTER GROUP command adds these new users to the sales group.

Example 10-14. Adding a user to a group

booktown=# ALTER GROUP sales ADD USER david, ben;

The ALTER GROUP server message returned in Example 10-14 indicates that the users david and ben were successfully added to the sales group. Example 10-15 demonstrates another query to the pg_ group table to verify the addition of those new users to the group. Note that there are now four system IDs in the grolist column for the sales group.

Example 10-15. Verifying user addition

booktown=# SELECT * FROM pg_group WHERE groname = 'sales';
 groname | grosysid |        grolist
 sales   |        1 | {7019,7018,7017,7016}
(1 row)

Removing a user from a group

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 sales group, his user (david) should be removed from that group; Example 10-16 achieves this.

Example 10-16. Removing a user from a group

booktown=# ALTER GROUP sales DROP USER david;

The ALTER GROUP message returned from Example 10-16 indicates that the david user was successfully removed from the sales group.

To complete his transition to the accounting department, David must then have his user added to the accounting group. The following statements use similar syntax as the statements in Example 10-14 and Example 10-15. The net effect is that the david user is added into the accounting group. This means that any special rights granted to this group will be implicitly granted to david for as long as he is a member of the group.

booktown=# ALTER GROUP accounting ADD USER david;
booktown=# SELECT * FROM pg_group;
  groname   | grosysid |     grolist
 sales      |        1 | {7016,7017,7019}
 accounting |        2 | {7018}
(2 rows)