Organizations

[Zyles]

Is there a solution for organizations on top of groups?

Org 1
- Group 1
- - User 1
- - User 2
- Group 2
- - User 3
- - User 4

Org 2
- Group 3
...

[nogalskisam]

I did it like this. I have an open issue and I'm not sure if this approach is the ideal approach, so some input from @point-source might be useful.

I added a parent_group_id column to groups. This allows for recursive group levels. These are what I consider 'tenants' in my application. Users would have to belong to all groups and sub-groups in order for RLS policies to work with this approach. I make users who are admins members of all groups to make this work. I also gave my groups a group_type column so that I can use this for better reporting and logic.

In order for users to exist across multiple groups and to make my application multi-tenanted, I set the raw_app_meta_data to contain a selectedTenant value and then add a tenant_id to every table where tenancy is required.

My tables look a little something like this:

public.groups (
    id uuid not null default gen_random_uuid (),
    metadata jsonb not null default '{}'::jsonb,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    name text not null,
    parent_group_id uuid null,
    group_type public.group_type not null,
    constraint group_pkey primary key (id),
    constraint groups_parent_group_id_fkey foreign key (parent_group_id) references groups (id)
  ) tablespace pg_default;

create table
  public.product (
    id uuid not null default gen_random_uuid (),
    tenant_group_id uuid not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    name text null,
    description text null,
    thumbnail text null,
    constraint courses_pk primary key (id),
    constraint courses_groups_fk foreign key (tenant_group_id) references groups (id)
  ) tablespace pg_default;

And with RLS policies that look like this:

alter policy "Only selects current group products"
on "public"."products"
to authenticated
using (
((( SELECT ((auth.jwt() -> 'app_metadata'::text) ->> 'selectedTenant'::text)))::uuid = tenant_group_id)
);

[Zyles]

I'm getting the error:

Failed to update database policy: failed to update pg.policies with the given ID: syntax error at or near ")"

For the using statement.

[Zyles]

The AI helped me with this line:

CREATE POLICY "Enable insert for authenticated users only" ON "public"."cities" TO authenticated USING (
  (auth.jwt() -> 'app_metadata' ->> 'selectedTenant')::UUID = group_id
);

But I still get an error:

new row violates row-level security policy for table

Do you still need to pass the group_id in your requests using the supabase JS client? That is what I'm trying to avoid.

[point-source]

When I was building this, I did briefly consider group hierarchies but I ultimately decided that the performance / functionality trade-off wasn't really worth it since most applications of a "parent group" are really just about sharing permissions with a larger group that just so happens to also overlap the membership of two or more smaller groups summed. This being the case, it's just as effective to use multiple flat groups in most cases since users can already belong to more than one group. Thus, the following permission models are equivalent in function:

Hierarchical Exclusive Group Membership

• Parent Group : Read all animals
• • Group 1 : Write dogs
• • Group 2 : Write cats

User A is in Group 1 and can Read all and Write dogs. User B is in Group 2 and can Read all and Write cats.

Flat Multi-group membership

• Group 1 : Write dogs
• Group 2 : Write cats
• Group 3 : Read all animals

User A is in Groups 1 & 3 and can Read all and Write dogs. User B is in Groups 2 & 3 and can Read all and Write cats.

Notice that without having a hierarchy, you still achieve the same functionality for permissions as well as for being able to modify group 3 and affect all users. So in this example, the "parent"ing of the group is merely a semantic label that has more to do with the permissions frontend interface rather than the function of the backend.

The only time I can think of when a true hierarchical model is required is when a user may have conflicting permissions as a result of being a member of more than one group. Since this library doesn't really have the explicit concept of a negating permission though, all permissions are effectively OR'd instead of AND. So there's no priority to be determined.