Supabase•2d ago

N sitters per Company

Company can pay for N licences for users to use at the same time. How to control number of users using app at the same time per company? 1. Auth Hooks are not for this. 2. edge functions? 3. own backend app working as a proxy between supabase? Is it possible to use only edge functions in supabase? Limitations? How would you solve it in supabase? Context: my experience with supabase is limited. I am looking simple solution to maintenance for very small team.

4 Replies

Colin Emonds•2d ago

You mean users simultaneously logged in or users simultaneously registered? The latter one is easy, you could use a trigger for that. The former is kind of hard to implement in any system that uses JWT-based authentication, as you don't have a central "sessions" table where you can count rows. What I would probably do is decouple license management from the authentication flow, then check both for authorization. Like this:

create table public.license (
    license_id uuid primary key default extensions.uuid_generate_v4(),
    company_id uuid not null,
    in_use_by uuid,
    in_use_since timestamptz
);

revoke insert, update, delete on public.license from public;
grant update(in_use_by, in_use_since) on public.license to authenticated;

create table public.actual_app_functionality (
    foo text
);

alter table public.actual_app_functionality enable row level security;

create policy is_user_licensed on public.actual_app_functionality
as restrictive
for all
using (
    exists(select * from public.license where in_use_by = auth.uid())
);

create table public.license (
    license_id uuid primary key default extensions.uuid_generate_v4(),
    company_id uuid not null,
    in_use_by uuid,
    in_use_since timestamptz
);

revoke insert, update, delete on public.license from public;
grant update(in_use_by, in_use_since) on public.license to authenticated;

create table public.actual_app_functionality (
    foo text
);

alter table public.actual_app_functionality enable row level security;

create policy is_user_licensed on public.actual_app_functionality
as restrictive
for all
using (
    exists(select * from public.license where in_use_by = auth.uid())
);

Now, when a user logs in, after authentication is completed, just run this in their context:

supabase
  .from('license')
  .update({ in_use_by: supabase.auth.getUser().data!.user.id, in_use_since: new Date() })
  .filter('in_use_by', 'eq', null)
  .limit(1);

supabase
  .from('license')
  .update({ in_use_by: supabase.auth.getUser().data!.user.id, in_use_since: new Date() })
  .filter('in_use_by', 'eq', null)
  .limit(1);

You should probably extend this with a policy on public.license that checks whether the user that wants to update (or even select) a row actually belongs to the given company, and there's maybe a bunch of other details that you want. But I think for a general approach, this should be quite productive.

kwladykaOP•2d ago

You mean users simultaneously logged in

yes, for example max 3 users from 10 at the same time in company X can use the app. Each company has own number of licences (users at the same time). let me check if I understand: 1) auth stay as it is in supabase 2) each request / query to DB is going through edge function instead of directly to supabase 3) edge function read query, check conditions, if conditions ok, then send query to DB and return ? or rather: 1) auth go through edge function 2) edge function check conditions 3) edge function reject login or let login 4) later on each equry / request to DB go in common way directly to supabase ? Are both solutions possible in supabase + edge functions? Any not obvious issues / limitations vs having own backend service as proxy?

Colin Emonds•2d ago

I would recommend option 1, but the solution I suggested does not even use Edge Functions. You can use Edge Functions for this, if you want, but you can also use PostgreSQL's Row Level Security feature to add additional constraints for data access (in this case: user is currently licensed). That's how my solution from above does it: the user grabs a license after login by writing their own ID into the in_use_by column, then whenever a request comes in, the Row Level Security policy checks whether the user is currently in the in_use_by field of any license, and if not, rejects the request. The advantage is that you retain the ability to use the normal Supabase tooling on the client, instead of having to proxy everything through an Edge Function. The licensing is checked by the database itself, and hence, there's no way to forget to check for licensing either (as long as each public table or function has the above RLS policy applied). If you use Edge Functions, as long as those connect to the database in the user's context, you get the licensing check for free there.

kwladykaOP•2d ago

ah I see. This is interesting. Thank you.

Gaming

Programming

N sitters per Company

Did you find this page helpful?