Row Level Security (RLS) Fundamentals

Control who can see and modify which rows of data with PostgreSQL's built-in security

Last updated: 8/15/2025

Row Level Security (RLS) is your primary defence against unauthorised data access. It decides which rows each user can see or modify, right at the database level.

The Default: Everything is Blocked

When you enable RLS on a table, the default is deny everything. No rows are visible until you create policies that allow access.

-- Turn on RLS (blocks all access by default)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Now NO ONE can see any rows until you add policies

How RLS Works

Every database request goes through this flow:

Client Request (Authentication) (RLS Policies) (Allowed Rows)

Client makes request with an API key or JWT token
Supabase identifies the user (authenticated or anonymous)
PostgreSQL checks RLS policies for that user
Only matching rows are returned or affected

Basic Policy Patterns

Public Read Access

Allow anyone to read certain data:

-- Everyone can read all products
CREATE POLICY "products_public_read" 
ON products FOR SELECT 
USING (true);

User-Owned Data

Users can only see their own records:

-- Users see only their own orders
CREATE POLICY "users_own_orders" 
ON orders FOR SELECT 
USING (auth.uid() = user_id);

Authenticated Users Only

Require login for any access:

-- Only logged-in users can read profiles
CREATE POLICY "authenticated_read_profiles" 
ON profiles FOR SELECT 
USING (auth.role() = 'authenticated');

Understanding auth.uid()

auth.uid() returns the UUID of the currently authenticated user. It's NULL for anonymous requests.

-- Check current user in SQL
SELECT auth.uid(), auth.role();

-- Example policy using the current user
CREATE POLICY "user_profile_access" 
ON profiles FOR ALL 
USING (auth.uid() = id);

Policies for Different Operations

SELECT Policies (Reading Data)

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

-- User can read own drafts
CREATE POLICY "author_read_drafts" ON posts 
FOR SELECT USING (author_id = auth.uid());

INSERT Policies (Creating Data)

-- Users can only create records for themselves
CREATE POLICY "users_insert_own_posts" ON posts 
FOR INSERT WITH CHECK (author_id = auth.uid());

UPDATE Policies (Modifying Data)

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

DELETE Policies (Removing Data)

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

Combining Policies

Multiple policies for the same operation are combined with OR logic:

-- Posts are visible if they're published OR owned by the user
CREATE POLICY "public_published_posts" ON posts 
FOR SELECT USING (published = true);

CREATE POLICY "authors_see_own_posts" ON posts 
FOR SELECT USING (author_id = auth.uid());

-- Result: Users see published posts + their own unpublished posts

Real-World Example: Blog System

-- Blog posts table
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT,
  author_id UUID NOT NULL,
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

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

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

-- Authors can see all their own posts
CREATE POLICY "authors_read_own" ON posts
FOR SELECT USING (auth.uid() = author_id);

-- Authors can create posts (must set themselves as author)
CREATE POLICY "authors_create_posts" ON posts
FOR INSERT WITH CHECK (auth.uid() = author_id);

-- Authors can update their own posts
CREATE POLICY "authors_update_own" ON posts
FOR UPDATE 
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

-- Authors can delete their own posts
CREATE POLICY "authors_delete_own" ON posts
FOR DELETE USING (auth.uid() = author_id);

Testing Your Policies

As an Anonymous User

// This will only return published posts
const { data } = await supabase
  .from('posts')
  .select('*');

As an Authenticated User

// First, sign in
await supabase.auth.signInWithPassword({
  email: 'user@example.com',
  password: 'password'
});

// This will return published posts + user's own unpublished posts
const { data } = await supabase
  .from('posts')
  .select('*');

Common Gotchas

USING vs WITH CHECK

USING: Controls which existing rows can be seen/modified
WITH CHECK: Controls what values can be inserted/updated

-- Wrong: This allows users to create posts for other authors
CREATE POLICY "bad_insert" ON posts 
FOR INSERT USING (auth.uid() = author_id);

