Authentication for different companies

How would one go about creating policies to show records only for the domain/one company? I have 3 user role types and trying to figure out. Is there a video or tutorial anyone knows of that can help with this?

garyaustin•1/14/24, 2:25 AM

I've not seen anything.
You can use the jwt app metadata claim, or a role/company table that maps to user id.
You also have access to the domain in RLS.

https://github.com/supabase-community/supabase-custom-claims

https://github.com/GaryAustin1/custom-properties

Another repository has a combo of both what I show and the jwt claims that is a bit more flushed out but I don't have the link handy.

Header access: https://postgrest.org/en/latest/references/transactions.html?highlight=http%20headers%20jwt#request-headers-cookies-and-jwt-claims

GitHub

GitHub - supabase-community/supabase-custom-claims: How to implemen...

How to implement custom claims with Supabase. Contribute to supabase-community/supabase-custom-claims development by creating an account on GitHub.

GitHub

GitHub - GaryAustin1/custom-properties: Custom roles/teams/groups/c...

Custom roles/teams/groups/claims using tables and a setof functions for RLS - GitHub - GaryAustin1/custom-properties: Custom roles/teams/groups/claims using tables and a setof functions for RLS

PostgREST

Transactions

After User Impersonation, every request to an API resource runs inside a transaction. The sequence of the transaction is as follows: Access Mode: The access mode determines whether the transaction can modify the database or not. There are 2 possible values: READ ONLY and READ WRITE. Modifying the...

vick•1/15/24, 2:25 PM

I structured my project to have all the data "owned" by a company_id. I have a table that maps users (based on the authenticated user_id) to companies, and all the RLS is done using that mapping to company_id. The mapping table also defines the permissions that user has for that company.

Vvick I structured my project to have all the data "owned" by a company_id. I have a t...

JohnOP•1/16/24, 3:39 AM

This is brilliant.

Vvick I structured my project to have all the data "owned" by a company_id. I have a t...

JohnOP•1/16/24, 5:50 PM

Do you have an example you can share of how you did this?

vick•1/16/24, 6:43 PM

I have a table of user to company permissions mappings with the following fields: user_id, company_id, roleuser_id, company_id, role.

Every table has a field for company_idcompany_id which has a FK to the companies table, where company_idcompany_id is the PK.

Every table (other than the mapping table) has a RLS of something like this: USING (company_id = ANY (allowed_companies(auth.uid(),'viewer')))USING (company_id = ANY (allowed_companies(auth.uid(),'viewer')))

Where the allowed_companies() function queries the mapping table like this: SELECT array_agg(company_id) FROM user_permissions WHERE user_id=user_guid AND role >= min_roleSELECT array_agg(company_id) FROM user_permissions WHERE user_id=user_guid AND role >= min_role.

My only restriction is that any given user_id,company_id tuple must be unique; i.e., you cannot have two different roles for the same company. My roles are defined to be cumulative, so having two is pointless. Aside from audit tracking log tables, the only place I reference the user_id is in the permissions mapping table. It could be an FK to the user_id in the auth.users table, but I don't do that.

The user_permissions table has RLS of "see your own" like USING (user_id=auth.uid()USING (user_id=auth.uid() so you cannot see anyone else's permissions.

We provision our accounts, so I don't have to deal with securing the permissions to write to the user_permissions table since no regular user can do it.

Authentication for different companies

Similar Threads

Similar Threads

Similar Threads