Using Service Role with Supabase in Next.js Backend - Which Approach is Valid?

[ganeshrvel]

I'm new to Supabase.
I'm working on a Next.js application with Supabase and want to handle everything through the Next.js backend using service role. I'm comparing two approaches and need help understanding which is valid/secure.

For both approaches, I start with:

revoke all on schema public from anon; 
revoke all on schema public from authenticated;

Approach 1: Manual User ID Filtering

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(supabaseUrl, serviceRoleSecret, {
  auth: {
    persistSession: false,
    autoRefreshToken: false,
    detectSessionInUrl: false,
  },
});

// Manually filter by user_id in every query
const { data, error } = await supabase
  .from('accounts')
  .select('*')
  .eq('user_id', user.id); // Manually attach user_id

Approach 2: Service Role with JWT and RLS

// Get user session
import { createServerClient } from "@supabase/ssr";
import { cookies } from "next/headers";

export async function createClient() {
  const cookieStore = await cookies()
  
  return createServerClient(
    SUPABASE_URL,
    SUPABASE_ANON_KEY,
    {
      cookies: {
        getAll() {
          return cookieStore.getAll()
        },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({name, value, options}) =>
              cookieStore.set(name, value, options)
            )
          } catch {
          }
        },
      },
    }
  )
}

// Get session token
const { data: { session: sessionToken } } = await supabase.auth.getSession()

// Create service role client with session token
const supabase = createClient(supabaseUrl, serviceRoleSecret, {
  auth: {
    persistSession: false,
    autoRefreshToken: false,
    detectSessionInUrl: false,
  },
  global: {
    headers: {
      Authorization: `Bearer ${sessionToken}`
    }
  }
});

// In Supabase:
CREATE POLICY "service_role can access authenticated user data"
    ON public.your_table
    AS RESTRICTIVE
    FOR ALL
    TO service_role
    USING (auth.uid() = user_id);

Questions:

1. Is Approach 2 even possible? Can you use auth.uid() = user_id with service role when sessionToken is passed?
   • I would appreciate if someone can point me to an article which talks about this setup.
2. Is there a better way to handle this? All examples I see use authenticated clients, but I want everything through the backend.
3. Is Approach 1 the only way here?

I'm trying to understand the proper way to handle this for a production application. Looking for insights on security implications and best practices.

Thanks in advance!

[GaryAustin1]

Approach 2 is not possible. The authorization header is what decides if the role is service role. You also cannot use SSR clients with service role as the cookie would replace the default role with a user session.

Approach 1 with a supabase-js is the main way.

You could potentially create a new Postgres role and mint your own JWT with the jwt secret on the fly with sub UUID and role as server_role and still use auth.uid() in RLS but with a server_role role that obeys RLS. You would have to grant that role to authenticator and all your schemas, tables and functions.

[GaryAustin1]

Can you clarify if you went with minting the JWT or just service_role and filters.

[ganeshrvel]

I just used the service roles. Minted a jwt, sent it to supabase. It worked. The only concern is that I have to call the getUser() api on every server request to mint the jwt, which is affecting the performance.

[GaryAustin1]

Yes. You would have to do that even if you went with service_role and did not mint and just used a filter also as you need to verify the jwt.
Technically you could verify the jwt though to your API with the same jwt library much faster than calling getUser(). The only thing you would not know is if the user is signed out or deleted after they got their jwt as the jwt sent to your API is good until it expires.

[ganeshrvel]

Thanks for the suggestion.

I think someone has made an attempt to solve this extra api call issue: https://github.com/butttons/supabase-auth-helpers

What do you think of it?

[GaryAustin1]

Sorry, I'm not going to search thru their repository to see what they do. I assume as they have the jwt_secret they are going to verify the jwt is signed and not expired. Which is what I suggested. That is also done by Supabase here: https://supabase.com/docs/guides/database/postgres/custom-claims-and-role-based-access-control-rbac#accessing-custom-claims-in-your-application