PostgreSQL Row Level Security (RLS) Training Labs

A hands-on training program with 15 progressive labs to master PostgreSQL Row Level Security (RLS) concepts, from fundamentals to advanced security patterns.

📋 Prerequisites

• Supabase CLI installed
• Docker installed and running

🚀 Quick Start

1. Clone the Repository

git clone https://github.com/Ellba/Supabase-RLS-Training-Labs.git
cd Supabase-RLS-Training-Labs

2. Start Supabase Local Development

supabase start

This will:

• Start a local PostgreSQL database
• Apply all migrations (base schema + 15 labs cron jobs)
• Set up the RLS training environment

3. Access Your Database

After supabase start completes, you'll see connection details:

API URL: http://127.0.0.1:54321
Studio URL: http://127.0.0.1:54323
Access DB: psql "postgresql://postgres:postgres@127.0.0.1:54322/postgres" 

Open Supabase Studio at http://localhost:54323 to:

• Enable cron job for a lab here - http://127.0.0.1:54323/project/default/integrations/cron/jobs
• View tables and policies
• Run SQL queries
• Monitor lab progress

Note: Some cron jobs fail before you apply solution. In other cases, the job succeeds, but you need to improve performance or the query in the cron job should return positive result.

4. Start the Labs

Open the SQL Editor in Supabase Studio and work through the labs in order:

1. Read the lab description in labs_descriptions.sql
2. Analyze the problem - understand what's broken
3. Write your solution - fix the RLS policies
4. Verify - each lab has built-in tests that run automatically
5. Compare - check your solution against solutions.sql

🔍 How Each Lab Works

Each lab contains:

1. A broken RLS setup - tables with incorrect or missing policies
2. Test data - automatically inserted to verify your solution
3. A cron job - simulates real-world operations that fail until fixed
4. Success criteria - clear indicators when the lab is solved

Example Workflow

-- 1. Check the current state
SELECT * FROM rls_lab.lab_1_no_policy_docs;

-- 2. Analyze the policies
SELECT * FROM pg_policies WHERE tablename = 'lab_1_no_policy_docs';

-- 3. Create your solution
CREATE POLICY "your_policy_name"
    ON rls_lab.lab_1_no_policy_docs
    FOR INSERT
    TO authenticated
    WITH CHECK (auth.uid() = user_id);

-- 4. Verify it works
-- The cron job will automatically run and show success/failure

📖 Project Structure

rls_test/
├── README.md                          # This file
├── labs_descriptions.sql              # Lab problems and instructions
├── solutions.sql                      # Complete solutions with explanations
├── supabase/
│   ├── config.toml                    # Supabase configuration
│   ├── migrations/                    # Database migrations
│   │   ├── 20251010092332_base_schemas.sql
│   │   ├── 20251010103851_lab_1_no_policies.sql
│   │   ├── ... (15 labs total)
│   │   └── 20261010113629_disable_cron_jobs.sql
│   └── seed.sql                       # Optional seed data
└── temp/                              # Temporary files (not tracked in git)

🛠️ Useful Commands

Supabase CLI Commands

# Start local development
supabase start

# Stop local development
supabase stop

# Recreates the local Postgres container and applies all local migrations found in supabase/migrations directory and db is seeded after the migrations are run. Any other data or schema changes made during local development will be discarded.
supabase db reset