Optimizing Supabase Row Level Security: Lessons from the Trenches

Introduction

In my journey to build a secure, scalable API using Supabase, I encountered several challenges with Row Level Security (RLS) that required thoughtful optimization. This post shares my experience taming complex RLS policies, fixing security vulnerabilities, and boosting database performance. Whether you’re just starting with Supabase or looking to optimize an existing implementation, these insights should help you build more secure and efficient applications.

The RLS Complexity Problem

When I first implemented Row Level Security in my application, I followed a common pattern: creating specific policies for different user roles and operations. Over time, this led to a proliferation of overlapping policies, each granting access in slightly different ways.

-- Initial approach: Multiple specific policies
CREATE POLICY "Employees can view their own data" ON companies
  FOR SELECT USING (auth.uid() = employee_id);

CREATE POLICY "Admins can view all data" ON companies
FOR SELECT USING (auth.role() = 'admin');

CREATE POLICY "Managers can view department data" ON companies
FOR SELECT USING (auth.jwt() -> 'manager_of' ? department_id::text);

The problem? PostgreSQL evaluates these policies using OR logic – meaning if any policy grants access, the row is accessible. This leads to two significant issues:

1. Performance degradation: Each policy requires evaluation for every row
2. Security confusion: The interaction between multiple policies becomes difficult to reason about

The Consolidated Policy Approach

My solution was to consolidate these policies into a single, comprehensive policy per table:

-- Optimized approach: One comprehensive policy
CREATE POLICY all_authenticated_policy ON companies
  USING (
    auth.role() = 'authenticated' AND (
      auth.uid() = employee_id OR
      auth.role() = 'admin' OR
      auth.jwt() -> 'manager_of' ? department_id::text
    )
  );

This approach resulted in:

• ~80% reduction in query time for tables with complex access patterns
• Simpler security reasoning with all logic in one place
• Easier maintenance when updating access rules

JWT Structure: The Key to RLS Success

One of the most interesting challenges I faced was that my app serves as a white-label platform. That means other apps (with their own authentication systems!) mee my backend, and I needed to make sure their users could be recognized and authorized by Supabase’s RLS.

This meant I had to bridge the gap between external auth systems and Supabase’s expectations. Supabase RLS is super picky about JWT claims, so I had to make sure every token—no matter where it came from—had the right structure.

Here’s how I build my Supabase tokens (with a little help from jwt.sign):

function createSupabaseToken(user) {
  return jwt.sign(
    {
      aud: "authenticated", // Required for RLS policies
      role: "authenticated", // Must match policy requirements
      sub: user.supabase_uuid, // Must be a valid UUID
      email: user.email,
      // Custom claims for additional RLS checks
      user_metadata: {
        original_id: user.legacy_id,
        company_id: user.company_id,
      },
    },
    process.env.JWT_SECRET
  );
}

Pro tip: Always include aud: "authenticated" and role: "authenticated" in your tokens. Otherwise, your RLS policies might just ignore you.

Security Deep Dive: Function Search Paths

During my security audit, I discovered numerous “Function Search Path Mutable” warnings in Supabase. These seemingly minor warnings actually represent significant security vulnerabilities.

The Risk

When PostgreSQL functions don’t specify a search path, they inherit it from the calling context – potentially allowing SQL injection attacks through path manipulation.

The Solution

I created a script to automatically set explicit search paths for all functions:

DO $$
DECLARE
  func_record record;
BEGIN
  FOR func_record IN
    SELECT
      n.nspname as schema_name,
      p.proname as function_name
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'public'
    AND pg_get_functiondef(p.oid) NOT ILIKE '%SET search_path%'
  LOOP
    EXECUTE format(
      'ALTER FUNCTION %I.%I SET search_path = public, pg_temp',
      func_record.schema_name,
      func_record.function_name
    );
  END LOOP;
END $$;

This script remediated over 20 potential security vulnerabilities in my database.

Performance Tuning: Eliminating Duplicate Indexes

During my optimization efforts, I discovered several duplicate indexes that were degrading write performance.

-- Example of duplicate indexes I found
Table public.employee_schedules has identical indexes:
  - employee_schedules_employee_id_key
  - uniqueemployeeschedules_employeeid

These duplicates occurred when:

• Unique constraints were added (creating automatic indexes)
• Manual indexes were created with different naming conventions
• Schema migrations were performed without checking existing indexes

My cleanup script removed these redundancies:

DO $$
BEGIN
  DROP INDEX IF EXISTS public.uniqueemployeeschedules_employeeid;
  DROP INDEX IF EXISTS public.uniqueinbox_employeeid;
  -- Additional indexes...
END $$;

The results were immediate: faster write operations and reduced database size.

Bridging Two Worlds: Authentication Sync

Because my app is a white-label platform, I needed to support clients who bring their own authentication systems. The challenge? Making sure users from these external systems could be mapped to Supabase users—so RLS could do its job.

I ended up writing a helper that syncs users from any external auth system (not just my legacy one!) into Supabase. This way, every user—no matter where they log in—gets a Supabase UUID and the right metadata. Here’s how I did it:

async function syncExternalUserWithSupabase(adminSupabase, externalUser) {
  try {
    // First try to find user by email
    const { data: existingUsers } = await adminSupabase
      .from("users")
      .select("id")
      .eq("email", externalUser.email)
      .maybeSingle();

    let supabaseUuid;

    if (existingUsers) {
      // Use existing user
      supabaseUuid = existingUsers.id;
    } else {
      // Create new user
      const { data, error } = await adminSupabase.auth.admin.createUser({
        email: externalUser.email,
        password: generateSecurePassword(),
        user_metadata: { original_id: externalUser.id },
      });

      if (error) throw error;
      supabaseUuid = data.user.id;
    }

    // Update employee record with Supabase UUID
    await updateEmployeeRecord(externalUser.id, supabaseUuid);

    return supabaseUuid;
  } catch (error) {
    logger.error("User sync error:", error);
    throw error;
  }
}

This approach keeps everything in sync, so Supabase RLS can recognize users from any connected app. It’s a little extra work, but it makes the platform truly flexible for different clients.

Best Practices and Lessons Learned

Based on my experience, here are the key takeaways for effective Supabase RLS implementation:

1. Consolidate RLS policies: One well-structured policy per table is better than multiple overlapping ones
2. Structure JWT tokens correctly: Always include aud and role claims for proper RLS functionality
3. Set explicit search paths: Protect against SQL injection by setting function search paths
4. Monitor for duplicate indexes: Regular database maintenance should include index deduplication
5. Automate security checks: Build scripts to scan for and fix common security issues
6. Test RLS thoroughly: Verify policy behavior with different user roles and edge cases

Conclusion

Optimizing Row Level Security in Supabase requires attention to policy structure, JWT configuration, function security, and database performance. By addressing these areas systematically, I significantly improved both security and performance in my application.

The effort invested in RLS optimization pays dividends in reduced technical debt, improved scalability, and enhanced security posture. For any team building serious applications on Supabase, I highly recommend making RLS optimization a priority.

---

Have you tackled RLS optimization in your Supabase projects? You can hit me on my social media to discuss

P.S. Follow me on Twitter & Threads. I like to share or reshare info there as well.

Wrap it up

That’s all for now. See you next time!

---

Your Engineer Friend