I am trying to find a good way to have dynamic pages and authentication. I am currently on the WeWeb + Supabase stack and experimenting still, and wonder if anyone else has solved authentication/dynamic pages/organization in a smarter and simple way… I will try to simplify.
Database roles and user
I have organization, role, userRole (mapping) and user tables in Supabase.
One user can have several userRole in several organizations.
Authentication
In WeWeb, authentication is all well due to the auth plugin.
However, authenticated is not enough. The organization needs to be correct, AND the userRole needs to have this organization_id, user_id and a role.
Dynamic page slugs
In WeWeb i use dynamic values in the URL so that i can have: organization/project/ and currently deploying slug, instead of id, for clean URLs.
This means I have to add the slug into all internal links A bit cumbersome to work with.
I am also storing the organization_id as a global variable (not local storage)
organization member checking
On every page load, I fetch organization, and organization + userRole of userId. (Does this user have a role in this organization?) This way i ensure that if you don’t have a role in this organization you will be redirected.
I of course will have RLS, so the data should be safe; but I’m not sure if its enough in itself. If i made this single organization, then it shouldnt be a problem I think? But the navigation between organizations and pages seem a bit problematic to me. How can I make this easier?
Just a tip, you can add the users organization and roles to the JWT using Supabase Auth Hooks “Customize Access Token (JWT) Claims”, that should be a safe way of doing this.
So it will be available without doing an extra database lookup upon page load. Then you then get both organisation and userroles available in supabase to use in your RLS checks, there also without needing database lookups, very good for performance.
I can’t figure out how to set a claim that writes to raw_user_meta_data. I would have it in a function which triggers on insert userRole, or update userRole. I’ve been looking but I’m stuck. Any pointers?
I actually did something else. I am currently storing the session_currentOrganization, then I have a app workflow that checks the session current org variable, if the organization_data has loaded, if the role has access to this organization.
In any case where everything is “OK” no call to the database happens. If something is “wrong” then it tries to fetch the correct data, if it fails, it redirects to a “select organization” page, which by clicking on an org will set the session_currentOrganization (also to local storage, actually)
This way, everything is smooth until its not - then some calls happen.
If nothing works there then you’re redirected and fix your own problem by selecting an org. Hopefully.
I also trashed the slugs for now for easier navigation. Might be handy later.
Heres how I set the claim in the “Customize Access Token (JWT) Claims” hook.
remember user must be blocked from changing his own org_id and roles by RLS.
DECLARE
user_id UUID := (event->>'user_id')::uuid;
claims jsonb := event->'claims';
org_id BIGINT := null;
roles_array TEXT[] := ARRAY[]::TEXT[];
BEGIN
-- Fetch org_id from the profiles table
SELECT organisation_id INTO org_id
FROM public.profiles
WHERE id = user_id;
-- Fetch array of roles' short_ids for the user
-- Ensure roles_array is at least initialized to an empty array
SELECT ARRAY_AGG(r.short_id) INTO roles_array
FROM public."userRoles" ur
JOIN public.roles r ON ur."roleId" = r.id
WHERE ur."userId" = user_id;
-- If no roles are found, initialize roles_array to an empty TEXT array
IF roles_array IS NULL THEN
roles_array := ARRAY[]::TEXT[];
END IF;
-- Ensure 'app_metadata' exists in claims
IF jsonb_typeof(claims->'app_metadata') IS NULL THEN
claims := jsonb_set(claims, '{app_metadata}', '{}');
END IF;
-- Update claims with roles and org_id
claims := jsonb_set(claims, '{app_metadata, roles}', to_jsonb(roles_array));
claims := jsonb_set(claims, '{app_metadata, org_id}',
CASE WHEN org_id IS NULL
THEN 'null'::jsonb
ELSE to_jsonb(org_id::text)
END);
-- Update the 'claims' object in the original event
event := jsonb_set(event, '{claims}', claims);
-- Return the modified event
RETURN event;
END;
This actually doesn’t seem to help that much - even though it’s right - at least not in a simple fashion, I’ve attempted to make this work in my own script. It only gives you the custom claim within the JWT, so you’d need to go all the way to decode the JWT and only then you can see the org, so that’s a whole lot of extra work.
WeWeb, as far as I know (just tested it) doesn’t decode the JWT by default, it as well fetches the roles via a select to the roles table.
You would need to de-code it with Custom JavaScript, which is not quite no-code friendly.
This JWT Claim approach also is not my preferred, even though I thought it would be the best solution, because when you change the user’s role, or a user’s org, if you rely on the token information, then the user can have an “admin token” until his session expires (unless you reset it - which is yet an another hassle to do.)
This seems to be right, I cannot get the custom claims in the nocode user object. I think it should be there, but I’m not using it frontend.
Yea, this is the backside.
But making a database query within the RLS function, to get the org_id and user group is very expensive, as I was seeing major slowdowns when doing queries on many rows.
Using custom claims was a good solution for that. For example, checking 10.000 rows was extremely expensive, and I didn’t find a way to cache the query so it was only used once for all rows.
Do you know a way?
I think you might want to set up indexes, if it’s slow. 10 000 items should be nothing for a PostgreSQL DB. And you’re probably doing the same lookup, just on the DB before sending it via the claim right?
Well that’s the thing, doing the lookup once on login, vs doing the lookup 10000 times (once for each row) inside a RLS function. Even if I had all indexes in place, it was way slower than looking at the custom claims.
To get both the roles and the org_id i would have to get some data, something like this:
SELECT *
FROM public."userRoles"
JOIN public.roles r ON "roleId" = r.id
WHERE u"userId" = auth.uid() AND r.name = role_name
and
SELECT organisation_id
FROM public.profiles
WHERE id = auth.uid();
Now, I would be happy if I could retrieve this only once for each lookup, but the RLS function is run once for each row. So I would need to cache it somewhere. So this is the reasoning for using custom claims. Of course it would be better to have it update for each lookup, so you could update priveleges without logging in again, but I think the user experience with really fast data lookup is more important.
Yes, that makes sense, the issue with these claims is, that when the role changes mid session, you still have the old role, which might be undesired. For the rest, it indeed is a valid solution. I think though that this shouldn’t be that much of an issue ,if you have proper indexes in place it should be super fast.
Edit: At the end of the day, this is how the RLS was made before Supabase introduced the custom claims. It actually is a new feature, still in Beta I think, and I also found some bugs in the Dashboard when using Claims.
I believe I managed to get this search query (filtered collection) of that table down to around 500 ms at best, using the RLS functions with joined database lookups. Now I’m around 60 ms for each, so pretty much feels instant.
Sounds like quite a big improvement, 500 ms is huge, I’d consider looking for the root of that timing. @thomlov I actually found this in the Supabase Docs.
So it actually might be this part, instead of the whole thing slowing down the query.
This thread is gold!
Multiple row and subquery for SELECT is pretty interesting. I’m currently using this for select RLS, which checks if the row in question belongs to the user’s organization. This will be expanded with role at some stage.
(organization_id = ( SELECT "userRole"."organizationId"
FROM "userRole"
WHERE ("userRole"."userId" = ( SELECT auth.uid() AS uid))))
I just added the select to auth uid This was created partly Supabase AI, and for some reason it added ‘AS uid’? Does this look OK? It seems to work fine, but I can’t say anything on performance gains.
–
I’ll probably have to revisit this thread. It seems to me as if I have a good way of qualifying based on organization which only queries once per session, RLS should work well as the actual security measure for fetching data. It’s been a while since i tried logging in as a user with two orgs, so that I’ll have to check.
Nice find! Using SELECT as this in the RLS policy actually helps solve the case of caching, on simple RLS policies. I just tested, and I achieve the same quick replies now using database lookups as the JWT.
However, it seems that it doesn’t help in the case of more advanced RLS queries. As in the function below, I have a table with 18.000 entries, and they have a location_id, and I have to check that location_id against the location to find the org_id, to validate the user. Here I’m still seeing huge differences in query time, doesn’t seem to be cached in the same way.
Will do more testing tomorrow to try to find out of the more advanced cases
DECLARE
jwt_claims jsonb;
auth_uid uuid := auth.uid();
user_role_short_ids text[];
user_org_id bigint; -- Extracted from JWT or user's profile
has_required_claims BOOLEAN := false;
lokasjoner_org_id bigint; -- Organization ID associated with lokasjoner_id
BEGIN
jwt_claims := auth.jwt();
-- Try to extract user's organization ID and roles from JWT
IF jwt_claims->'app_metadata' ? 'org_id' AND jwt_claims->'app_metadata' ? 'roles' THEN
user_org_id := (jwt_claims->'app_metadata'->>'org_id')::bigint;
user_role_short_ids := ARRAY(SELECT jsonb_array_elements_text(jwt_claims->'app_metadata'->'roles'))::text[];
-- Check if roles from JWT match any in role_names
has_required_claims := user_role_short_ids && role_names;
END IF;
-- Fetch the organization ID associated with lokasjoner_id
SELECT l.organisation_id INTO lokasjoner_org_id
FROM public.loc_lokasjoner l
WHERE l.id = lokasjoner_id;
-- Proceed with the check only if lokasjoner_id is valid and the organization ID is obtained
IF lokasjoner_org_id IS NOT NULL THEN
-- If we have the necessary claims from JWT and the organization IDs match
IF has_required_claims AND (user_org_id = lokasjoner_org_id) THEN
RETURN TRUE;
ELSE
-- Fallback to DB check
-- Log the missing required claims in JWT
RAISE LOG 'rls_has_role_access_lokasjoner_id - Fallback to DB: Missing required claims in JWT. User: %, JWT: %', auth_uid, jwt_claims::text;
RETURN EXISTS (
SELECT 1
FROM public."userRoles" ur
JOIN public.roles r ON ur."roleId" = r.id
JOIN public.profiles p ON ur."userId" = p.id
WHERE p.id = auth_uid
AND r.short_id = ANY(role_names)
AND p.organisation_id = lokasjoner_org_id
);
END IF;
ELSE
RETURN FALSE;
END IF;
END;
I actually found a crazy sophisticated way of doing this. In Postgres you can set some in memory enviroment values, via the set_config() function. Meaning that you could instead of using the claim, store this as a “session variable” (by using the set_config() with the flag false. Then you wouldn’t have to use any queries to fetch the credentials (workspace, role), only fetch it from these settings.
You would fetch these settings by hooking up to the postgrest’s pre_request hook so it would go something along the lines of:
pre_request fetches the workspace and the role
you only get the workspace and the role via select current_setting() which is in memory, so it works exactly like a custom claim, but with the benefit of being fetched once every PostgREST run apparently.
Then you’d do your usual checks. This eliminates the issue of the JWT not refreshing instantly. You in theory then could store these details in the metadata, meaning that you would be able to access it in WeWeb as well.
That actually is the thing I also found and that I’m mentioning in the comment above. I’m afraid though that “just installing the extension” might be a temporary fix for something as complex as Multi Tenant RBAC. I’m studying that extension now as we speak
it’s not a nocode install, but it should be easier than copy pasting sql code in the supabase dashboard.
it’s not a solution for any situation, but it is an opinionated solution with rbac, multitenant, invitations. you can expand from there, for example I like a different db structure for this.
