如何确定层级组织中的权限？

我正在尝试创建高性能逻辑来确定分层组织内的权限。

员工被分配到一个或多个单位。单元是分层的，（理论上）无限深度（实际上不超过 6 层）。

例如，员工Jane可能是Supervisor of the Accounts Receivable单元（的子单元）Accounting单位），还有Member of the Ethics Committee（一个孩子Committees，它本身就是Office of the CEO).

As the Supervisor of Accounts Receivable, Jane应该有权查看其他人的人事档案Accounts Receivable，但不在Ethics Committee因为她只是一个Member。同样，公司的正式员工Accounts Receivable单位不应该能够查看彼此的个人资料，尽管他们都需要获得许可才能查看公司的会计记录。

我想这个的数据库架构将如下所示：

| **employees** | **units**   | **positions** | **assignments** | **permissions** |
| ------------- | ----------- | ------------- | --------------- | --------------- |
| id            | id          | id            | employee_id     | unit_id         |
| name          | name        | title         | unit_id         | is_management   |
|               | parent_path | is_management | position_id     | ability         |

考虑到这一点，我如何编写一个高性能查询来确定哪些权限Jane已经结束Sam, an Accountant in Accounts Receivable, 与超过Bill, a Receptionist in Office of the CEO?

我最接近的是这样的：

create function permissions(actor employees, subject employees) returns setof permissions as $$
begin
  for unit_id in select unit_id from assignments where employee_id = subject.id loop
    select permissions.name
    from assignments
    left join units on (unit.id = assignments.unit_id)
    left join positions on (positions.id = assignments.position_id)
    left join permissions on (
      permissions.unit_id = units.id
      and permissions.is_management = positions.is_management
    )
    where assignments.user_id = actor.id
      and (units.parent_path = unit_id or units.parent_path @> unit_id)
  end loop;
end;
$$ language plpgsql stable;