How to Properly Secure Supabase Row-Level Security

Supabase's Row-Level Security (RLS) is a powerful PostgreSQL feature that controls which rows a user can read, insert, update, or delete. When configured correctly, it provides a strong last line of defense — even if your API layer has bugs. When misconfigured, it leaves your entire database exposed to any authenticated user.

VibeShield's Supabase scanner checks for the most common RLS mistakes. This post explains what we look for, how RLS works under the hood, and how to configure it for production applications.

How Supabase RLS Works Under the Hood

RLS is a native PostgreSQL feature, not something Supabase invented. When you enable RLS on a table, PostgreSQL appends a WHERE clause to every query against that table — automatically, at the database engine level — based on the policies you define.

Supabase layers its authentication system on top of this by injecting JWT claims into the PostgreSQL session context. When a client makes a request using the Supabase JS SDK, the JWT from the user's session is decoded and its claims are made available to RLS policies through two key functions:

• auth.uid() — returns the sub claim from the JWT, which is the user's UUID
• auth.role() — returns the role claim: anon, authenticated, or a custom role

When you write a policy like USING (auth.uid() = user_id), PostgreSQL evaluates this expression for every row. Rows where the expression returns false are silently excluded from the result set — not returned as an error.

This means RLS operates entirely inside the database, independent of your API layer. Even if an attacker bypasses your Next.js middleware, even if they call the Supabase REST API directly, RLS still enforces data isolation — provided it is configured correctly.

Real-World Incident: What Happens Without RLS

Consider a realistic scenario that plays out regularly in the vibe-coding era.

A solo founder builds a SaaS project management tool using Lovable over a weekend. Lovable scaffolds a Supabase backend with a projects table. The AI generates RLS policies that use USING (true) to make things work quickly during prototyping. The founder ships on Monday, tweets about it, and gets 300 sign-ups.

By Tuesday afternoon, a curious user opens the browser console and runs:

const { data } = await supabase.from('projects').select('*');
console.log(data); // returns ALL projects from ALL users

Within an hour, someone posts the issue to Hacker News. Every project from every user — including names, descriptions, internal notes, and linked API keys stored in project metadata — is now publicly accessible to any registered user.

This is not a hypothetical. Variations of this story happen every week. The fix would have been three lines of SQL.

What RLS Does (and Doesn't Do)

RLS enforces access control at the database level. Even if an attacker bypasses your API middleware or directly calls the Supabase REST API, RLS policies block unauthorized data access.

However, there's a critical nuance: RLS only works when it's enabled AND has explicit policies. A table with RLS enabled but no policies is locked down — no access for anyone. A table without RLS enabled is wide open: any authenticated user can read all rows.

There's also one important exception: the service_role key bypasses RLS entirely. This is by design — it allows your backend to perform administrative operations. But it means that any code using the service role key ignores your carefully crafted policies. See How Exposed API Keys End Up in JS Bundles for how service role keys end up in client-side code and why that's catastrophic.

The Three Most Common Mistakes

1. Forgetting to Enable RLS

The most common issue: tables created without enabling RLS. This is the default in PostgreSQL — RLS is opt-in, not opt-out.

-- Check which tables have RLS disabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = false;

Enable RLS on every table that holds user data:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;

A good rule of thumb: if the table name could appear in a GDPR data subject request, it needs RLS enabled.

2. Overly Permissive "Allow All" Policies

AI assistants often generate this pattern to make things work quickly:

-- DANGEROUS: allows any authenticated user to read all rows
CREATE POLICY "Allow all authenticated users"
ON orders FOR SELECT
TO authenticated
USING (true);

The USING (true) clause means every row passes the check. Any logged-in user can read every order in your database.

A subtler variant appears when policies are created per-operation but the SELECT is still wide open:

-- Still dangerous: SELECT is unrestricted even though write ops are scoped
CREATE POLICY "Users manage own orders" ON orders
FOR ALL TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
 
-- But then someone adds this "for convenience":
CREATE POLICY "Read all" ON orders
FOR SELECT TO authenticated
USING (true);  -- overrides the scoped policy!

PostgreSQL evaluates all matching policies with OR logic — if any policy grants access, the row is visible. One permissive policy can undermine all your restrictive ones.

The fix: Filter by the authenticated user's ID:

-- Correct: user can only see their own orders
CREATE POLICY "Users can view own orders"
ON orders FOR SELECT
TO authenticated
USING (auth.uid() = user_id);

3. Missing Service Role Isolation

Supabase provides two client keys: anon (public) and service_role (admin). The service role bypasses RLS entirely.

// NEVER use service_role in client-side code
const supabase = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY);

If your service_role key is exposed in a JS bundle, an attacker has unrestricted database access regardless of your RLS policies. The key is also dangerous in leaked .env files, CI/CD logs, and git history.

The fix: Use service_role only in server-side code (API routes, edge functions). Client-side Supabase clients should always use the anon key:

// Client-side — uses anon key
// components/MyComponent.tsx
import { createBrowserClient } from '@supabase/ssr';
 
const supabase = createBrowserClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!  // anon key is safe to expose
);
 
// Server-side — can use service role
// app/api/admin/route.ts
import { createClient } from '@supabase/supabase-js';
 
const adminClient = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!  // server only, never in bundle
);

A Complete RLS Setup for a Multi-Tenant App

Here's a production-ready RLS configuration for a typical documents table:

-- Enable RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
 
-- Users can only see their own documents
CREATE POLICY "select_own_documents" ON documents
FOR SELECT TO authenticated
USING (auth.uid() = owner_id);
 
-- Users can only insert documents they own
CREATE POLICY "insert_own_documents" ON documents
FOR INSERT TO authenticated
WITH CHECK (auth.uid() = owner_id);
 
-- Users can only update their own documents
CREATE POLICY "update_own_documents" ON documents
FOR UPDATE TO authenticated
USING (auth.uid() = owner_id)
WITH CHECK (auth.uid() = owner_id);
 
-- Users can only delete their own documents
CREATE POLICY "delete_own_documents" ON documents
FOR DELETE TO authenticated
USING (auth.uid() = owner_id);

Note the use of WITH CHECK on INSERT and UPDATE — this prevents users from assigning ownership to other users when creating or modifying records.

RLS for Shared/Team Data (Multi-Tenant Patterns)

Single-user ownership is straightforward, but most SaaS apps involve teams where multiple users share data. Here's a production pattern using an organization_members join table:

-- organizations table
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- members join table
CREATE TABLE organization_members (
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member',  -- 'owner', 'admin', 'member'
  PRIMARY KEY (org_id, user_id)
);
 
-- projects belong to organizations
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Enable RLS
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
 
-- Users can see orgs they belong to
CREATE POLICY "members_see_own_org" ON organizations
FOR SELECT TO authenticated
USING (
  id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  )
);
 
-- Users can see projects in their orgs
CREATE POLICY "members_see_org_projects" ON projects
FOR SELECT TO authenticated
USING (
  org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  )
);
 
-- Only org admins and owners can create projects
CREATE POLICY "admins_insert_projects" ON projects
FOR INSERT TO authenticated
WITH CHECK (
  org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  )
);
 
-- Users can see their own memberships
CREATE POLICY "members_see_own_membership" ON organization_members
FOR SELECT TO authenticated
USING (user_id = auth.uid());

This pattern scales to any multi-tenant model. The key insight: always resolve authorization through a join on a membership table rather than embedding user IDs directly in every row.

Supabase Storage Policies

RLS applies to database tables, but Supabase Storage uses a parallel policy system for buckets and objects. Many developers secure their tables but leave storage wide open.

-- Make a bucket private (only accessible to authenticated users)
INSERT INTO storage.buckets (id, name, public) VALUES ('documents', 'documents', false);
 
-- Allow users to upload their own files
CREATE POLICY "users_upload_own_files"
ON storage.objects FOR INSERT TO authenticated
WITH CHECK (
  bucket_id = 'documents' AND
  (storage.foldername(name))[1] = auth.uid()::text
);
 
-- Allow users to read their own files
CREATE POLICY "users_read_own_files"
ON storage.objects FOR SELECT TO authenticated
USING (
  bucket_id = 'documents' AND
  (storage.foldername(name))[1] = auth.uid()::text
);
 
-- Allow users to delete their own files
CREATE POLICY "users_delete_own_files"
ON storage.objects FOR DELETE TO authenticated
USING (
  bucket_id = 'documents' AND
  (storage.foldername(name))[1] = auth.uid()::text
);

The convention here is to organize files in folders named by user ID: documents/{user-uuid}/filename.pdf. The storage.foldername() function extracts the folder path, and the policy checks that the first folder segment matches the authenticated user's ID.

Auditing Existing RLS Policies

Use these SQL queries in the Supabase SQL Editor to audit your current RLS setup:

