Custom claims for multi tenancy and user roles

[xylys]

Feature request

Is your feature request related to a problem? Please describe.

Adding custom claims on the JWT token like in firebase.
This would make multi tenancy and user roles easier to implement with RLS

Describe the solution you'd like

The ability to add custom claims to the JWT token send to the user upon sign in.
For example one or multiple organisation ID's a user belongs to.

This would greatly simplify and speed up RLS policy creation, since the org_id from the token can be directly compared to the org_id of the row without doing an additional query.

eg. what currently uses an additional select query

create policy "Read messages if user is a member of the org" 
  on public.messages 
  for select using ( 
    auth.uid() in (
        select user_id from members where members.org_id = messages.org_id
    )
  );

Would become a simple policy we could add in the web ui

auth.claims.organisation = org_id

A realtime listener or polling system could be implemented in the client library to automatically refresh the token in case a users custom claims would change.

[kiwicopple]

You can definitely use the auth.raw_user_metadata and auth.raw_app_metadata columns for this.

However I wouldn't recommend it. JWT is great for Authentication, but not so great for Authorization. What happens if a user is removed from an organization (in your example above)? Their token is still valid for 1 hour, and the Policies will continue to allow the user until the token is refreshed.

It's a tradeoff between simplicity for security.

I do think we need to set up a good multi-tenant solution, something which is simple and secure. I'll transfer this over to a Discussion, because I'd love to get comments/ideas. Here is a good article which could seed the discussion:
https://cazzer.medium.com/designing-the-most-performant-row-level-security-strategy-in-postgres-a06084f31945

[wadclapp]

raw_user_meta_data can be updated by the user (e.g. supabase.auth.update({ data: { example: 123 } }))? Is raw_app_meta_data the same? Or can they be prevented from being updated by the user? Is there any documentation on these?

I'd like to store the user's username (a copy from their public.users record), as well as some other info, to make things easier, at least until something like custom claims is implemented.

[emilioschepis]

I had the same doubt and looked around in the GoTrue code. While raw_user_meta_data can be updated by the user, raw_app_meta_data requires the admin role.

https://github.com/supabase/gotrue/blob/master/api/user.go#L92-L106

I think I will explore this way of associating custom claims to the user until a native solution is implemented.

[logemann]

I am also not sure if everybody should create their own "roles" tables in the DB and map them to the authed users or if a IAM framework should support in that area too. Auth0 definitely does offer some role management features.

[NixBiks]

Did anyone implement something that can be recommended to achieve this?

[RobertB4]

I haven't finished the whole implementation yet, but I have a working postgres policy that uses app_metadata from the jwt, which contains the data from the raw_app_meta_data column in the auth.users table. Basically, any data saved to raw_app_meta_data can be used to write policies.

Supabase adds a few helper functions to postgres by default, e.g. the auth.uid function. Its content is as follows:

  select
  nullif(
    coalesce(
      current_setting('request.jwt.claim.sub', true),
      (current_setting('request.jwt.claims', true)::jsonb ->> 'sub')
    ),
    ''
  )::uuid

As a reminder, supabase jwts contain the following data:

type JWT = {
  exp: number;
  sub:  string;
  email: string;
  phone: string;
  app_metadata: {
    [key: string]: any
  };
  user_metadata: {
    [key: string]: any
  },
  role: string
}

Essentially I just took the auth.uid function and rewrote it a little. However, I removed the line current_setting('request.jwt.claim.sub', true) because I don't quite understand it. request.jwt.claim always seems to be null, maybe someone who has a better understanding can chime in why it is needed.

Here is my policy example. Everything between brackets needs to be replaced:

create policy ["policy name here"]
   ON [table name here] USING (
     (select
   nullif(
       ((current_setting('request.jwt.claims')::jsonb ->> 'app_metadata')::jsonb ->> '[data key here]'),
     ''
   )::uuid = '[comparison value here]')
);

The key line is ((current_setting('request.jwt.claims')::jsonb ->> 'app_metadata')::jsonb ->> '[data key here]'), which for example can be used like this: ((current_setting('request.jwt.claims')::jsonb ->> 'app_metadata')::jsonb ->> 'company_id')

Here is an example that should work out of the box:

create policy "only users of the same company can read/write todos"
   ON public.todos USING (
     (select
   nullif(
       ((current_setting('request.jwt.claims')::jsonb ->> 'app_metadata')::jsonb ->> 'company_id'),
     ''
   )::uuid = public.todos.company_id)
);

I know kiwicopple said that that this approach has the drawback that a user still has access until the jwt expires after their rights get revoked, but personally it is a tradeoff I am willing to make. I am not sure if there are any other drawbacks to this solution.

[RobertB4]

Following up on my post from yesterday since my multi tenancy custom claims setup is now fully working. Here are the steps I took to implement it.

