Today I got involved in a discussion on the General mailing list about using ROLE in Postgres.  I realized this is a topic that can get confusing in a hurry and I thought I might try to clear things up, in my mind if nothing else.  Part of the confusion arises from a historical carry over. In versions of Postgres prior to 8.1 there where USER and GROUP.  (An aside, I found this out by using this : Feature Matrix, a very handy tool. ) These where distinct and operated somewhat like their OS equivalents, in that users logged in and then could be part of a group. In 8.1+, in other words all current community supported versions, USER and GROUP have been replaced by ROLE.  The confusion arises because while the current underlying mechanism is based solely on ROLE, the USER and GROUP commands have been maintained for backwards compatibility. So when you use the SQL command CREATE USER in 8.1+ you are just creating a ROLE with the clause LOGIN  whereas CREATE GROUP is just CREATE ROLE which by default has the clause NOLOGIN. For my own personal sanity I just use the CREATE ROLE command and pretend USER and GROUP do not exist. Since we already done the rabbit hole I might as well mention the psql \d commands that apply as well. Again for historical reasons these are \du and \dg which at one time listed users and groups respectively. Now they both return the same thing, a list of the roles. The thing to remember is that unless you are working with a pre 8.1 database you are working with some variation of a ROLE.

So what does a ROLE get you? As was implied above a ROLE can have the characteristics of a user or group. For my own use I follow the pattern used by the current CREATE USER/GROUP commands where a ROLE with LOGIN set is a taken to be a user and one with NOLOGIN is equivalent to a group.  Note: This is just a convenient way of viewing things and you will not see a ROLE defined that way when you list them. Assuming that pattern then it possible to create group like ROLES and then assign user like ROLES membership in them. For instance:

‘Groups’:
CREATE ROLE accounting;
CREATE ROLE sales;
CREATE ROLE production;

‘Users’

CREATE ROLE aklaver LOGIN IN ROLE accounting,sales;
CREATE ROLE daffy_duck LOGIN  IN ROLE production;
If you want to change role membership after CREATE ROLE then you will need to use the GRANT or REVOKE commands.

Where this pays off is in object permissions. Give the ‘group’ roles permissions to their respective objects and then add or drop ‘users’ to the ‘groups’ as needed. This way you can maintain access flexibility without continually  having to change permissions on objects.

I have touched on just the bare basics, this is a subject that covers a lot of ground. To really learn it requires multiple passes through the documentation and plenty of hands on testing.