[Authorization] How to filter records by membership in tree structures

[L-U-C-K-Y]

Hi all

We are trying to integrate Hasura with our infrastructure and wondering how we could achieve our main permission logic.

• We use RBAC, which fits the Hasura authorization model
• There is tree-like structure as in GitLab, where a tenant has groups and teams (groups can have subgroups and teams)
• Users have different role permission on groups and teams (membership), which are inherited when assigned to a group.
• We have 2 tables: tree & tree_membership, whereas:
  • tree stores the structure tree and the path as ltree
  • tree_membership stores the user_id and role within the tree

Structure tree:

- tenant
- - group
- - - - team
- - - - team
- - group
- - - - team
- - - - group
- - - - - - - - team
- - - - - - - - team

Structure tree with roles:

- tenant
- - group (role: admin)
- - - - team (role: admin - inherited from group)
- - - - team (role: admin - inherited from group)
- - group
- - - - team  (role: write)
- - - - group
- - - - - - - - team (role: write)
- - - - - - - - team

We are trying now to implement this with Hasura permissions, does anyone have an idea?

Reference screen from the docs:

[poita66]

You'll need to flatten that data into another table or with a view. It's not a great answer, but it's really the only option when working with trees in SQL