7

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

 1 year ago
source link: https://www.percona.com/blog/postgresql-role-inheritance-in-reverse-discovering-descendant-roles-in-reverse-gear/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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

October 4, 2023

Sagar Jadhav

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

SQL function - role_inheritance_reverse
PgSQL
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
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;

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:

  1. Input Parameter: The function takes a single input parameter, username, which is the role you want to start from.
  2. 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.
  3. Recursive Step: The recursive part of the query identifies roles that inherit permissions from other roles, effectively tracing the hierarchy upward.
  4. Result: The query returns a table with columns for the child role, parent role, depth (level in the hierarchy), and the inheritance path.
  5. 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
                                   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”:

Function Execution
postgres=# SELECT * FROM role_inheritance_reverse('D');
username | parent_role | depth | inherit_path
----------+-------------+-------+--------------
D        | D           |     1 | D
B        | B           |     2 | D<-B
C        | C           |     2 | D<-C
A        | A           |     3 | D<-B<-A
C        | C           |     3 | D<-B<-C
(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!

Share This Post!

Subscribe

Connect with
guest

Label

0 Comments

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK