Recently, I've been creating https://shareweave.com. Right now it's just a simple discord bot to save messages to a tasklist and set reminders for them - it's mainly targeted towards people who manage communities and will likely expand to have more features to support community managers in the future.

I decided to use supabase because I wanted to have a web interfae for more flexibility, and I wanted it to be simple to do CRUD stuff and realtime in the future. I've tried a Mongo + Nodejs combo in the past for something similar, but found supabase to be easier. However, I did run into a problem with the auth. It seems that supabase isn't really meant for connecting to other apps. While most things were easy, one thing I stuggled with was the ability to use discord IDs in my app.

For instance, when discord sent my app a notification that someone wants to set a reminder for something, I needed to query a supabase table to find the discord ID for that user's settings. Supabase has docs about making a profile table (which is just a public settings table) for a supabase ID, but not for an external app like discord. The solution is pretty simple, but wasn't clear and well documented, so I'll show you what I did here.

I decided to solve this by creating my table settings and creating the discord_id property on it. To ensure that only people who owned the discord ID were able to insert/edit their row in the table, I needed to use the auth.uid() function to perform a lookup on auth.identities, where the discord ID and other info regarding external identities is stored. I then had to use that lookup result and check it against the incoming discord_id parameter to decide whether this action was allowed or not. Then, I just had to add this logic to my row level security policy. But there's a little problem here.

I realized that RLS policies are run as the API user, so they do not have access to auth.identities. With some help from the supabase community, I figured out that making a function for it would fix this. I can define a postgres function that has full DB access, and then use that in the policy.

The code below creates a function that performs a query to get a discord ID from a supabase uid, regardless of permissions. The security definer part is what lets it get full permissions. The function returns the result of the select id from... query.

create or replace function public.getDiscordIdFor(arg_user_id uuid) returns text language 'sql' security definer as $BODY$
      SELECT id
      FROM auth.identities
      WHERE user_id = arg_user_id AND provider='discord'
$BODY$;                     

Now, to use that result in an RLS policy:

((auth.uid() = id) AND (discord_id = getdiscordidfor(auth.uid())))

(I also have an id column which will have the supabase ID, in case I need it)

This should be applicable to other integrations with supabase, like slack or notion. Comment if you have any questions! Hope this helps someone :)