Bridging External Auth with Supabase User Impersonation

As promised, here’s a write up on how I integrated Clerk authentication with Supabase Studio’s user impersonation feature for local development. I drafted it as a mini “how-to” guide, and while writing it, I realized there might be a simpler solution on Supabase’s end to handle this more gracefully...more on that at the end! 😀

This gist is split into two main sections:

• Section 1: A guide to bridging Clerk and Supabase Studio impersonation locally
• Section 2: A proposed enhancement to Supabase Studio's impersonation mode to more elegantly solve the same problem for all environments

Section 1: A Guide to Bridging Clerk and Supabase Studio Impersonation Locally

What We're Building

We're going to build a very simple "tasks" system protected by Row Level Security (RLS) that works with both Clerk authentication and Supabase Studio's user impersonation feature. This is a great way to test RLS policies in a local environment. By the end of this guide, you'll be able to:

1. Test RLS policies directly in Supabase Studio with user impersonation
2. Ensure your RLS policies remain consistent between Clerk and Supabase Studio
3. Handle organization-level permissions using custom JWT claims

Prerequisites

This guide assumes you have:

• A working Clerk integration with Supabase
• Configured your Supabase JWT template
• Setup Clerk authentication in your client application

We won't cover these steps in this guide, but you can find more information in the Clerk integration documentation.

The Challenge: Bridging Two Authentication Systems

Supabase Studio's user impersonation feature is amazing for RLS testing, but it expects a user record in auth.users with a UUID based ID. Meanwhile, Clerk uses text based IDs, (e.g. user_abc123) and doesn't store users in auth.users. So:

1. Your real users live in Clerk’s database, not in auth.users.

2. Supabase wants a UUID; Clerk IDs aren’t UUIDs.

   

The Solution: Mock Users in Supabase

We'll solve this by seeding mock users in our local auth.users table. Then:

• Production: Still uses Clerk, no changes needed
• Local dev: These mock users let you impersonate different scenarios in Supabase Studio. Perfect for testing RLS.

⚠️ WARNING: Never add mock users to production

1. Create Helper Functions to Bridge Clerk and Supabase

We rely on two main helper functions. They each read a claim from the active JWT session:

In the SQL Editor:

-- From the official docs, a function to parse the current user's id from the JWT
create or replace function public.requesting_user_id()
returns text
language sql
set search_path = ''
as $$
  select nullif(
    current_setting('request.jwt.claims', true)::json->>'sub',
    ''
  )::text;
$$;

-- Function to parse the current user's organization id from the JWT
create or replace function public.requesting_organization_id()
returns text
language sql
set search_path = ''
as $$
  select nullif(
    current_setting('request.jwt.claims', true)::json->'app_metadata'->>'org_id',
    ''
  )::text;
$$;

These work seamlessly with both:

1. Clerk tokens in production (so sub might be user_abc123).
2. Supabase impersonation tokens, where sub will be a UUID (likef47ac10b-58cc-4372-a567-0e02b2c3d479).

To ensure compatibility, in the Clerk dashboard, configure your Clerk JWT template to match Supabase's expected format by placing custom claims in the app_metadata field (a secure place to store authorization data). Paste this in:

{
  "aud": "authenticated",
  "role": "authenticated",
  "email": "{{user.primary_email_address}}",
  "app_metadata": {
    "org_id": "{{org.id}}"
  },
  "user_metadata": {}
}

This aligns with Supabase's JWT structure (do not paste this into Clerk, only for reference):

{
  "aud": "authenticated",
  "role": "authenticated",
  "email": "dev.org@example.com",
  "raw_app_meta_data": {
    "org_id": "test_org"
  }
}

2. Create the Tasks Table

Next, create a "tasks" table that uses our helper functions to assign user_id and organization_id. Then enable RLS:

In the SQL Editor:

-- Create the tasks table
create table tasks (
  id bigint generated by default as identity primary key,
  user_id text not null default requesting_user_id(),
  organization_id text default requesting_organization_id(),
  name text not null,
  is_done boolean not null default false,
  created_at timestamp with time zone not null default now()
);

-- Enable RLS
alter table tasks enable row level security;

Verify this was successful by navigating to the Table Editor. You should see the tasks table with no rows.

3. Create Mock Users for Testing

We need actual records in auth.users to leverage Supabase's impersonation UI. Let's add them.

In the SQL Editor:

-- Create a mock user for testing with no organization
insert into auth.users (id, email)
values (
  gen_random_uuid(),
  'dev.test@example.com'
);

