user_metadata is editable on the client. It is not secure. app_metadata is server side only. The approach described used the tenant_id sourced from Supabase auth, and compares it to the tenant_id in app_metadata (which, again, is not user editable). The user never has an opportunity to try and change their tenant_id.
Hi Ryan, thank you for this article, I found it very helpful. Just for reference (more in case anyone else had this issue), I wanted to update my auth.users table using Supabase's SQL editor, and I found that the column name I had to reference in the query was "raw_app_meta_data", not "app_metadata". Interestingly, when next creating the auth.tenant_id() function, it doesn't work with "raw_app_meta_data" - it must use "app_metadata" for the column name. It looks like there's 2 different names with which to read the column. But your instructions here work perfectly as long as one is doing it via JS as you have done.
just stumbled on this post...
"You *must* use `app_metadata` and NOT `user_metadata`. The former is secure and cannot be modified by the user, where the latter can be"
What prevents a user from hacking an api call to change their tenant ID to one that they aren't supposed to have access to?
It seems that the only safe way is to set the tenant ID in user_metadata when the user is created
user_metadata is editable on the client. It is not secure. app_metadata is server side only. The approach described used the tenant_id sourced from Supabase auth, and compares it to the tenant_id in app_metadata (which, again, is not user editable). The user never has an opportunity to try and change their tenant_id.
Nice pragmatic solution.
I would like to share with everyone my final solution.
Create a function to automatically add the tenant_id when inserting a new row or updating an existing ro in a table:
create or replace function set_tenant_id()
returns trigger
language plpgsql
as $$
begin
-- Setzt den Wert von auth.tenant_id() in die tenant_id-Spalte, falls diese NULL ist
if NEW.tenant_id is null then
NEW.tenant_id := auth.tenant_id();
end if;
return NEW;
end;
$$;
And the trigger for the table "article" as an example:
create trigger set_tenant_id_before_insert_or_update
before insert or update on article
for each row
execute function set_tenant_id();
Hi Ryan, thank you for this article, I found it very helpful. Just for reference (more in case anyone else had this issue), I wanted to update my auth.users table using Supabase's SQL editor, and I found that the column name I had to reference in the query was "raw_app_meta_data", not "app_metadata". Interestingly, when next creating the auth.tenant_id() function, it doesn't work with "raw_app_meta_data" - it must use "app_metadata" for the column name. It looks like there's 2 different names with which to read the column. But your instructions here work perfectly as long as one is doing it via JS as you have done.
I get ERROR: unrecognized configuration parameter "request.jwt.claims" :(