Supabase Row Level Security: A Practical Setup Guide for Next.js

Row Level Security is what separates a Supabase database that is secure from one that only appears secure — here is the real policy structure and its failure modes.

Row Level Security is the feature that separates a Supabase database that is secure from one that only appears secure. Without RLS, any user who obtains the anon key — which is public and embedded in every Next.js client bundle — can read and write any row in any table.

This is what RLS is, how we set it up for ShabelleHub, the specific mistakes that are easy to make, and — the part that's easy to miss — how it can fail *silently* in both directions.

What RLS Does

By default, a Supabase table with RLS disabled allows any request with a valid API key to read and write any row. The anon key is safe for public read access to public data — but not for any table containing user data, draft content, or admin-only records.

RLS adds a per-row filter to every query. When a user makes a request, Supabase evaluates a policy against that user's JWT before returning any data. The policy is a SQL expression:

-- Allow users to read only their own profile
CREATE POLICY "Users can view own profile"
ON profiles FOR SELECT
USING (auth.uid() = id);

auth.uid() returns the UUID of the authenticated user from their JWT. The policy allows a SELECT only when the row's id matches the requesting user's ID. A user cannot read another user's profile row — even with a valid anon key.

The Default State That Surprises People

Enabling RLS on a table and stopping there does not leave the table half-open — it blocks all access to that table entirely, including from your own backend, unless you're using the service role key:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
-- No policies yet: every request via anon or authenticated keys now returns zero rows

A table with RLS enabled but no policies denies all access — no rows are returned, no rows can be written. This is the *opposite* of the mistake most people expect. The danger isn't forgetting to lock a table down; it's enabling RLS and then not writing the policy you actually need, so a feature just silently returns empty results instead of erroring. We hit this directly the first time we enabled RLS on a new table mid-migration — the admin panel didn't crash, it just quietly showed zero posts.

Enabling RLS Across the Schema

RLS must be explicitly enabled on each table individually. Creating a table does not enable RLS automatically:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE tags ENABLE ROW LEVEL SECURITY;
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE media ENABLE ROW LEVEL SECURITY;

ShabelleHub's Policy Structure

The CMS has two roles: editor and admin. The policies reflect what each role should be able to do.

Public Read Access

CREATE POLICY "Public can read published posts"
ON posts FOR SELECT
USING (published = true);

This policy applies to unauthenticated requests (where auth.uid() returns null). Any request can read rows where published = true. Unpublished drafts are not returned.

Editors Can Read All Posts

CREATE POLICY "Editors can read all posts"
ON posts FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role IN ('editor', 'admin')
  )
);

This policy uses a subquery against the profiles table to check the requesting user's role. If the user has editor or admin role, they can read all posts regardless of published status.

Editors Can Edit Their Own Posts

CREATE POLICY "Editors can update own posts"
ON posts FOR UPDATE
USING (
  author_id = auth.uid()
  AND EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role IN ('editor', 'admin')
  )
);

Two conditions must both be true: the post's author_id must match the requesting user's ID, and the user must have an editor or admin role. An editor cannot update another editor's posts.

Admins Can Edit Any Post

CREATE POLICY "Admins can update any post"
ON posts FOR UPDATE
USING (
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role = 'admin'
  )
);

Admins bypass the author_id check. They can edit any post.

Profiles Table

-- Anyone can read profiles (for author pages)
CREATE POLICY "Public can read profiles"
ON profiles FOR SELECT
USING (true);

-- Users can update only their own profile
CREATE POLICY "Users can update own profile"
ON profiles FOR UPDATE
USING (auth.uid() = id);

-- Only admins can change roles
CREATE POLICY "Admins can update any profile"
ON profiles FOR UPDATE
USING (
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role = 'admin'
  )
);

The role-change protection is critical: without it, any authenticated user could update their own role field to admin. The policy restricts UPDATE on profiles to the user's own row, except for admins who can update any row.

Why the Role Check Is a Subquery, Not a JWT Claim

It's simpler to put a role directly in the Supabase Auth user's JWT claims and check it with auth.jwt() -> 'role' inside the policy. That works, but changing someone's role then requires a separate admin API call to update auth metadata, and roles become harder to track since they're not visible in a normal table you can query and audit with SQL.

Keeping roles in a plain profiles table that the policy subquery checks against means role management is just normal row data — visible, editable, and auditable through the same SQL tooling as everything else in the database, at the cost of one extra query per policy check. For ShabelleHub, that trade-off was worth it: being able to run SELECT id, role FROM profiles WHERE role = 'admin' directly is worth more than the marginal query cost.

The Service Role Bypass

API routes that need to bypass RLS — for admin operations performed server-side — use the service role key:

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

export function createAdminClient() {
  return createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL,
    process.env.SUPABASE_SERVICE_ROLE_KEY // never expose this to the browser
  );
}

The service role key bypasses RLS entirely. It must only be used in server-side code — API routes, getStaticProps, getServerSideProps. It must never appear in client-side code or be prefixed with NEXT_PUBLIC_.

A check in scripts/check-env-leakage.js runs as the prebuild step and fails the build if the service role key appears in any client-accessible file:

