Nested groups and RLS?

[nogalskisam]

Hi. Thanks for this package, it's great!

I'd like to gather your thoughts on implementing nested groups. While I know you don't support this yourself, I have added a parent_group_id relationship to the groups.id column in order to add parent/children groups. The idea, at least for my application, is that a tenant manager will manage children groups and the assignment of users in those groups - and will manage/own any data within those groups.

Where I'm struggling is how I should tackle these when it comes to row level security. From what I can see, I have two options:

1. Make top-level group admins members of any child groups they create, so that those groups are added to their raw_app_meta_data and thus their token, giving them access to the child groups
2. Amend the RLS Policies so that recursive checks happen to see if the user is the admin of any parent groups in the hierarchy. This clearly carries potential issues with performance, even when taking indexes into account.

What are your thoughts on this predicament with regards to this specific extension's implementation?

[point-source]

If you choose option 1, is there any reason you need the parent group system at all? Assuming a tenant manager can create groups and they instantly become an admin of all groups they make, do they even need to be part of a special manager group?

If all you are trying to accomplish with the manager group is to restrict those users who are allowed to create groups, you can actually already do this with this library without creating nested groups. Let me know if that would be sufficient and if so, I can create an example for you.

[nogalskisam]

The main challenge I have is with assets and files belonging to the top-level group and then being distributed among child groups. In my application, you can think of it as a parent company and then staff and customers are distributed among individual stores that belong to the company. They might share the same stock, but you want to understand which store is selling it, for example.

What I've done is add parent_group_id and group_type (tenant, organisation) columns to the group. A tenant is the top level and organisation is any level of nesting below that, although I am happy to create tables to organise that data better if there's anything below the organisation level.

Because a user can in theory belong to many tenants and organisations, I plan on adding a selectedGroups object to the app_meta_data token that stores the hierarchical group ids, but wanted to see what you suggested first: { ... "selectedGroups": [{ "tenant": <tenantid> }, { "organisation": <organisationid> }] }

I also had a problem with admins administering their child groups, but I've just made them an admin of any group they make and added the following clauses to any of our RLS policies for our master admin group: OR user_has_group_role(<admin_group_id>, 'admin'::text)