Clarifying the use of Groups and Roles,

First, a little backgrond history. Prior to PostgreSQL v8.1, the division between a Group and a Role was much clearer. However, because “There is no CREATE GROUP statement in the SQL”, with 8.1, CREATE GROUP became an alias for CREATE ROLE, with the option to allow (or prevent) login, and CREATE USER was changed to CREATE ROLE.

Confused yet? Don’t worry, hopefully I’m going to clear up the distinction.

In the practical world of database use, a GROUP is a collection of USERS  (ROLEs that can login. 
GROUPs normally are not given the option to login.

So to be a little more succinct:

Groups usually identify a class of users (roles) with similar functions. Consider the following example.

Since several users (roles) may all perform accounting functions, it makes sense to have an 'accounting' group (role). Likewise, managers who have extra privileges could be part of the 'officers' group (role). It is also possible for a user to belong to more than one group. However, for this example will use the simple case of just one group (role).

Creating Groups
Consider the following example.

A company has 3 different types of users: Order takers (group name orders), Administrative (group name admin) and managers (group name mgr). The list of users follows:

orders |admin  | mgr
====================
Bob	   | Carol | Dick
Alice  | Ted   | Jane
Jocelyn| Edna  |	 

According to the company rules, Order takers can only view and update the order table. 
Administrative users can view and update the employee table, and can view but not update the orders table. Managers have full access to both the orders table and the employee table.

To implement this, you would follow these steps: 
1.	Create the three groups:

CREATE ROLE orders WITH NOLOGIN;
CREATE ROLE admin  WITH NOLOGIN;
CREATE ROLE mgr    WITH NOLOGIN;

Note: It is best to use lowercase for object names in PostgreSQL, otherwise they must be enclosed in quotes 

2.	GRANT the appropriate permissions to the groups:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO orders;
GRANT SELECT ON TABLE orders TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employee TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO mgr;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employee TO mgr;

Note: I specifically did NOT use the GRANT ALL option in above. 
Although GRANT ALL, at first appears to simplify granting permissions, it is actually a very bad practice that is often misused. That is because doing so would also allow groups and ordinary users the following additional privileges: TRUNCATE, REFERENCES & TRIGGER. 
Only the table owner (usually the dba and/or postgres), should have those privileges. 

3.	Create the users and add them to the Groups..

CREATE ROLE bob     WITH INHERIT LOGIN PASSWORD 'bobspw'     IN ROLE orders;
CREATE ROLE alice   WITH INHERIT LOGIN PASSWORD 'alicespw'   IN ROLE orders;
CREATE ROLE jocelyn WITH INHERIT LOGIN PASSWORD 'jocelynspw' IN ROLE orders;
CREATE ROLE carol   WITH INHERIT LOGIN PASSWORD 'carolspw'   IN ROLE admin;
CREATE ROLE ted     WITH INHERIT LOGIN PASSWORD 'tedspw'     IN ROLE admin;
CREATE ROLE edna    WITH INHERIT LOGIN PASSWORD 'ednaspw'    IN ROLE admin;
CREATE ROLE dick    WITH INHERIT LOGIN PASSWORD 'dickspw'    IN ROLE mgr;
CREATE ROLE jane    WITH INHERIT LOGIN PASSWORD 'janespw'    IN ROLE mgr;

Note: the INHERIT option is critical, otherwise the user would have to do a “SET ROLE group_name;” to obtain the group permissions

Now, any time the users/roles connect to the database, they automatically have the correct permissions to access the tables they need. This simplifies the task of maintaining user permissions, because whenever a new user enters the company, they only have to be created in the appropriate group, and there is no need to grant user specific rights to any tables.