User IDs as strings instead of UUIDs for external auth?

[colinclerk]

Hello - I work for clerk.dev (an authentication service) and we're working to improve our compatibility with Supabase.

A key challenge is that our User IDs are strings (prefixed KSUIDs) instead of UUIDs. This is breaking the auth.uid() helper which expects a UUID in the sub claim. In particular, we think line 100 is throwing errors:

supabase/docker/volumes/db/init/01-auth-schema.sql

Lines 92 to 101 in c8d910a

	create or replace function auth.uid()
	returns uuid
	language sql stable
	as $$
	select
	coalesce(
	current_setting('request.jwt.claim.sub', true),
	(current_setting('request.jwt.claims', true)::jsonb ->> 'sub')
	)::uuid
	$$;

Is there a recommended way to get past this? We'd like to better understand the different options.

Currently, our guide omits the sub claim and uses a userId claim instead. We then ask developers to define a separate function to grab the user ID. This is unfortunate since it means developers cannot use auth.uid() and it's not a great developer experience overall.

We've had three other ideas:

• We can submit a PR to cast auth.uid() to text if casting to a uuid fails. This is our preference if it would be accepted.
• We can try to instruct developers to override auth.uid(). We haven't explored this as an option because we are concerned with forward compatibility through supabase upgrades. Is this a valid concern?
• We can start generating UUIDs for each user, and put that into the sub claim instead. We're not huge fans of this since we'd be giving 2 IDs to each user.

[arcavid]

It'd be just amazing to have the simple auth.uid() work.
I wonder if this feature is being worked on since this issue is more than two months old.

[FranckPoingt]

Any update on the matter? The provided workaround doesn't work...

[JonathanPicques]

Maybe creating a new policy (like auth.ouruid()) could help?
See #1849

[alex-galey]

The suggested workaround didn't work for me and this is a deal breaker not to be able to implement RLS

• I defined a custom function in postgres requesting_user_id()
select nullif(current_setting('request.jwt.claims', true)::json->>'sub', '')::text;

• The function is seen on project/xxx/database/functions

• I defined an RLS policy (RLS is active on the table ofc) for the users only to be able to get their own data :

CREATE POLICY "Pol 1" 
	ON public.user USING (
		auth.role() = 'authenticated'::text
	) WITH CHECK (
		requesting_user_id() = auth_id
	);

• I used the Clerk + Supabase template as a strating point

• I was able to insert a row with user_id :

const token = await getToken({ template: 'supabase-user-dashboard' });
supabase.auth.setAuth(token);
await supabase.from('user').upsert({
   auth_id: userId,
   firstname: 'Alex',
   lastname: 'G'
   }, { returning: 'minimal' })

userId is coming from const { userId, getToken } = useAuth();

• I manually added another row with non-matching auth_id and queried the table again, which now returns the other row too :

const token = await getToken({ template: 'supabase-user-dashboard' });
supabase.auth.setAuth(token);
const { data } = await supabase.from('user').select();

Any update on when the native auth.uid() api call would be working ?

[steve-chavez]

Hey @colinclerk, sorry for the late reply here.

We can submit a PR to cast auth.uid() to text if casting to a uuid fails. This is our preference if it would be accepted.

Can you show a snippet of how would you do that? Not sure if possible since the function signature would have to change as well(returns text?)

---