-- Create another mock user that belongs to an organization (our custom claim)
insert into auth.users (id, email, raw_app_meta_data)
values (
  gen_random_uuid(),
  'dev.org@example.com',
  '{"org_id": "test_org"}'::jsonb
);

Verify this was successful by navigating to Authentication > Users, you should see both users.

4. Add Mock Task Data

Let's generate some tasks for each of these mock users.

In the SQL Editor:

do $$
declare
    user_record record;
begin
    -- Loop through each user in the 'auth.users' table
    for user_record in 
        select 
            id::text, 
            raw_app_meta_data->>'org_id' as org_id
        from auth.users
    loop
	-- Insert 10 tasks for each user
        insert into tasks (user_id, name, organization_id)
        select 
            user_record.id, 
            'Task ' || generate_series,
            user_record.org_id
        from generate_series(1, 10);
    end loop;
end;
$$;

Now each user in auth.users has 10 tasks associated with them. Verify this was successful by navigating back to the Table Editor and refresh the tasks table to see your new rows.

5. Setup Row Level Security Policies

We'll create a select policy that ensures:

1. A user can only access tasks where user_id = their sub
2. If we have an organization claim, it must match

In the SQL Editor:

-- Create the select policy
create policy "Users can only access tasks in their organization"
on tasks
as permissive
for select
to authenticated
using (
  (select requesting_user_id()) = user_id
  and (select requesting_organization_id()) is not distinct from organization_id
);

The is not distinct from operator is basically a friendlier = that behaves better with NULL values.

6. Testing in Supabase Studio

1. Go to the Table Editor and select tasks
2. In the upper right corner, click on “role” and pick “authenticated”
3. Locate your mock user (e.g., dev.test@example.com) and click Impersonate
4. Observe that you now only see tasks where user_id = that user’s UUID (and matching org_id if applicable).

Best Practices

1. Seed mock users in dev or staging, never production!
2. Use explicit naming for mock users, like dev.test@example.com
3. Test your RLS policies using both Clerk users (manually from your app) and mock users (Supabase Studio impersonation)
4. Keep your RLS policies identical for both auth systems, no separate logic!

Common Questions

Q: Why can't we just use Clerk IDs in auth.users?

A: Supabase Studio's user impersonation feature requires UUIDs, while Clerk uses text-based IDs. Our mock user approach is the easiest workaround.

Q: Do RLS policies work the same for both auth systems?

A: Absolutely. The requesting_user_id() function interprets sub consistently, whether it’s from Clerk or Supabase’s token. Same for requesting_organization_id().

Q: Is this safe for production?

A: As long as you only create these mock users in dev/staging, you’re good. The actual RLS logic is identical in any environment, so no worries there.

Section 2: Proposed Enhancement: Flexible Role Impersonation

Current Implementation

Currently, Supabase Studio's user impersonation feature operates by creating JWTs based on users that exist in the auth.users table. The system is designed around Supabase's native auth system, which expects specific data structures and UUID based user identifiers as seen here: https://github.com/supabase/supabase/blob/master/apps/studio/lib/role-impersonation.ts

It’s great if you’re fully on Supabase Auth, but not so smooth if you have an external auth provider like Clerk or Auth0.

Proposed Enhancement

Idea: Let developers input any sub or additional JWT claims, even if there’s no corresponding record in auth.users. Behind the scenes, this would do something like:

select set_config('role', 'authenticated', true);
select set_config('request.jwt.claims', '{"sub": "user_id_from_clerk"}'::text, true);

No mock user creation needed. You’d just paste your external user ID and any relevant claims (like org_id) into a new UI field in Studio.

Real-world Usage Example

1. Pull up Clerk’s dashboard to find your user’s ID (e.g., user_abc123).
2. In Supabase Studio, open an advanced impersonation dialog.
3. Enter sub = user_abc123, optionally add your custom claims as JSON.
4. Boom. RLS picks up those claims just like any normal JWT token.

The beauty of this approach is its simplicity: no configuration to save, no mock users to maintain, just quick, reliable testing with real user identities. Additionally, this approach is safe for production since we're not touching auth.users and can be extended to other auth providers like Auth0.

Example Use Cases

1. Quick RLS Testing

• User ID: user_123
• Additional JWT Claims: (none required)

2. Organization Based Access

• User ID: user_123

• Additional JWT Claims:

  {
    "app_metadata": {
      "org_id": "org_456"
    }
  }

3. Role Based Testing

• User ID: user_123

• Additional JWT Claims:

  {
    "app_metadata": {
      "role": "admin"
    }
  }

I hope this helps! Please feel free to share any thoughts or questions!