PostgreSQL manages database access permissions using the concept of roles. A role can be either a database user or a group of database users, depending on how the role is set up. Roles can own the database objects and assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.
PostgreSQL lets you grant permissions directly to the database users. However, as a good practice for security and ease of user-account management, it is recommended that you create multiple roles with specific sets of permissions based on application and access requirements and then assign the appropriate roles to each user. Such assignment of roles can become complex if we assign a role to another role that is already a parent role of some other role.
To understand this with simple words, consider we have multiple roles inside the database as below:
1 2 3 4 5 6 7 8 9 10 | 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 you can see above, role A is a member of B and B is a member of D and E, so A is inheriting permissions from B directly and from D and E indirectly. With a small set of roles, we can quickly identify the inheritance by looking at the output of du, but it would become difficult with a large set of roles.
The below SQL query can be used to get the role inheritance/cascading:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | WITH RECURSIVE cte AS ( SELECT oid ,oid as member_of,1 as d,''::name as path FROM pg_roles r where r.rolname !~ '^pg_' UNION SELECT m.roleid,m.member as member_of ,d+1,path||'->'||pg_get_userbyid(cte.oid) as path FROM cte JOIN pg_auth_members m ON m.member = cte.oid where d<15 ) SELECT distinct pg_get_userbyid(member_of)::varchar as username, pg_get_userbyid(oid)::varchar as parent_role ,d::int as depth, substr(path::text||'->'||pg_get_userbyid(oid),3) as path FROM cte where d > 1 ORDER BY depth; username | parent_role | depth | path ----------+-------------+-------+--------- A | B | 2 | A->B B | D | 2 | B->D B | E | 2 | B->E C | B | 2 | C->B C | D | 2 | C->D C | E | 2 | C->E B | D | 3 | A->B->D B | D | 3 | C->B->D B | E | 3 | A->B->E B | E | 3 | C->B->E (10 rows) |
We can also create a function to get the inherited role details for the specific role:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE OR REPLACE FUNCTION role_inheritance(usrname 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 oid ,oid as member_of,1 as d,''::name as path FROM pg_roles r where r.rolname=usrname UNION SELECT m.roleid,m.member as member_of ,d+1,path||'->'||pg_get_userbyid(cte.oid) as path FROM cte JOIN pg_auth_members m ON m.member = cte.oid where d<15 ) SELECT distinct pg_get_userbyid(member_of)::varchar as username, pg_get_userbyid(oid)::varchar as parent_role ,d::int as depth, substr(path::text||'->'||pg_get_userbyid(oid),3) as path FROM cte WHERE d > 1 order by 3; end; $$; |
Execute the function for a specific role:
1 2 3 4 5 6 7 | postgres=# select * from role_inheritance('A'); username | parent_role | depth | inherit_path ----------+-------------+-------+-------------- A | B | 2 | A->B B | D | 3 | A->B->D B | E | 3 | A->B->E (3 rows) |
Overall, PostgreSQL roles can be used very effectively to handle permissions if we know the impact of granting them. We encourage you to try our product Percona Distribution for PostgreSQL, trusted by numerous global brands across many industries, for a unified experience to monitor, manage, secure, and optimize database environments.
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!
