20.3. Role Membership
It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. InPostgres Pro this is done by creating a role that represents the group, and then grantingmembership in the group role to individual user roles.
To set up a group role, first create the role:
CREATE ROLEname; Typically a role being used as a group would not have theLOGIN attribute, though you can set it if you wish.
Once the group role exists, you can add and remove members using theGRANT andREVOKE commands:
GRANTgroup_roleTOrole1, ... ;REVOKEgroup_roleFROMrole1, ... ;
You can grant membership to other group roles, too (since there isn't really any distinction between group roles and non-group roles). The database will not let you set up circular membership loops. Also, it is not permitted to grant membership in a role toPUBLIC.
The members of a group role can use the privileges of the role in two ways. First, every member of a group can explicitly doSET ROLE to temporarily“become” the group role. In this state, the database session has access to the privileges of the group role rather than the original login role, and any database objects created are considered owned by the group role not the login role. Second, member roles that have theINHERIT attribute automatically have use of the privileges of roles of which they are members, including any privileges inherited by those roles. As an example, suppose we have done:
CREATE ROLE joe LOGIN INHERIT;CREATE ROLE admin NOINHERIT;CREATE ROLE wheel NOINHERIT;GRANT admin TO joe;GRANT wheel TO admin;
Immediately after connecting as rolejoe, a database session will have use of privileges granted directly tojoe plus any privileges granted toadmin, becausejoe“inherits”admin's privileges. However, privileges granted towheel are not available, because even thoughjoe is indirectly a member ofwheel, the membership is viaadmin which has theNOINHERIT attribute. After:
SET ROLE admin;
the session would have use of only those privileges granted toadmin, and not those granted tojoe. After:
SET ROLE wheel;
the session would have use of only those privileges granted towheel, and not those granted to eitherjoe oradmin. The original privilege state can be restored with any of:
SET ROLE joe;SET ROLE NONE;RESET ROLE;
Note
TheSET ROLE command always allows selecting any role that the original login role is directly or indirectly a member of. Thus, in the above example, it is not necessary to becomeadmin before becomingwheel.
Note
In the SQL standard, there is a clear distinction between users and roles, and users do not automatically inherit privileges while roles do. This behavior can be obtained inPostgres Pro by giving roles being used as SQL roles theINHERIT attribute, while giving roles being used as SQL users theNOINHERIT attribute. However,Postgres Pro defaults to giving all roles theINHERIT attribute, for backward compatibility with pre-8.1 releases in which users always had use of permissions granted to groups they were members of.
The role attributesLOGIN,SUPERUSER,CREATEDB, andCREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actuallySET ROLE to a specific role having one of these attributes in order to make use of the attribute. Continuing the above example, we might choose to grantCREATEDB andCREATEROLE to theadmin role. Then a session connecting as rolejoe would not have these privileges immediately, only after doingSET ROLE admin.
To destroy a group role, useDROP ROLE:
DROP ROLEname;Any memberships in the group role are automatically revoked (but the member roles are not otherwise affected).