I am trying to implement an auth system similar to supabase-auth, using postgres and RLS for the user database. I am creating users that map to 2 postgres roles (admin and regular), and I want to create some sort of policy to be able to let users only view their information. As such, I need to be able to tell who is logged in, but I don’t want to create individual postgres users for each app user.

I noticed that supabase provides a auth.uid() sql function, that returns the user’s id and can be used like this

create policy "Individuals can view their own todos."
on todos for select
using ( (select auth.uid()) = user_id );

How would I go about implementing something like this? I don’t know how to set session variables in postgres, or anything similar to that.

For reference, this is how supabase does it.

Thanks in advance!