// scripts/check-env-leakage.js
const fs = require('fs');
const path = require('path');

const SERVICE_ROLE_PATTERN = /SUPABASE_SERVICE_ROLE_KEY/;

function scanDir(dir) {
  const entries = fs.readdirSync(dir, { withFileTypes: true });
  for (const entry of entries) {
    if (entry.name === 'node_modules' || entry.name === '.next') continue;
    const fullPath = path.join(dir, entry.name);
    if (entry.isDirectory()) {
      scanDir(fullPath);
    } else if (entry.name.endsWith('.js') || entry.name.endsWith('.jsx')) {
      const content = fs.readFileSync(fullPath, 'utf8');
      if (SERVICE_ROLE_PATTERN.test(content) && !fullPath.includes('api/') && !fullPath.includes('lib/supabaseAdmin')) {
        console.error(`❌ Service role key reference found in client-accessible file: ${fullPath}`);
        process.exit(1);
      }
    }
  }
}

scanDir('./');
console.log('✓ No admin secret leakage detected.');

This runs before every build. If a developer accidentally references the service role key in a page component or client utility, the build fails before it can deploy.

The Mistake That's Invisible Until You Specifically Test For It

A policy that grants SELECT to admins on posts but forgets to also grant it on a table joined into that query — say, an authors table joined for the byline — fails silently in a way that's easy to misdiagnose: the query returns fewer rows than expected, or joined fields come back null, with no error at all. RLS filtering doesn't throw exceptions — it just excludes rows that don't pass the policy on *any* table touched by the query, including joined ones.

This is a distinct failure mode from the "blocks everything" default state above: this one only shows up once a working query is later extended to join a new table, and the symptom looks exactly like a data problem, not a permissions problem.

Common RLS Mistakes

Forgetting to enable RLS on new tables. Every new table must have ALTER TABLE x ENABLE ROW LEVEL SECURITY run before it is safe to use. We added this as the first migration step whenever a new table is created.

Writing policies that conflict. Supabase evaluates all applicable policies for a given operation and returns rows where any policy passes. Two conflicting policies do not cancel each other out — the more permissive one wins. If a public read policy and an authenticated-only policy both exist for SELECT, unauthenticated users can read via the public policy.

Using the service role key in getStaticProps. getStaticProps runs at build time on the server — using the service role is safe here. But the result of getStaticProps is serialised into the page's JSON and sent to the browser. If you accidentally include a service role key in the returned props, it ships to the client.

Not testing RLS with the actual role your app uses. The easiest way to verify RLS is working is to create a Supabase client with only the anon key and attempt to query tables that should be restricted — or, directly in the Supabase SQL editor, run set role authenticated; before the query. Testing as the database owner bypasses RLS entirely and will show data a real request never would.

Testing the Policies

// Test script — run with node, never ship to production
const { createClient } = require('@supabase/supabase-js');

const anon = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY
);

async function testRLS() {
  // Should return only published posts
  const { data: posts } = await anon.from('posts').select('id, title, published');
  console.log('Unpublished posts visible to anon:', posts?.filter(p => !p.published).length);
  // Expected: 0

  // Should return empty or error — anon cannot see profile roles
  const { data: profiles } = await anon.from('profiles').select('id, role');
  console.log('Profile roles visible to anon:', profiles?.length);
  // Expected: profiles visible (public read) but role column exposure depends on policy
}

testRLS();

Running this against the production database (read-only anon client) confirms that RLS policies behave as expected before any content is added.

The Result

ShabelleHub's database has RLS enabled on every table. Unauthenticated users can read published posts and public profiles. Editors can manage their own content. Admins can manage everything. The service role key is server-only and checked by the prebuild script.

The policies are SQL — readable, version-controlled alongside the schema migrations, and testable with a standard Supabase client. RLS policies are not a "set it once" feature: every new table that gets joined into an existing query needs its own policy considered explicitly, since a working query today can start silently dropping rows the moment it's extended to join a table nobody wrote a policy for.

RLS is the feature that makes it safe to expose Supabase's API directly to the browser. Without it, the anon key is a liability. With it, the anon key is a feature.

Frequently Asked Questions

What happens if you enable RLS on a Supabase table and add no policies?

Every request using the anon or authenticated key returns zero rows, with no error — not a security hole, but a feature that silently appears broken until a policy is written.

Should admin roles be stored in Supabase Auth JWT claims or a separate table?

A plain profiles/admin_users table checked via a policy subquery keeps role management as normal, auditable row data queryable with SQL, at the cost of one extra query per policy check — versus JWT claims, which require a separate admin API call to update.

Why would a query return fewer rows than expected with no error at all?

RLS filtering doesn't throw exceptions — it silently excludes rows that don't pass a policy. This is especially easy to miss on joined tables where the joined table has no policy covering the current role.

How do you actually test whether an RLS policy works as intended?

Run the exact query in the Supabase SQL editor as the specific role your app uses (set role authenticated;) — not as the database owner, which bypasses RLS entirely and will show data a real request never would.

*This article is part of the ShabelleHub Building in Public series.*

Frequently Asked Questions

📬 Get the latest AI tool reviews

Expert picks and comparisons, weekly. No spam.