21.3. Role Membership

    To set up a group role, first create the role:

    Typically a role being used as a group would not have the attribute, though you can set it if you wish.

    Once the group role exists, you can add and remove members using the GRANT and commands:

    1. REVOKE group_role FROM role1, ... ;

    The members of a group role can use the privileges of the 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 the privileges of roles of which they are members, including any privileges inherited by those roles. As an example, suppose we have done:

    Immediately after connecting as role joe, a database session will have use of privileges granted directly to joe plus any privileges granted to admin, because joe “inherits” admin‘s privileges. However, privileges granted to wheel are not available, because even though joe is indirectly a member of wheel, the membership is via which has the NOINHERIT attribute. After:

    1. SET ROLE admin;

    the session would have use of only those privileges granted to admin, and not those granted to joe. After:

    1. SET ROLE NONE;
    2. 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 adminbefore becoming .

    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 PostgreSQLby giving roles being used as SQL roles the INHERIT attribute, while giving roles being used as SQL users the NOINHERIT attribute. However, PostgreSQL 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 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 choose to grant CREATEDB and CREATEROLE to the admin role. Then a session connecting as role joe would not have these privileges immediately, only after doing .

    Any memberships in the group role are automatically revoked (but the member roles are not otherwise affected).