Supabase RLS Pitfalls: When Row-Level Security Fights Your App

I spent three days debugging why a new user couldn't create their first booking. The insert kept failing silently. No errors, no logs, just... nothing. The row didn't appear.

Turns out, the RLS policy on the bookings table required the user to be the owner of the booking. But the user ID gets set during the insert. The policy was checking ownership of a row that didn't exist yet. Classic chicken-and-egg.

Welcome to Supabase RLS.

What RLS Actually Does

Row-Level Security is a PostgreSQL feature that Supabase wraps with a nice UI. You define policies that control which rows a user can SELECT, INSERT, UPDATE, or DELETE. These policies run at the database level — your application code never sees rows the user isn't allowed to access.

-- Only allow users to see their own bookings
CREATE POLICY "Users can view own bookings"
ON bookings FOR SELECT
USING (auth.uid() = user_id);

-- Only allow users to insert bookings for themselves
CREATE POLICY "Users can create own bookings"
ON bookings FOR INSERT
WITH CHECK (auth.uid() = user_id);

The appeal is obvious: even if your application code has a bug that forgets an authorisation check, the database won't return unauthorised data. Defence in depth.

Where It Goes Wrong

Problem 1: The Insert Chicken-and-Egg

When inserting a new row, RLS evaluates the WITH CHECK policy against the row being inserted. This means you need to set the user_id (or whatever your ownership column is) in the insert payload, and it must match the authenticated user.

Sounds straightforward until you have a table with a foreign key that references another table the user doesn't own yet:

-- venues table: only venue owners can insert
CREATE POLICY "Owners can insert venues"
ON venues FOR INSERT
WITH CHECK (auth.uid() = owner_id);

-- bookings table: must reference a valid venue
CREATE POLICY "Users can create bookings"
ON bookings FOR INSERT
WITH CHECK (auth.uid() = user_id);

User creates a booking. The booking references a venue. The user doesn't own the venue — they're a customer. But the booking insert needs to reference the venue's ID. Do you need a SELECT policy on venues that lets everyone read? Maybe. But then you've loosened your venue security for every other operation.

Problem 2: Computed or Default Values

If your table has a trigger that sets values (like a created_by column set by a trigger), the RLS policy evaluates before the trigger runs. So you can't rely on database-side defaults for ownership columns — the application must explicitly set them.

// This won't work if you're relying on a trigger to set user_id
const { error } = await supabase
    .from('bookings')
    .insert({ venue_id: venueId, date: selectedDate });

// You must explicitly pass the user ID
const { error } = await supabase
    .from('bookings')
    .insert({
        venue_id: venueId,
        date: selectedDate,
        user_id: user.id,  // Must be explicit
    });

Problem 3: Cross-Table Operations

Business logic often spans multiple tables. "Accept a booking" might update the bookings table, create a payment record, and send a notification. With RLS, each table has its own policies, and there's no concept of a transaction-level authorisation context.

The venue owner accepting a booking needs UPDATE on bookings (which they can do as the venue owner), INSERT on payments (which might require being the booking's user), and INSERT on notifications (which... whose notification is it?).

You end up either making policies so permissive they defeat the purpose, or you bypass RLS entirely for complex operations.

The createServiceClient() Escape Hatch

Supabase provides two client types:

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

// Regular client — respects RLS, uses the user's JWT
const supabase = createClient(url, anonKey);

// Service client — bypasses RLS entirely, uses the service role key
const supabaseAdmin = createClient(url, serviceRoleKey);

The service client is the equivalent of a database superuser. RLS policies don't apply. It can read, write, and delete anything.

On the Rezzy project, I ended up with a pattern where the Next.js frontend uses the regular client for reads (RLS handles scoping), but API routes use the service client for writes:

// app/api/bookings/route.ts

import { createServiceClient } from '@/lib/supabase/service';
import { createClient } from '@/lib/supabase/server';

export async function POST(request: Request) {
    const supabase = await createClient();

    // Verify the user is authenticated (regular client)
    const { data: { user }, error: authError } = await supabase.auth.getUser();

    if (!user) {
        return Response.json({ error: 'Unauthorized' }, { status: 401 });
    }

    const body = await request.json();

    // Use service client for the insert (bypass RLS)
    const admin = createServiceClient();

    const { data: booking, error } = await admin
        .from('bookings')
        .insert({
            user_id: user.id,
            venue_id: body.venueId,
            date: body.date,
            status: 'pending',
        })
        .select()
        .single();

    if (error) {
        return Response.json({ error: error.message }, { status: 400 });
    }

    return Response.json(booking);
}

Notice what happened there? We've essentially moved authorisation back to the application layer. We check auth manually, validate the input, and bypass RLS for the write. The "database-level security" promise is reduced to read-side filtering.

When RLS Is Actually Good

To be fair, RLS genuinely shines in specific scenarios:

Multi-tenant data isolation for reads. If every table has a tenant_id and your RLS policy is USING (auth.jwt() ->> 'tenant_id' = tenant_id::text), you get automatic tenant scoping on every SELECT. A developer can't accidentally forget a WHERE clause. That's valuable.

Simple ownership models. If your app is like a todo list where users only ever see their own data, RLS policies are straightforward and effective:

CREATE POLICY "Own data only" ON todos
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

Direct client-to-database access. If you're using Supabase's client library directly from a mobile app or SPA with no backend, RLS is your only authorisation layer. It's better than nothing, and for simple apps it's enough.

When RLS Is a Nightmare

Complex business logic. Anything involving multiple tables, conditional access, role-based permissions, or temporal logic (this user can access this resource until the booking expires) is painful to express in SQL policies.

Admin operations. Admin dashboards need to see everything. You end up with CREATE POLICY "Admins see all" ON bookings USING (is_admin(auth.uid())) and a function that checks a role in a separate table. It works but it's fragile.

Testing. Testing RLS policies means testing at the database level. You can't unit test a SQL policy the way you can unit test a Laravel Policy class. You end up writing integration tests that authenticate as different users and verify query results.

The Comparison with Laravel Policies

Having built similar apps in both approaches, here's my honest take:

Laravel Policies are application-level. They're PHP classes you can test, debug, and reason about with standard tools. They compose with the rest of your application. The downside is that a developer can forget to check authorisation.

Supabase RLS is database-level. It can't be bypassed by application bugs (unless you use the service client). The downside is that complex logic is awkward, testing is harder, and you'll end up bypassing it for writes anyway.

For apps with complex business logic — marketplaces, booking systems, anything with roles and multi-step workflows — I'd pick Laravel Policies every time. For simpler apps where the client talks directly to the database, RLS is pragmatic.

My Advice

If you're using Supabase, use RLS for read-side scoping and keep your policies simple. For writes and complex operations, use server-side API routes with the service client and handle authorisation in application code.

Don't try to encode your entire authorisation model in SQL policies. You'll end up with 30 policies per table, half of them with WITH CHECK (true), and a codebase where nobody can confidently answer "who can do what?"

Database-level security is brilliant in principle. In practice, business logic doesn't fit neatly into SQL predicates.

---

Building a SaaS product? I can help with architecture, payments, and the hard bits.