Role Hierarchy

In addition to granting roles to users, you can also grant roles to other roles. This lets you build hierarchies of roles, with more privileged roles (an administrator, for example) being assigned all of the privileges of lesser-privileged roles (a user of a particular application), in addition to the privileges you assign to it directly. By organizing your roles this way, any privilege you add to the application role (reading or writing to a new table, for example) is automatically made available to the more-privileged administrator role.

Example

The following example creates two roles, assigns them privileges, then assigns them to a new administrative role.

  1. Create new table applog:

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

  2. Create a new role called logreader:

    => CREATE ROLE logreader;

  3. Grant the logreader role read-only access on the applog table:

    => GRANT SELECT ON applog TO logreader;

  4. Create a new role called logwriter:

    => CREATE ROLE logwriter;

  5. Grant the logwriter write access on the applog table:

    => GRANT INSERT ON applog to logwriter;

  6. Create a new role called logadmin, which will rule the other two roles:

    => CREATE ROLE logadmin;

  7. Grant the logadmin role privileges to delete data:

    => GRANT DELETE ON applog to logadmin;

  8. Grant the logadmin role privileges to have the same privileges as the logreader and logwriter roles:

    => GRANT logreader, logwriter TO logadmin;

  9. Create new user Bob:

    => CREATE USER Bob;

  10. Give Bob logadmin privileges:

    => GRANT logadmin TO Bob;

The user Bob can now enable the logadmin role, which also includes the logreader and logwriter roles. Note that Bob cannot enable either the logreader or logwriter role directly. A user can only enable explicitly-granted roles.

Hierarchical roles also works with administrative access to a role:

=> GRANT logreader, logwriter TO logadmin WITH ADMIN OPTION;
GRANT ROLE
=> GRANT logadmin TO Bob;
=> \c - bob;  -- connect as Bob
You are now connected as user "Bob".
=> SET ROLE logadmin; -- Enable logadmin role
SET
=> GRANT logreader TO Alice;
GRANT ROLE

Note that the user Bob only has administrative access to the logreader and logwriter roles through the logadmin role. He doesn't have administrative access to the logadmin role, since it wasn't granted to him with the optional WITH ADMIN OPTION argument:

=> GRANT logadmin TO Alice;
WARNING:  Some roles were not granted
GRANT ROLE

For Bob to be able to grant the logadmin role, a superuser would have had to explicitly grant him administrative access.

See Also