RLS: Real Security in Your Database | Codea Bien

You're in a code review. Someone opens the browser console, runs a fetch with the anon key, and the room goes silent: the project's security depended entirely on client code, and anyone could read any user's data.

I've lived that moment, both as the spectator and as the one responsible. The good news: there's a fix, and it's called RLS (Row Level Security). Here you'll learn to implement it correctly so your database can defend itself.

What is RLS

Row Level Security is a PostgreSQL feature that allows defining access policies at the row level. Instead of relying on your code to filter which data each user sees, the database itself handles it.

This means that even if someone has access to your database (with the anon key, for example), they can only see the data that policies allow them to.

Why it's critical

Without RLS:

Client → SELECT * FROM tasks → ALL tasks (even from other users)

With RLS:

Client → SELECT * FROM tasks → Only CURRENT USER's tasks

Supabase's anon key has access to your database. Without RLS, anyone with that key can read everything.

Enable RLS

-- Enable RLS on a table
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

Once enabled, nobody can access the table until you create policies. It's deny-by-default.

Basic policies

Only the owner can read their data

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

Only the owner can insert

-- Users can only create tasks for themselves
CREATE POLICY "Users can insert own tasks"
ON tasks
FOR INSERT
WITH CHECK (auth.uid() = user_id);

Only the owner can update

-- Users can only update their own tasks
CREATE POLICY "Users can update own tasks"
ON tasks
FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

Only the owner can delete

-- Users can only delete their own tasks
CREATE POLICY "Users can delete own tasks"
ON tasks
FOR DELETE
USING (auth.uid() = user_id);

Policy types

Type	Description
SELECT	Who can read rows
INSERT	Who can create rows
UPDATE	Who can modify rows
DELETE	Who can delete rows
ALL	Combination of all 4 above

Role-based policies

Public access (read)

-- Anyone can read products (public store)
CREATE POLICY "Public can view products"
ON products
FOR SELECT
USING (true);

Only admin can modify

-- Only admins can modify products
CREATE POLICY "Admins can manage products"
ON products
FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_roles.user_id = auth.uid()
    AND user_roles.role = 'admin'
  )
);

Complete example: Task app

Create the table

-- Create tasks table
CREATE TABLE tasks (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

Enable RLS

-- Enable RLS
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

Create policies

-- Read policy
CREATE POLICY "Users can view own tasks"
ON tasks FOR SELECT
USING (auth.uid() = user_id);

-- Insert policy
CREATE POLICY "Users can insert own tasks"
ON tasks FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Update policy
CREATE POLICY "Users can update own tasks"
ON tasks FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Delete policy
CREATE POLICY "Users can delete own tasks"
ON tasks FOR DELETE
USING (auth.uid() = user_id);

Use from client

// src/tasks.ts
import { supabase } from './lib/supabase'

// This only returns current user's tasks (RLS handles it)
const { data, error } = await supabase
  .from('tasks')
  .select('*')

// This only works if user_id matches authenticated user
const { data: newTask, error: insertError } = await supabase
  .from('tasks')
  .insert({ title: 'My new task' })

Advanced policies

Time-based access

-- Can only edit tasks created in last 24 hours
CREATE POLICY "Recent tasks can be edited"
ON tasks FOR UPDATE
USING (
  auth.uid() = user_id
  AND created_at > now() - interval '24 hours'
);

User metadata-based access

-- Only premium users can create more than 10 tasks
CREATE POLICY "Premium users can create tasks"
ON tasks FOR INSERT
WITH CHECK (
  auth.uid() = user_id
  AND (
    SELECT count(*) FROM tasks WHERE user_id = auth.uid()
  ) < 10
  OR
  (auth.jwt() ->> 'plan')::text = 'premium'
);

Shared access

-- Users can view tasks from projects they're members of
CREATE POLICY "Project members can view tasks"
ON tasks FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM project_members
    WHERE project_members.project_id = tasks.project_id
    AND project_members.user_id = auth.uid()
  )
);

Service Role Key

Supabase has two keys:

Anon Key: Public, respects RLS
Service Role Key: Private, bypasses RLS

The service role key is used only on the server (Edge Functions, API routes):

// src/lib/supabase-admin.ts (SERVER ONLY)
import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.SUPABASE_URL
const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY

// This client bypasses RLS - use only on server
export const supabaseAdmin = createClient(supabaseUrl, supabaseServiceKey)

NEVER expose the service role key on the client.

Debugging RLS

View active policies

-- View all policies for a table
SELECT * FROM pg_policies WHERE tablename = 'tasks';

Test policies

-- Simulate a specific user
SET request.jwt.claims = '{"sub": "user-uuid-here"}';
SET role = 'authenticated';

SELECT * FROM tasks;
-- Only returns that user's tasks

RESET role;

Common errors

"new row violates row-level security policy"

INSERT policy doesn't allow that operation
Verify WITH CHECK is correct

0 rows returned (but there is data)

SELECT policy is filtering everything
Verify USING is correct

"permission denied for table"

RLS is enabled but no policies exist
Create at least one policy

Common security mistakes

Forgetting to enable RLS: Table is accessible by default
Using service role key on client: Exposes all data
Overly permissive policies: USING (true) on sensitive tables
Not verifying on INSERT: WITH CHECK different from USING

What's next

In the next article we cover Storage: how to handle files, images, and documents in Supabase. We'll see uploads, image transformations, and how to serve them efficiently.