-- 1. Tables without RLS enabled (should be empty for user-data tables)
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = false;
 
-- 2. All current policies — review for USING (true)
SELECT tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, cmd;
 
-- 3. Tables with RLS enabled but NO policies (effectively locked to everyone)
SELECT t.tablename
FROM pg_tables t
LEFT JOIN pg_policies p ON t.tablename = p.tablename AND t.schemaname = p.schemaname
WHERE t.schemaname = 'public'
  AND t.rowsecurity = true
  AND p.policyname IS NULL;
 
-- 4. Policies using USING (true) — permissive "allow all" patterns
SELECT tablename, policyname, qual
FROM pg_policies
WHERE schemaname = 'public'
  AND qual = 'true';

Query 4 is particularly useful — any row in that result is a policy that grants unconditional access and needs immediate review.

RLS Performance Considerations

RLS policies that reference other tables (like the multi-tenant pattern above with organization_members) execute a subquery on every row evaluation. Without proper indexes, this can cause significant performance degradation at scale.

-- Essential indexes for RLS performance
 
-- Index on the foreign key used in ownership checks
CREATE INDEX idx_documents_owner_id ON documents(owner_id);
CREATE INDEX idx_projects_org_id ON projects(org_id);
 
-- Index on the membership lookup table (critical for multi-tenant RLS)
CREATE INDEX idx_org_members_user_id ON organization_members(user_id);
CREATE INDEX idx_org_members_org_id ON organization_members(org_id);
 
-- Composite index if you filter by both org and role
CREATE INDEX idx_org_members_user_role ON organization_members(user_id, org_id, role);

You can verify RLS is using these indexes with EXPLAIN ANALYZE:

SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO '{"sub": "your-test-user-uuid"}';
 
EXPLAIN ANALYZE SELECT * FROM projects WHERE true;

Look for Index Scan rather than Seq Scan in the output. A sequential scan on a large table with an RLS subquery is a performance time bomb.

Testing Your RLS Policies

Use the Supabase SQL editor to test policies by impersonating a user:

-- Test RLS as a specific user
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO '{"sub": "user-uuid-here"}';
 
SELECT * FROM documents; -- Should only return this user's documents

You can also write automated tests using the Supabase test helpers or a dedicated PostgreSQL testing framework like pgTAP:

-- Using pgTAP for RLS testing
BEGIN;
SELECT plan(3);
 
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO '{"sub": "user-a-uuid"}';
 
SELECT is(
  (SELECT count(*)::int FROM documents),
  2,
  'User A sees only their 2 documents'
);
 
SELECT is(
  (SELECT count(*)::int FROM documents WHERE owner_id != auth.uid()),
  0,
  'User A sees no documents owned by others'
);
 
ROLLBACK;

FAQ

Does RLS slow down my app?

With proper indexes on the columns referenced in your policies, the overhead is negligible for most applications. The subquery pattern used in multi-tenant RLS can become slow at scale (millions of rows) without indexes, but adding the right indexes (see above) typically resolves this. Always benchmark with EXPLAIN ANALYZE before concluding that RLS is the bottleneck.

Do I need RLS if I use the service_role key only on the backend?

Yes, strongly recommended. The service_role key is a single point of failure — if it leaks (through git history, CI/CD logs, or a misconfigured environment variable), an attacker has unrestricted database access. RLS on user tables ensures that even a compromised service_role still can't be weaponized via your own exposed API endpoints, because your application code still enforces ownership checks at the query level.

What happens to anonymous (anon) users?

By default, the anon role can't access any table where RLS is enabled unless you explicitly create a policy for it. If you want unauthenticated users to see certain public data, add a specific policy:

-- Allow anon users to read published posts only
CREATE POLICY "anon_read_published" ON posts
FOR SELECT TO anon
USING (published = true);

Any table without an anon policy is effectively private to unauthenticated users, which is the safe default.

Can I test RLS without a real user token?

Yes. The SET LOCAL request.jwt.claims trick shown above works in the SQL Editor and in any PostgreSQL session. You can also use supabase.auth.admin.generateLink() or create test users in your Supabase project dashboard. For CI/CD testing, consider using the Supabase local development stack (supabase start) which gives you a full local Postgres instance where you can run RLS tests without touching production data.

VibeShield's Supabase scanner automatically checks for all of these misconfigurations — including exposed service role keys in JS bundles, tables without RLS enabled, and overly permissive USING (true) policies.

Scan your Supabase app →