How to Build a Secure Paywall with Supabase RLS — Patterns and Pitfalls

Why RLS Is the Core of Any Paywall

The scariest scenario for a paid content service:

Someone who hasn't paid can see your content.

If you control access with if (purchased) { showContent() } on the client side, an attacker can bypass it by disabling JavaScript or hitting your API directly.

Even if you check on the server, accidentally exposing an API that fetches all users' purchase history will sink you.

Supabase Row Level Security (RLS) solves this at the database layer.

The database itself decides "this user can/cannot see this row" — so application-level bugs can't bypass it.

I use RLS on this blog's paid articles. Even if there's a bug in the Next.js access control logic, the database layer blocks it. Defense in depth.

What you'll learn:

• RLS concepts and 2-layer defense design
• RLS policy design for purchase history tables
• When to use Service Role Key vs. Anon Key
• How to bypass RLS correctly from Webhooks
• N+1-safe purchase check patterns
• Efficient RLS queries in Server Components

RLS Basics: Row-Level Access Control

Normal table permissions grant access to the entire table:

GRANT SELECT ON article_purchases TO authenticated;

This lets every authenticated user read everyone's purchase history.

RLS lets you write SQL that defines which rows a user can see:

ALTER TABLE article_purchases ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own purchases"
  ON article_purchases
  FOR SELECT
  USING (auth.uid() = user_id);

Now every query through Supabase automatically gets WHERE user_id = 'current_user_id' appended. Trying to fetch another user's purchases returns an empty result.

Design Pattern: Purchase History Table RLS

Base Table Definition

CREATE TABLE article_purchases (
  id TEXT PRIMARY KEY,                    -- Stripe Checkout Session ID
  user_id UUID REFERENCES auth.users(id),
  customer_email TEXT,
  article_slug TEXT NOT NULL,
  amount INTEGER NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'usd',
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Layered RLS Policies

Step 1: Enable RLS

ALTER TABLE article_purchases ENABLE ROW LEVEL SECURITY;

With RLS enabled but no policies defined, the default is deny all.

Step 2: SELECT policy

CREATE POLICY "Users can view own purchases"
  ON article_purchases
  FOR SELECT
  USING (auth.uid() = user_id);

Users can only read their own purchase history.

Step 3: Intentionally no INSERT/UPDATE policies

-- Do NOT create INSERT or UPDATE policies
-- This rejects all writes from the Anon Key

Design decision: Purchase records must never be written by the client. They're written via Webhook using the Service Role Key.

Using the Service Role Key Correctly

Anon Key vs. Service Role Key

The Service Role Key completely ignores RLS. Never expose it to the client.

Writing from a Webhook

// lib/supabase/server.ts
export async function createAdminClient() {
  const cookieStore = await cookies();
  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,  // ← Service Role Key
    {
      cookies: {
        getAll() { return cookieStore.getAll(); },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            );
          } catch {}
        },
      },
    }
  );
}

Inside your Webhook handler, use createAdminClient():

// app/api/webhooks/stripe/route.ts
const supabase = await createAdminClient();

await supabase.from("article_purchases").upsert({
  id: session.id,
  user_id: session.metadata?.user_id,
  article_slug: session.metadata?.article_slug,
  status: "completed",
});

This bypasses RLS and writes the record.

Critical Pitfall: Service Role Key Leakage

Any environment variable prefixed with NEXT_PUBLIC_ gets bundled into client JavaScript.

❌ Never do this:

NEXT_PUBLIC_SUPABASE_SERVICE_ROLE_KEY=eyJ...

✅ Correct:

SUPABASE_SERVICE_ROLE_KEY=eyJ...

Leaking the Service Role Key gives anyone full database access with no restrictions.

Purchase Check Implementation

Basic Pattern in a Server Component

// Article page (Server Component)
const supabase = await createClient();  // ← Anon Key (RLS applied)
const { data: { user } } = await supabase.auth.getUser();

if (!user) {
  return <Paywall />;
}

const { data: purchase } = await supabase
  .from('article_purchases')
  .select('id')
  .eq('user_id', user.id)
  .eq('article_slug', slug)
  .eq('status', 'completed')
  .single();

if (!purchase) {
  return <Paywall />;
}

return <FullArticle />;

Key points:

• createClient() uses the Anon Key → RLS is applied
• Explicitly write eq('user_id', user.id) (RLS handles it, but explicit is faster)
• .single() for existence check

Avoiding N+1 Queries

When showing a list of paid articles with "purchased" badges, you might be tempted to query each article individually.

❌ Bad (N+1):

const articles = getAllArticles();
const purchased = await Promise.all(
  articles.map(a =>
    supabase.from('article_purchases')
      .select('id')
      .eq('article_slug', a.slug)
      .single()
  )
);

This fires one query per article.

✅ Good (1 query):

const articles = getAllArticles();
const slugs = articles.filter(a => a.frontmatter.premium).map(a => a.slug);

const { data: purchases } = await supabase
  .from('article_purchases')
  .select('article_slug')
  .in('article_slug', slugs)
  .eq('status', 'completed');

const purchasedSet = new Set(purchases?.map(p => p.article_slug) ?? []);

// Use purchasedSet.has(slug) in your articles.map

One query with IN, O(1) lookup with Set.

Index Design

Purchase checks run on every article page load. Without proper indexes, queries slow down as purchases grow.

Recommended Index

-- Composite partial index on user_id + article_slug
CREATE INDEX idx_article_purchases_user_slug
  ON article_purchases(user_id, article_slug)
  WHERE status = 'completed';

Why:

• Composite index speeds up eq('user_id', x).eq('article_slug', y)
• Partial index (WHERE status = 'completed') excludes pending purchases, reducing index size

Verify with EXPLAIN

EXPLAIN ANALYZE
SELECT id FROM article_purchases
WHERE user_id = 'xxx' AND article_slug = 'yyy' AND status = 'completed';

You want to see Index Scan using idx_article_purchases_user_slug. If you see Seq Scan, the index isn't being used.

Caching Strategy

Using Next.js unstable_cache

Hitting the DB on every request is wasteful. Purchase checks for the same user can be cached for a few seconds.

import { unstable_cache } from 'next/cache';

const checkPurchase = unstable_cache(
  async (userId: string, slug: string) => {
    const supabase = await createClient();
    const { data } = await supabase
      .from('article_purchases')
      .select('id')
      .eq('user_id', userId)
      .eq('article_slug', slug)
      .eq('status', 'completed')
      .single();
    return !!data;
  },
  ['article-purchase-check'],
  { revalidate: 60, tags: ['article-purchase'] }  // 60s cache
);

Call revalidateTag('article-purchase') on purchase completion to invalidate immediately.

Testing RLS

Role Switching in Supabase SQL Editor

-- Run query as authenticated user A
SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claim.sub = 'user-A-uuid';

SELECT * FROM article_purchases;
-- → Only rows with user_id = 'user-A-uuid' appear

Verify with curl

# Try to fetch another user's purchases with Anon Key
curl "https://xxx.supabase.co/rest/v1/article_purchases?user_id=eq.OTHER_USER_ID" \
  -H "apikey: ANON_KEY" \
  -H "Authorization: Bearer USER_A_JWT"
# → []

If RLS is working, another user's data returns empty.

Production Checklist

• [ ] ALTER TABLE ... ENABLE ROW LEVEL SECURITY on all tables
• [ ] SELECT policies defined with minimum permissions
• [ ] No INSERT/UPDATE/DELETE policies (or strictly restricted)
• [ ] Service Role Key stored without NEXT_PUBLIC_ prefix
• [ ] Service Role Key only used server-side (API Routes, Server Actions, Webhooks)
• [ ] Composite + partial index created for purchase checks
• [ ] Visually confirm RLS is enabled on all tables in Supabase Dashboard → Policies tab

Summary

RLS is a mindset shift: defense at the database layer.

Even if application-layer checks are bypassed, the database stops it. Defense in depth — code bugs and misconfigurations don't leak data.

If you're handling paid content, RLS isn't "nice to have" — it's mandatory.

The design principles:

1. Default deny: Enable RLS first, then add minimum policies
2. Writes via Service Role Key: Never let clients write purchase records
3. Don't forget indexes: 80% of slow RLS queries are missing indexes

Implement this and your "non-paying users seeing paid content" risk drops to near zero.

Building a Next.js + Supabase + Stripe SaaS from scratch? I've packaged this RLS pattern — along with auth, payments, and subscription management — into LaunchKit, a production-ready boilerplate for indie developers.