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. In EnterpriseDB
this is done by creating a role that represents the group, and then
granting membership in the group role to individual user
roles.
To set up a group role, first create the role:
CREATE ROLE name;
Typically a role being used as a group would not have the LOGIN
attribute, though you can set it if you wish.
Once the group role exists, you can add and remove members using the
GRANT and
REVOKE commands:
GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;
You can grant membership to other group roles, too (since there isn't
really any distinction between group roles and non-group roles). The
only restriction is that you can't set up circular membership loops.
The members of a role can use the privileges of the group role in two
ways. First, every member of a group can explicitly do
SET 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 the INHERIT attribute automatically have use of
privileges of roles they are members of. As an example, suppose we have
done
CREATE ROLE ron LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO ron;
GRANT wheel TO admin;
Immediately after connecting as role ron, a database
session will have use of privileges granted directly to ron
plus any privileges granted to admin, because ron
"inherits" admin's privileges. However, privileges
granted to wheel are not available, because even though
ron is indirectly a member of wheel, the
membership is via admin which has the NOINHERIT
attribute. After
SET ROLE admin;
the session would have use of only those privileges granted to
admin, and not those granted to ron. After
SET ROLE wheel;
the session would have use of only those privileges granted to
wheel, and not those granted to either ron
or admin. The original privilege state can be restored
with any of
SET ROLE ron;
SET ROLE NONE;
RESET ROLE;
Note: The SET 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 become
admin before becoming wheel.
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 in EnterpriseDB by giving
roles being used as SQL roles the INHERIT attribute, while
giving roles being used as SQL users the NOINHERIT attribute.
However, EnterpriseDB defaults to giving all roles
the INHERIT attribute, for backwards compatibility with pre-8.1
releases in which users always had use of permissions granted to groups
they were members of.
The role attributes LOGIN, SUPERUSER,
CREATEDB, and CREATEROLE can be thought of as
special privileges, but they are never inherited as ordinary privileges
on database objects are. You must actually SET ROLE to a
specific role having one of these attributes in order to make use of
the attribute. Continuing the above example, we might well choose to
grant CREATEDB and CREATEROLE to the
admin role. Then a session connecting as role ron
would not have these privileges immediately, only after doing
SET ROLE admin.
To destroy a group role, use DROP ROLE:
DROP ROLE name;
Any memberships in the group role are automatically revoked (but the
member roles are not otherwise affected). Note however that any objects
owned by the group role must first be dropped or reassigned to other
owners; and any permissions granted to the group role must be revoked.