Step 1: Make sure to add organization_id to auth.users.raw_app_meta_data after a user signs up.
Updating this column requires admin rights to the database, meaning it has to be done server side. Also, the supabase client does not expose any api to update raw_app_meta_data so you will need another postgres client that allows you to write raw sql.

Step 2: Create a postgres function to extract organization_id from the jwt.

create or replace function auth.orgid() returns uuid as $$
	 select nullif(
			((current_setting('request.jwt.claims')::jsonb ->> 'app_metadata')::jsonb ->> 'organization_id'),	
			'')::uuid
$$ language sql;

Step 3: Set up your policies. Here is an example policy for my boards table. This policy assumes that the boards table has a column called organization_id.

create policy "allow read/write access to boards for users of the same organization"
  on public.boards for all using (
    (auth.orgid() = boards.organization_id)
  );

Depending on your policies, you may or may not run into authorization issues when trying to set up a new user. E.g. if you have an organizations table that only allows read/write access to users of the same organization, you may have to create an organization with admin access to the db depending on the order you do things in. Personally, I want my db to auto generate uuids, so I have to create an organization before I can use its id to update auth.users.raw_app_meta_data. If you generate a uuid on the server this won't be an issue. It all depends on your table setup, e.g. if you use automatically incrementing integers as ids, you will probably have a similar problem.

I haven't fully implemented it yet, but to use custom claims for role based authorization, you can create a postgres function similar to the auth.orgid function. Here is mine:

create or replace function auth.authz_role() returns integer as $$
	 select nullif(
			((current_setting('request.jwt.claims')::jsonb ->> 'app_metadata')::jsonb ->> 'authz_role')::integer,	
			0)::integer
$$ language sql;

[steve-chavez]

@mike-luabase You could create a plpgsql function that inside does a

RAISE NOTICE 'The full claims are: %', current_setting('request.jwt.claims', true);

Use that function inside your policy and then check your postgres logs for the traces.

[mike-luabase]

@steve-chavez I added this:

raise notice 'test raise notice does it worrrkkk...';

to a function and it doesn't show in the Supabase database logs. It should, right?

[mike-luabase]

Looks like LOG level works:

raise LOG 'test raise notice does it worrrkkk...';

[maximeantoine1997]

I can confirm that this implementation works as expected and makes it WAY EASIER to create new RLS policies afterwards thanks to the postgres functions auth.orgid(). Thank you RobertB4

[bombillazo]

@RobertB4 thanks for sharing, this is very useful!

[PH4NTOMiki]

I implemented this function:

create or replace function auth.my_role() returns text security definer as $$
  select nullif((select raw_app_meta_data from auth.users where id = (select nullif(current_setting('request.jwt.claims', true)::json->>'sub', '')::uuid))->>'role', '')::text;
$$ language sql stable;

so I still store user role inside that raw_app_meta_data but if I change it in the DB, the user is denied access immediately without needing for the JWT to expire. @kiwicopple what do you think of that, also how is it performance-wise?

And for the part needing some server to change it, you actually don't need it anymore, you can do it inside RPCs.

[PH4NTOMiki]

Also @kiwicopple would that role field in raw_app_meta_data be automatically removed(kicked out) somehow, from some DB internal procedure. I hope not

[hatemjaber]

@RobertB4 does having multiple schemas mean that you have to replicate user-defined-functions for each schema (assuming the structure of each schema is the same)?

[psteinroe]

Is there any update here? I just came across db-pre-request functions in postgREST. If we would be able to "enrich" the request once it hits the database similar to how the jwt is set to current_setting that would simplify a lot.

EDIT:
I just received answer from the (super awesome) supabase support and they hinted that db-pre-request can already be set!

ALTER ROLE authenticator SET pgrst.db_pre_request to 'your_custom_function';
NOTIFY pgrst, 'reload config';

and to reset

ALTER ROLE authenticator RESET pgrst.db_pre_request;
NOTIFY pgrst, 'reload config';

more details here!

[RyannGalea]

Has any further progress been made with multi-tenancy, basically going through this kind of setup now and it seems overly complex and really should be built in. Hopefully some news in his upcoming release week.

[omartineau]

I'm also interested. I'm building a multi-tenancy Saas. For now, I'm builling with de RobertB4 solution above in the thread. But I know it's not the perfect solution because app_metadata are persisted in DB.

[chocovish]

nhost is having a decent solution for custom jwt claims where we can add any data which is some how related to the auth.users table.
we can have something similar, like an ui or after-signin-hook where we can put sql query and the result from the query will be attached to a given claim.
https://github.com/nhost/hasura-auth/releases/tag/v0.2.0

[alexaron]

const firebaseTenantId = 'org1-y6bqo'; // Taken from Cloud Console, where this tenant already exists.
const auth = admin.auth().tenantManager().authForTenant(firebaseTenantId)
return await auth.createCustomClaims(user.uid, {
fooClaim: 'bar'
});

[josephbuchma]

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