supabase postgresql rls security database

Supabase Row Level Security: Patterns That Actually Work

· 11 min read · Database & Architecture

Practical RLS patterns for Supabase including user ownership, role-based access, shared resources, and common debugging techniques.

Supabase Row Level Security: Patterns That Actually Work

Row Level Security in Supabase looks simple in the docs. You write a policy, enable RLS, and your data is protected. Then you deploy, and users can see each other's data, or worse — nobody can see anything.

RLS failures are silent. No error message tells you "your policy is wrong." Queries just return empty arrays or full tables. This guide covers the RLS patterns that work in production and the mistakes that break them.

Why RLS Matters

Without RLS, any authenticated user can query any row in your table through the Supabase client. The anon key is public — it ships in your frontend JavaScript. If RLS is disabled, anyone with that key can read your entire database.

-- Without RLS: any authenticated user sees ALL rows
SELECT * FROM messages; -- Returns everything

RLS adds authorization at the database level:

-- With RLS: users only see their own messages
SELECT * FROM messages; -- Returns only rows where sender_id = auth.uid()

Enabling RLS

ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Force RLS for table owner too (important for testing)
ALTER TABLE messages FORCE ROW LEVEL SECURITY;

Without FORCE, the table owner (usually postgres) bypasses all policies. This is fine for admin operations but can mask bugs during development.

Pattern 1: User Owns Row

The most common pattern. Users can only access their own data:

CREATE POLICY "Users can view own messages"
ON messages FOR SELECT
USING (sender_id = auth.uid());

CREATE POLICY "Users can insert own messages"
ON messages FOR INSERT
WITH CHECK (sender_id = auth.uid());

CREATE POLICY "Users can update own messages"
ON messages FOR UPDATE
USING (sender_id = auth.uid())
WITH CHECK (sender_id = auth.uid());

CREATE POLICY "Users can delete own messages"
ON messages FOR DELETE
USING (sender_id = auth.uid());

Key distinction: USING filters which rows you can see/modify. WITH CHECK validates the data you are inserting or updating. For UPDATE, you need both — USING to select the row, WITH CHECK to validate the new data.

Pattern 2: Public Read, Private Write

Blog posts, product listings, or any public content:

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

-- Only authors can insert
CREATE POLICY "Authors can create posts"
ON blog_posts FOR INSERT
WITH CHECK (author_id = auth.uid());

-- Only the author of a post can update it
CREATE POLICY "Authors can update own posts"
ON blog_posts FOR UPDATE
USING (author_id = auth.uid())
WITH CHECK (author_id = auth.uid());

Pattern 3: Role-Based Access

When you need admin, editor, and viewer roles:

-- Create a function to check user role
CREATE OR REPLACE FUNCTION public.user_role()
RETURNS TEXT AS $
  SELECT role FROM public.profiles WHERE id = auth.uid()
$ LANGUAGE SQL SECURITY DEFINER STABLE;

-- Admins can do everything
CREATE POLICY "Admins full access"
ON blog_posts FOR ALL
USING (public.user_role() = 'admin');

-- Editors can read and update but not delete
CREATE POLICY "Editors can read posts"
ON blog_posts FOR SELECT
USING (public.user_role() IN ('admin', 'editor'));

CREATE POLICY "Editors can update posts"
ON blog_posts FOR UPDATE
USING (public.user_role() IN ('admin', 'editor'));

The SECURITY DEFINER on the function means it runs with the function owner's permissions, not the calling user's. This lets the function read the profiles table even if the calling user doesn't have direct access.

Pattern 4: Shared Resources

When multiple users need access to the same resource (teams, organizations):

-- Team members can view team projects
CREATE POLICY "Team members can view projects"
ON projects FOR SELECT
USING (
  team_id IN (
    SELECT team_id FROM team_members
    WHERE user_id = auth.uid()
  )
);

Warning: subqueries in RLS policies run on every row check. For large tables, this can be slow. Consider denormalizing the team_id onto the user's JWT claims if performance becomes an issue.

Pattern 5: Service Role Bypass

Backend serverless functions need to bypass RLS for admin operations:

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

// This client bypasses RLS — use only in server-side code
const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

Never expose the service role key in frontend code. It bypasses all policies.

Common Mistakes

Mistake 1: Forgetting to enable RLS. New tables have RLS disabled by default. If you create a table and forget ALTER TABLE ... ENABLE ROW LEVEL SECURITY, the table is open to anyone with the anon key.

Mistake 2: SELECT policy missing. You write INSERT and UPDATE policies but forget SELECT. The user inserts data but can't read it back.

Mistake 3: Missing WITH CHECK on INSERT. Without WITH CHECK, users can insert rows with any user_id, not just their own.

Mistake 4: Policy too broad. A policy like USING (true) on ALL operations means anyone can do anything. Be explicit about each operation.

Debugging RLS

When policies seem wrong, test in the SQL editor with:

-- Check what auth.uid() returns
SELECT auth.uid();

-- Check which policies exist
SELECT * FROM pg_policies WHERE tablename = 'messages';

-- Test as a specific user (in Supabase SQL editor)
SET request.jwt.claim.sub = 'user-uuid-here';
SELECT * FROM messages;

Takeaways

RLS is the most important security feature in Supabase. It moves authorization from application code to the database, which means bugs in your frontend cannot expose other users' data.

Start with the simplest policy that works, test it as different users, and use FORCE ROW LEVEL SECURITY during development to catch issues early. Every new table should have RLS enabled before any data is inserted.

Read Next