The Book Town company is divided into three main departments where employees who need access to the same types of programs are grouped together. The same thinking applies to databases. Users are grouped together to have access to the same objects in a database. To group users together, create the group and then add users to it:

    CREATE GROUP groupname 
      WITH USER username1, username2,...;

This creates the accounting group and add users Jennifer and Jonathan into the group:

Example 8-5. Creating a Group

    CREATE GROUP accounting
      WITH USER jennifer, jonathan;

Note Note

You must create the user before using the CREATE GROUP .. WITH USER .. command. You could create a group without specifying a user and then adding a user later in the implementation. If a group already exists, you can use ALTER GROUP to add a user.

The syntax for adding a person or user into an existing group is:

    ALTER  GROUP groupname 
    ADD USER username1, username2,...;

For instance, the Book Town store hired a new temporary employee named Julene to help the Sales department during the summer time. He needs to have the same access permissions that the Sales group has. To add her to the group, use:

Example 8-6. Modifying a Group

   ALTER GROUP sales ADD USER julene;


Alternatively, you can remove a person(s) or user(s) from a group by using:

     ALTER GROUP groupname 
     DROP USER username1, username2,...;

Once the summer ends, Julene's employment at Book Town is also over, so you should delete her user from the sales group with the following command:

   ALTER GROUP sales DROP USER julene;


Tip Tip

To view a list of groups and their attributes, use the following query:

Example 8-7. Viewing group attributes

   SELECT * FROM pg_group;

This also displays the user identification numbers that belong to each group.

To remove a group of users, use:

Example 8-8. Dropping a Group - Syntax

   DROP GROUP groupname;

Help us make a better book, leave feedback. (