-- Right: This ensures inserted posts have correct author_id
CREATE POLICY "good_insert" ON posts 
FOR INSERT WITH CHECK (auth.uid() = author_id);

Service Role Bypasses RLS

Requests made with the service_role key bypass all RLS policies:

// This ignores RLS policies - sees ALL data
const { data } = await adminSupabase
  .from('posts')
  .select('*');

Keep service role keys server-only for admin operations.

Anonymous vs Authenticated

Make sure your policies handle both states:

-- This policy fails for anonymous users (auth.uid() is NULL)
CREATE POLICY "bad_policy" ON posts 
FOR SELECT USING (author_id = auth.uid());

-- Better: Handle both cases explicitly
CREATE POLICY "public_read" ON posts 
FOR SELECT USING (published = true);

CREATE POLICY "author_read" ON posts 
FOR SELECT USING (auth.uid() IS NOT NULL AND author_id = auth.uid());

Debugging RLS Issues

Check Current User Context

SELECT 
  auth.uid() as current_user_id,
  auth.role() as current_role;

Test Policies Manually

-- Simulate a policy check
SELECT * FROM posts 
WHERE published = true OR author_id = auth.uid();

Enable Policy Logging

Check the Supabase logs to see which policies are being evaluated.

Performance Considerations

Index Policy Columns

-- Speed up user-owned data queries
CREATE INDEX idx_posts_author_id ON posts(author_id);

-- Speed up published post queries  
CREATE INDEX idx_posts_published ON posts(published) WHERE published = true;

Avoid Complex Joins in Policies

-- Slow: joins in every policy evaluation
CREATE POLICY "complex_policy" ON posts
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM user_permissions up
    JOIN roles r ON up.role_id = r.id
    WHERE up.user_id = auth.uid() AND r.name = 'admin'
  )
);

-- Better: use a view or simpler column check

Policy Naming Conventions

Use descriptive names that indicate the operation and access pattern:

-- Good policy names
"public_read_published_posts"
"authors_crud_own_posts"  
"admins_full_access"

-- Unclear policy names
"policy_1"
"posts_policy"
"access_rule"

Disabling RLS (Temporarily)

-- Turn off RLS (dangerous - only for development)
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

-- Turn it back on
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Warning: Only disable RLS in development. Production tables should always have RLS enabled.

Common Pattern in Supabase

For sensitive tables:

Base table: Full data, no direct API access for anon/authenticated (no policies).
Public view: Only non-sensitive columns, RLS policy allows select.
Backend/server functions: Use service_role key to access full table.

Example Implementation

-- Base table with sensitive data (no RLS policies)
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  full_name TEXT NOT NULL,
  phone TEXT,
  address TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Public view with only safe columns
CREATE VIEW public_user_profiles AS
SELECT id, full_name, created_at
FROM user_profiles;

-- Enable RLS on the view
ALTER VIEW public_user_profiles ENABLE ROW LEVEL SECURITY;

-- Policy for the view (users can see basic info about others)
CREATE POLICY "view_public_profiles" ON public_user_profiles
FOR SELECT USING (true);

-- Backend function to access full profile (uses service_role)
CREATE OR REPLACE FUNCTION get_full_user_profile(user_uuid UUID)
RETURNS TABLE(
  id UUID,
  email TEXT,
  full_name TEXT,
  phone TEXT,
  address TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT up.id, up.email, up.full_name, up.phone, up.address
  FROM user_profiles up
  WHERE up.id = user_uuid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

This pattern ensures sensitive data is never exposed through the public API while maintaining controlled access through server-side functions.

Next Steps

Once you understand basic RLS:

Learn multi-tenant patterns for team/organisation data
Implement role-based access with user roles and permissions
Use database functions to encapsulate complex policy logic
Monitor performance of policy evaluations in production

RLS provides bulletproof data security when configured correctly. Start with simple user-owned data patterns, then gradually add more sophisticated access controls as your application grows.