Role hierarchy

By granting roles to other roles, you can build a hierarchy of roles, where roles lower in the hierarchy have a narrow range of privileges, while roles higher in the hierarchy are granted combinations of roles and their privileges. When you organize roles hierarchically, any privileges that you add to lower-level roles are automatically propagated to the roles above them.

Creating hierarchical roles

The following example creates two roles, assigns them privileges, then assigns both roles to another role.

1. Create table applog:

   => CREATE TABLE applog (id int, sourceID VARCHAR(32), data TIMESTAMP, event VARCHAR(256));
   

2. Create the logreader role and grant it read-only privileges on table applog:

   => CREATE ROLE logreader;
   CREATE ROLE
   => GRANT SELECT ON applog TO logreader;
   GRANT PRIVILEGE
   

3. Create the logwriter role and grant it write privileges on table applog:

   => CREATE ROLE logwriter;
   CREATE ROLE
   => GRANT INSERT, UPDATE ON applog to logwriter;
   GRANT PRIVILEGE
   

4. Create the logadmin role and grant it DELETE privilege on table applog:

   => CREATE ROLE logadmin;
   CREATE ROLE
   => GRANT DELETE ON applog to logadmin;
   GRANT PRIVILEGE
   

5. Grant the logreader and logwriter roles to role logadmin:

   => GRANT logreader, logwriter TO logadmin;
   

6. Create user bob and grant him the logadmin role:

   => CREATE USER bob;
   CREATE USER
   => GRANT logadmin TO bob;
   GRANT PRIVILEGE
   

7. Modify user bob's account so his logadmin role is automatically enabled on login:

   
   => ALTER USER bob DEFAULT ROLE logadmin;
   ALTER USER
   => \c - bob
   You are now connected as user "bob".
   => SHOW ENABLED_ROLES;
        name      | setting
   ---------------+----------
    enabled roles | logadmin
   (1 row)
   

Enabling hierarchical roles

Only roles that are explicitly granted to a user can be enabled for that user. In the previous example, roles logreader or logwriter cannot be enabled for bob. They can only be enabled indirectly, by enabling logadmin.

Hierarchical role grants and WITH ADMIN OPTION

If one or more roles are granted to another role using WITH ADMIN OPTION, then users who are granted the 'higher' role inherit administrative access to the subordinate roles.

For example, you might modify the earlier grants of roles logreader and logwriter to logadmin as follows:

=> GRANT logreader, logwriter TO logadmin WITH ADMIN OPTION;
NOTICE 4617:  Role "logreader" was already granted to role "logadmin"
NOTICE 4617:  Role "logwriter" was already granted to role "logadmin"
GRANT ROLE

User bob , through his logadmin role, is now authorized to grant its two subordinate roles to other users—in this case, role logreader to user Alice:

=> \c - bob;
You are now connected as user "bob".
=> GRANT logreader TO Alice;
GRANT ROLE
=> \c - alice;
You are now connected as user "alice".
=> show available_roles;
      name       |  setting
-----------------+-----------
 available roles | logreader
(1 row)

=> \c - bob;
You are now connected as user "bob".
=> GRANT logadmin TO alice;
ROLLBACK 4925:  The role "logadmin" cannot be granted to "alice"