Joining two ltree based tree tables recursively

I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join them to each other – and bring in all the relations – in a reasonably efficient way?

In this system, the ancestry determines the capabilities of a role or permission. A role below building_access will inherit the rights and permissions of its parent. I can quite easily query roles or permissions and determine the entire line of ancestry for a given record.

What I am trying to do is query a role and determine the complete tree of permissions for that role. I am also trying to query all roles and determine the same list of permissions, for each and every role, aggregating it into a string.

SELECT r.name AS "role", CONCAT('["', string_agg(p.name, '", "' ORDER BY p.id), '"]') AS "permissions"
FROM roles r
JOIN roles_permissions rp ON rp.role = r.id
JOIN permissions p ON p.id = rp.permission
WHERE r.path @> 'company_employee.warehouse_employee'
GROUP BY r.id;

Actual Results:
---------------------+-------------------------------------------------
        role         |                   permissions
---------------------+-------------------------------------------------
 company employee    | ["building access", "break room access"]
 warehouse employee  | ["warehouse access", "warehouse stock access"]

Intended Results:
---------------------+-------------------------------------------------
        role         |                   permissions
---------------------+-------------------------------------------------
 company employee    | ["building access", "break room access"]
 warehouse employee  | ["building access", "break room access", "warehouse access", "warehouse stock access"]

This query functions to give me the direct permissions for every role, but it doesn't get the ancestors of those permissions to establish the actual total permissio

Inner join returns all ancestors of an specific path, I've used array_agg(distinc to avoid duplicates.

select
            array_agg(distinct p2.name) permissions
        from
            permissions p1
        join
            permissions p2
            on  p2.path @> p1.path

Then I've used it for each role that match the path company_employee.warehouse_employee

select r.name,
       (select array_agg(distinct p2.name) permissions
        from   permissions p1
        join   permissions p2 on  p2.path @> p1.path
        join   roles_permissions rp on rp.permission = p1.id
        where  rp.role in (select distinct r2.id
                           from   roles r1
                           join   roles r2 on r2.path @> r1.path
                           where  r1.id = r.id)) permissions
from   roles r
where  r.path @> 'company_employee.warehouse_employee';

| "company employee"   | "{"building access","break room access"}"                         |
|----------------------|-------------------------------------------------------------------|
| "warehouse employee" | "{"building access","break room access","warehouse access","warehouse stock access"}" |

rextester here

As Evan Carroll has pointed out

• If a role X.Y.Z inherits permissions from X.Y and hence from X

• And a permission A.B.C inherits from A.B and from A

Your bridge table should avoid to assign those rows twice.

Given next data:

ID | Roles    ID | Perm  
---+-----     ---+------ 
1  | X        1  | A     
2  | X.Y      2  | A.B   
3  | X.Y.Z    3  | A.C   
              4  | A.C.D 
              5  | A.C.E

Your bridge table joins (1,1) and (1,2):

X => A             A.B ~> A

In this case (1,1) is redundant because A permission is given by inheritance on A.B

And same for (2,3), (2,4):

Inherits from role
X.Y => A.C                 ~> X ~> A.B ~> A      A.C.D ~> A.C ~> A   ~> X ~> A.B ~> A

StackExchange Database Administrators Q#225865, answer score: 3