Joining two ltree based tree tables recursively
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 @&gt; 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 @&gt; 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 @&gt; r1.path
                           where  r1.id = r.id)) permissions
from   roles r
where  r.path @&gt; '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 =&gt; A            &lt;-- Redundant
X =&gt; A.B ~&gt; 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 =&gt; A.C                 ~&gt; X ~&gt; A.B ~&gt; A     &lt;-- Redundant
X.Y =&gt; A.C.D ~&gt; A.C ~&gt; A   ~&gt; X ~&gt; A.B ~&gt; A