DEV Community

FreezeOrange
FreezeOrange

Posted on

How to Actually Use Supabase RLS With Next.js App Router (Without Losing Your Mind)

#webdev #startup #javascript #tutorial

Row Level Security (RLS) is one of Supabase's most powerful features — and one of the most misunderstood. If you've ever written a Supabase policy that works in the dashboard but fails silently in your Next.js app, this post is for you.

I'm building Wishyze, an AI-powered affirmation platform. We run on Next.js 14, Supabase (Auth + Postgres), and have around 28,000 users. RLS is the backbone of our data model — every user's rituals, streaks, and preferences are isolated at the database level. Here's everything I learned the hard way.

Why RLS Matters (Even for Solo Devs)

The pitch is simple: instead of remembering to add userId checks in every API route and every query, you write policies once in Postgres and the database enforces access control automatically.

Sounds great. In practice, there are three things that trip people up:

  1. RLS is disabled by default on new Supabase tables
  2. The anon key bypasses RLS unless you're authenticated
  3. Server-side vs client-side clients behave differently with auth.uid()

Let's fix all three.

Step 1: Enable RLS on Every Table

This is the most common mistake. You create a table, write policies, and nothing happens because RLS was never turned on.

ALTER TABLE rituals ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_preferences ENABLE ROW LEVEL SECURITY;
ALTER TABLE streak_data ENABLE ROW LEVEL SECURITY;
Enter fullscreen mode Exit fullscreen mode

In the Supabase dashboard, there's a toggle for this. Use it.

Step 2: Write Policies That Actually Work

Here's a typical pattern for a user's own data:

-- Users can only see their own rituals
CREATE POLICY "Users can view own rituals"
  ON rituals
  FOR SELECT
  USING (auth.uid() = user_id);

-- Users can insert their own rituals
CREATE POLICY "Users can create own rituals"
  ON rituals
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Users can update their own rituals
CREATE POLICY "Users can update own rituals"
  ON rituals
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);
Enter fullscreen mode Exit fullscreen mode

The key distinction is USING vs WITH CHECK:

  • USING determines which rows you can see (SELECT, UPDATE, DELETE)
  • WITH CHECK determines what values you can write (INSERT, UPDATE)

For most apps where users own their data, USING and WITH CHECK are identical.

Step 3: The Server Client Pattern

Here's where Next.js App Router and Supabase get interesting. You need two different Supabase clients:

// app/lib/supabase/server.ts
import { createClient } from "@supabase/supabase-js";
import { cookies } from "next/headers";

export async function createServerClient() {
  const cookieStore = await cookies();

  return createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return cookieStore.getAll();
        },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            );
          } catch {
            // Called from a Server Component — ignore
          }
        },
      },
    }
  );
}
Enter fullscreen mode Exit fullscreen mode

This client reads the user's session from cookies. When you call supabase.auth.getUser() through this client, it verifies the JWT against your Supabase project and auth.uid() in your RLS policies resolves correctly.

// app/api/rituals/route.ts
import { createServerClient } from "@/app/lib/supabase/server";
import { NextResponse } from "next/server";

export async function GET() {
  const supabase = await createServerClient();
  const { data: { user }, error } = await supabase.auth.getUser();

  if (error || !user) {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }

  // RLS handles the rest — only this user's rituals come back
  const { data, error: queryError } = await supabase
    .from("rituals")
    .select("*")
    .order("created_at", { ascending: false });

  if (queryError) {
    return NextResponse.json({ error: queryError.message }, { status: 500 });
  }

  return NextResponse.json({ rituals: data });
}
Enter fullscreen mode Exit fullscreen mode

Notice what we're not doing: there's no WHERE user_id = auth.uid() in the query. The RLS policy handles that automatically. This is the whole point.

Step 4: The Service Role Client (Use Carefully)

Sometimes you need to bypass RLS — admin operations, background jobs, webhooks. That's what the service role key is for:

// app/lib/supabase/admin.ts
import { createClient } from "@supabase/supabase-js";

// NEVER expose this key to the client
export function createAdminClient() {
  return createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,
    {
      auth: {
        autoRefreshToken: false,
        persistSession: false,
      },
    }
  );
}
Enter fullscreen mode Exit fullscreen mode

Rules I follow:

  • This file lives only in app/lib/supabase/ — never imported by any client component
  • We use it exclusively in app/api/ routes that handle Paddle webhooks or background syncs
  • Every usage has a comment explaining why RLS bypass is needed

Common Gotchas I've Hit in Production

"My query returns everything"

RLS is off. Double-check:

SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = public;
Enter fullscreen mode Exit fullscreen mode

If rowsecurity is f, you need to ENABLE ROW LEVEL SECURITY on that table.

"auth.uid() returns null in my API route"

You're probably using the anon client instead of the server client. The server client reads cookies and resolves the JWT. The anon client doesn't have access to the user's session unless you explicitly pass a Bearer token.

"RLS works in development but breaks in production"

Check your RLS policies use auth.uid(), not auth.jwt()->>sub. The former works with both JWT verification methods. The latter can fail if your Supabase project uses a different JWT secret than expected.

"My Supabase realtime subscription shows no data"

Realtime is also affected by RLS policies. Make sure your SELECT policy allows the authenticated user to see the rows you're trying to subscribe to.

The Policy Patterns We Use in Production

After 28,000 users and a year of iteration, here's what our policy layer looks like:

users table       → Users can only access their own profile
rituals table     → Users can CRUD their own rituals
streak_data table → Users can read/update their own streaks
premium_status    → Service role only (managed by Paddle webhook)
Enter fullscreen mode Exit fullscreen mode

For the premium_status table specifically, we use a policy that only allows the service role:

CREATE POLICY "Service role only for premium status"
  ON premium_status
  FOR ALL
  USING (false);
Enter fullscreen mode Exit fullscreen mode

The service role bypasses RLS entirely, so the webhook handler can insert/update freely. Regular users see nothing. This is cleaner than trying to write permissive policies and accidentally exposing payment data.

Key Takeaways

  1. Enable RLS on every table — don't rely on "I'll remember"
  2. Write INSERT, SELECT, and UPDATE policies separately — the defaults are permissive and confusing
  3. Use the server client pattern — it's the only way auth.uid() works in App Router API routes
  4. Guard the service role key — treat it like a database root password
  5. Test policies with SET ROLE in SQL — simulate different users before deploying

RLS adds a small amount of overhead to each query, but the safety net is worth it. Once your policies are solid, you can refactor your frontend, add new API routes, or even open-source your Supabase project without worrying about data leaks.

If you're building something that needs per-user data isolation, give Supabase RLS a try. It's one of those features that feels like overkill until you need it — and then you can't imagine building without it.

If you want to see how we've put this pattern to use in production, check out wishyze.com.

Top comments (1)

Collapse
 
topstar_ai profile image
Luis •

Great write-up. Supabase RLS is one of those features that looks simple initially but requires a real shift in thinking compared to traditional backend authorization.

The biggest lesson is that authorization logic should live as close as possible to the data layer. If every API route manually checks ownership and permissions, it becomes easy to introduce inconsistencies as the application grows.

A few production patterns that help:

Design tables and relationships with RLS policies in mind from the beginning
Test policies with different user contexts, not only the happy path
Keep authentication (who are you?) separate from authorization (what can you access?)
Avoid accidentally exposing service-role capabilities to client code
Add automated tests for critical permission boundaries

For multi-tenant SaaS applications, RLS can become a powerful security foundation because the database itself enforces isolation.

The challenging part is not writing the first policy — it’s maintaining clear authorization rules as business requirements become more complex.

Great explanation of the real-world pain points with Next.js App Router + Supabase. Security layers that are invisible when everything works are usually the ones that matter most when systems scale. 👏