As suggested by JonathanPicques, another option would be to add a custom funcion to the auth schema. Maybe an auth.external_id()(it could take a key parameter in case the id doesn't come from the sub claim)

[mypalvikram]

Hello, just wanted to check if there have been any updates on this or if using the custom function is still the way to go. Thanks!

[rdylina]

@colinclerk I'm also curious if there has been any progress on this topic.

[sohcfst]

Would love integrate clerk and supabase in my project but coming across several snags as I'm not great with understanding RLS or SQL in general ):

[tonyabracadabra]

Also looking for this!

[kenmwangi]

Is there any progress on this issue? Seem to take a long time to resolve

[castroalves]

Any news on that?

[mansueli]

First, you'll need to drop the FK from the storage.objects table:

ALTER TABLE storage.objects 
DROP CONSTRAINT objects_owner_fkey;

Here's a possible workaround for converting any text into uuid by hashing it, then formatting as UUID:

CREATE OR REPLACE FUNCTION convert_to_uuid(input_value text)
RETURNS uuid AS $$
DECLARE
  hash_hex text;
BEGIN
  -- Return null if input_value is null or an empty string
  IF input_value IS NULL OR NULLIF(input_value, '') IS NULL THEN
    RETURN NULL;
  END IF;
  hash_hex := substring(encode(digest(input_value::bytea, 'sha512'), 'hex'), 1, 36);
  RETURN (left(hash_hex, 8) || '-' || right(hash_hex, 4) || '-4' || right(hash_hex, 3) || '-a' || right(hash_hex, 3) || '-' || right(hash_hex, 12))::uuid;
END;
$$ LANGUAGE plpgsql;

Then, you could edit the clerk's requesting_user_id function to use this instead:

create or replace function requesting_user_id()
returns uuid
language sql stable
as $$
  select convert_to_uuid(current_setting('request.jwt.claims', true)::json->>'sub');
$$;

[novemberkilo]

Thanks for this @mansueli

I was able to follow your suggestions here but with some modification. For the purposes of storing the generated uuid in a user_id column, I tried to use your convert_to_uuid function with something like

ADD COLUMN user_id uuid GENERATED ALWAYS AS (convert_to_uuid(clerk_user_id)) STORED;

but couldn't because of the error generation expression is not immutable -- if you do \df+ convert_to_uuid in psql you will see that this function is volatile

I was however, able to use postgres' uuid functions to accomplish this and get RLS working via a corresponding change to requesting_user_id

fwiw I used generate_uuid_v5 in place of convert_to_uuid successfully.

[tonyboylehub]

Can this be picked up and looked at again for proper intergration? You guys are "partners" according to you (Supabase) per your site and claim to be intergrated with each other but this still isn't resolved after 10 months nearly.

Storage is still broken and It's not a full intergration of service currently, or it at least needs to be noted in docs from both sides that there is "limited" intergration at least. That's 10 months of devs picking these two as a potential service to find out they are not fully integrated quite a way into a build/test.

Edit: I thought this originally brought up in Jan 2023 (only read the Jan part intially), but it was Jan 2022 this was brought to attention.

1 year 10 months this has been a thing.

[skorfmann]

ALTER TABLE storage.objects 
DROP CONSTRAINT objects_owner_fkey;

shouldn't be required anymore, see supabase/storage#276 (comment)

[tonyboylehub]

When ever your try and use Clerks JWT Auth for storage with signed URLs it still checks for a UUID in sub of the JWT and fails because it can't convert it to type of UUID. I don't believe it even makes it as far as the table.

[skorfmann]

So everything which is relying on UUID won't work, correct? e.g. user based permissions for storage since owner is uuid

create policy "Individual user Access"
on storage.objects for select
to authenticated
using ( auth.uid() = owner );

anything else which is affected by this?

[tonyboylehub]

I partly missed some of the issue I think, it's with the createSignedUploadUrl.

By default the storage.objects owner was a UUID, I've sinced used the sql editor to drop that column and create a new one as a texr but no luck still and still produces the same error.

In the Clerk JWT the user id is stored in sub of the token.

Then checking the sub of the token for the userID, which I've currently been using for all database relations.

error: StorageApiError: invalid input syntax for type uuid: "user_xxxxxxxxxxxxxxxxxxxxxxxxxxxx"
I can also see this log in the SQL logs as well.

I thought I had removed all the instances of UUID but clearly I haven't and something else is at play too.

I'm also a little aprehensive of changing to much in the objects table, as mentioned above and in multiple threads that the tables could be overwritten by Supabase it seems during an update/migration.

I've tried all the suggestions so far from policies, addtional sql functions, table editing/column renaming.

[Eliot6001]

Maybe turn it into a separate small dependency that you add only if you're using the combo of "Supabase + clerk" which would include a bunch of functions such as clerkbase.auth() ?