PostgreSQL Role Inheritance in Reverse: Discovering Descendant Roles in Reverse Gear

October 4, 2023

Author

Sagar Jadhav

Insight for DBAs

PostgreSQL

Share this Post:

In our previous blog post, PostgreSQL Role Inheritance at a Glance, we delved into the concept of role inheritance in PostgreSQL. We discussed how roles can inherit permissions from other roles, simplifying access control in your database. But what if you need to discover which roles inherit from a specific role? That’s where our new function, “role_inheritance_reverse,” comes into play.

Introducing function role_inheritance_reverse

The role_inheritance_reverse function can be a powerful SQL for PostgreSQL administrators and security experts. It allows you to navigate the role hierarchy in reverse, starting from a specified role and tracing all the descendant roles that inherit permissions from it, directly or indirectly.

Here’s a quick overview of the function

CREATE OR REPLACE FUNCTION role_inheritance_reverse(username character varying)<br>RETURNS TABLE(username character varying, parent_role character varying, depth integer, inherit_path text)<br>LANGUAGE plpgsql<br>AS $$<br>BEGIN<br>  RETURN QUERY<br>  WITH RECURSIVE cte AS (<br>    SELECT member, roleid as child, 1 as d, ''::name as path FROM pg_auth_members WHERE pg_get_userbyid(roleid) = usrname<br>    UNION ALL<br>    SELECT m.roleid, m.member as member_of, d + 1, path || '<-' || pg_get_userbyid(cte.child) as path<br>    FROM cte<br>    JOIN pg_auth_members m ON m.roleid = cte.child WHERE d < 20<br>  )<br>  SELECT distinct pg_get_userbyid(child)::varchar as username,<br>                  pg_get_userbyid(child)::varchar as parent_role,<br>                  d::int as depth,<br>                  substr(path::text || '<-' || pg_get_userbyid(child), 3) as path<br>  FROM cte<br>  ORDER BY 3;<br>END;<br>$$;<br>

1

CREATE OR REPLACE FUNCTION role_inheritance_reverse(username character varying) RETURNS TABLE(username character varying, parent_role character varying, depth integer, inherit_path text) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY WITH RECURSIVE cte AS ( SELECT member, roleid as child, 1 as d, ''::name as path FROM pg_auth_members WHERE pg_get_userbyid(roleid) = usrname UNION ALL SELECT m.roleid, m.member as member_of, d + 1, path || '<-' || pg_get_userbyid(cte.child) as path FROM cte JOIN pg_auth_members m ON m.roleid = cte.child WHERE d < 20 ) SELECT distinct pg_get_userbyid(child)::varchar as username, pg_get_userbyid(child)::varchar as parent_role, d::int as depth, substr(path::text || '<-' || pg_get_userbyid(child), 3) as path FROM cte ORDER BY 3; END; $$;

How does role_inheritance_reverse work?

The role_inheritance_reverse function starts with a specified role (given as username) and then explores the role hierarchy backward. Here’s how it works:

Input Parameter: The function takes a single input parameter, username, which is the role you want to start from.

Recursive Query: The magic happens inside a common table expression (CTE) with a recursive query. Initially, it selects the direct child roles of the specified role.

Recursive Step: The recursive part of the query identifies roles that inherit permissions from other roles, effectively tracing the hierarchy upward.

Result: The query returns a table with columns for the child role, parent role, depth (level in the hierarchy), and the inheritance path.

Ordering: The results are ordered by depth, providing a clear view of the inheritance structure.

To understand this better, let’s revisit the scenario we discussed in the previous blog post – PostgreSQL Role Inheritance at a Glance. Imagine that we have several roles within the database, as outlined below:

postgres=# du<br>                                   List of roles<br> Role name |                         Attributes                     | Member of<br>-----------+--------------------------------------------------------+----------<br> A         |                                                        | {B}<br> B         | Cannot login                                           | {E,D}<br> C         |                                                        | {E,D,B}<br> D         | Cannot login                                           | {}<br> E         | Cannot login                                           | {}<br> postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

1

postgres=# du List of roles Role name | Attributes | Member of -----------+--------------------------------------------------------+---------- A | | {B} B | Cannot login | {E,D} C | | {E,D,B} D | Cannot login | {} E | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

As illustrated above, role D plays the role of a parent to both B and C, while B takes on the role of a parent to C and A. As a result, both A and C directly inherit permissions from B and indirectly from D. In simpler language, we can view A and C as indirect descendants of D within the role hierarchy. While recognizing this inheritance pattern is relatively straightforward when dealing with a small number of roles, it can become considerably more complex as the number of roles grows. The role_inheritance_reverse function simplifies the task of identifying role inheritance, even in more extensive role hierarchies.

Let’s execute the function for a role “D”:

postgres=# SELECT * FROM role_inheritance_reverse('D');<br> username | parent_role | depth | inherit_path<br>----------+-------------+-------+--------------<br> D        | D           |     1 | D<br> B        | B           |     2 | D<-B<br> C        | C           |     2 | D<-C<br> A        | A           |     3 | D<-B<-A<br> C        | C           |     3 | D<-B<-C<br>(5 rows)

1	postgres=# SELECT * FROM role_inheritance_reverse('D');<br> username \| parent_role \| depth \| inherit_path<br>----------+-------------+-------+--------------<br> D \| D \| 1 \| D<br> B \| B \| 2 \| D<-B<br> C \| C \| 2 \| D<-C<br> A \| A \| 3 \| D<-B<-A<br> C \| C \| 3 \| D<-B<-C<br>(5 rows)

Practical use cases

Understanding role inheritance can be incredibly useful in various scenarios:

Security audits: Determine which roles inherit permissions from sensitive roles, helping you assess potential security risks.

Access control: Tailor access permissions for child roles based on their inheritance, ensuring a granular and secure access control strategy.

Troubleshooting: Resolve access-related issues by identifying how roles inherit permissions, enabling you to pinpoint and rectify access problems.

Documentation: Document your role hierarchy in reverse for reference, compliance, and auditing purposes.

Conclusion

The role_inheritance_reverse function is a valuable addition to your PostgreSQL utility queries. It empowers you to explore role inheritance in reverse, uncovering all the roles that inherit from a specific role.

So, whether you’re conducting a security audit, fine-tuning access control, or simply documenting your role hierarchy, the role_inheritance and role_inheritance_reverse functions are here to make your PostgreSQL role management more efficient and transparent.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!