labs_descriptions.sql

-- ============================================================================
-- RLS TRAINING SOLUTIONS
-- ============================================================================
-- This file contains solutions to all RLS labs, organized from basic to advanced.
-- Each lab teaches specific RLS concepts and includes detailed explanations.
--
-- DIFFICULTY LEVELS:
-- Labs 1-4:   EASY (Fundamentals)
-- Labs 5-8:   MEDIUM (Policy manipulation and combinations)
-- Labs 9-12:  PERFORMANCE (Performance optimization best practices)
-- Labs 13-15: DATA SECURITY (Advanced security patterns for sensitive data)
-- ============================================================================


-- ============================================================================
-- LAB 1: No Policies (RLS Enabled but No Policies Defined)
-- ============================================================================
-- DIFFICULTY: EASY
-- CONCEPTS: Basic RLS, INSERT policies, WITH CHECK clause
--
-- PROBLEM:
--   The table has RLS enabled but NO policies exist. This means ALL queries
--   will be denied by default (even for authenticated users).
--   The cron job tries to INSERT as an authenticated user with a valid JWT,
--   but fails because there's no policy allowing the insert.
--
-- SOLUTION:
--   Add an INSERT policy for authenticated users that allows them to insert
--   rows where the user_id matches their auth.uid().
--
-- KEY LEARNING:
--   - RLS denies by default when enabled with no policies
--   - INSERT operations require WITH CHECK clause
--   - Policies target specific roles (e.g., 'authenticated')
-- ============================================================================


-- ============================================================================
-- LAB 2: Role Mismatch (Policy Only for Authenticated, Anon Denied)
-- ============================================================================
-- DIFFICULTY: EASY
-- CONCEPTS: Database roles (anon vs authenticated), role targeting
--
-- PROBLEM:
--   The table has a policy that only applies to the 'authenticated' role.
--   The cron job attempts to INSERT as 'anon' (anonymous user with no JWT).
--   Since there's no policy for 'anon', the insert is denied.
--
-- SOLUTION:
--   Add a policy for the 'anon' role. Since anon users have no auth.uid(),
--   we can't check ownership. This policy allows all operations for anon
--   (using true condition). In production, you'd want more restrictive logic.
--
-- KEY LEARNING:
--   - Different policies can target different database roles
--   - 'anon' users don't have JWT tokens (auth.uid() returns NULL)
--   - Each role needs its own policies
-- ============================================================================


-- ============================================================================
-- LAB 3: Missing INSERT Policy (Has SELECT and UPDATE, Missing INSERT)
-- ============================================================================
-- DIFFICULTY: EASY
-- CONCEPTS: Policy granularity, different policies for different operations
--
-- PROBLEM:
--   The table has SELECT and UPDATE policies but NO INSERT policy.
--   The cron job tries to INSERT as an authenticated user with a valid JWT,
--   but fails because there's no INSERT policy defined.
--
-- SOLUTION:
--   Add an INSERT policy for authenticated users that allows them to insert
--   rows where the user_id matches their auth.uid().
--
-- KEY LEARNING:
--   - Policies are operation-specific (SELECT, INSERT, UPDATE, DELETE)
--   - Each operation needs its own policy unless you use FOR ALL
--   - Missing a specific policy blocks that operation
-- ============================================================================


-- ============================================================================
-- LAB 4: Create Policies from Scratch (Authenticated Users)
-- ============================================================================
-- DIFFICULTY: EASY
-- CONCEPTS: Creating policies, authenticated role, superuser bypass
--
-- PROBLEM:
--   The table has RLS disabled and NO policies. The cron job runs as
--   anon user and successfully inserts data.
--   Our goal is to allow only authenticated users to access the table. 
--
-- SOLUTION:
--   Enable RLS for the table.
--   Create SELECT and INSERT policies for authenticated users that allow them
--   to access their own documents (where auth.uid() = user_id) and deny access to anon users.
--
-- KEY LEARNING:
--   - Superusers bypass RLS entirely
--   - Authenticated users need explicit policies to access RLS-enabled tables
--   - Practice creating policies from scratch
--   - Use (SELECT auth.uid()) for InitPlan optimization
-- ============================================================================

-- Create SELECT policy for authenticated users


-- Create INSERT policy for authenticated users


-- ============================================================================
-- LAB 5: PERMISSIVE vs RESTRICTIVE (Understanding Policy Combination)
-- ============================================================================
-- DIFFICULTY: MEDIUM
-- CONCEPTS: PERMISSIVE vs RESTRICTIVE, policy combination, DROP POLICY
--
-- PROBLEM:
--   The table has THREE policies:
--   1. PERMISSIVE: Allow if user owns the document (auth.uid() = user_id)
--   2. PERMISSIVE: Allow if document is public (is_public = true)
--   3. RESTRICTIVE: Block everything (false)
--   
--   PERMISSIVE policies OR together: (owns it OR is_public)
--   RESTRICTIVE policies AND together: (false)
--   Final result: (owns it OR is_public) AND (false) = ALWAYS FALSE
--   
--   The restrictive policy blocks ALL access, even to documents the user owns!
--
-- SOLUTION:
--   Drop the RESTRICTIVE blocking policy. This allows the PERMISSIVE policies
--   to work correctly, granting access to owned or public documents.
--
-- KEY LEARNING:
--   - PERMISSIVE policies combine with OR logic
--   - RESTRICTIVE policies combine with AND logic
--   - Final check: (all PERMISSIVE ORed) AND (all RESTRICTIVE ANDed)
--   - One RESTRICTIVE policy with false blocks everything
--   - Use DROP POLICY to remove unwanted policies
--   - RESTRICTIVE policies are useful for adding constraints, not blocking
-- ============================================================================


-- ============================================================================
-- LAB 6: Missing DELETE Policy (Understanding DELETE Operations)
-- ============================================================================
-- DIFFICULTY: MEDIUM
-- CONCEPTS: DELETE policies, USING clause for DELETE
--
-- PROBLEM:
--   The table has SELECT, INSERT, and UPDATE policies but NO DELETE policy.
--   The cron job tries to DELETE a row, but fails because there's no
--   DELETE policy defined.
--
-- SOLUTION:
--   Add a DELETE policy for authenticated users. DELETE policies only need
--   a USING clause (not WITH CHECK) because they filter which rows can be
--   deleted, they don't create new rows.
--
-- KEY LEARNING:
--   - DELETE policies only use USING clause, not WITH CHECK
--   - USING determines which rows the user can delete
--   - DELETE is often forgotten when setting up CRUD policies
--   - Consider carefully who should have DELETE permissions
-- ============================================================================


-- ============================================================================
-- LAB 7: Altering Policies (Learn to Modify Existing Policies)
-- ============================================================================
-- DIFFICULTY: MEDIUM
-- CONCEPTS: ALTER POLICY, changing policy conditions
--
-- PROBLEM:
--   The SELECT policy is too restrictive - it only allows viewing documents
--   with status = 'published'. Users can insert 'draft' documents but can't
--   view them afterwards because the SELECT policy blocks them.
--   
--   The cron job inserts a 'draft' document, then tries to SELECT it, but
--   the query returns no results.
--
-- SOLUTION:
--   Alter the SELECT policy to allow users to see their own documents
--   regardless of status. This way users can see both their drafts and
--   published documents.
--
-- KEY LEARNING:
--   - ALTER POLICY lets you modify existing policies without dropping them
--   - You can change: name, roles, USING condition, WITH CHECK condition
--   - You CANNOT change: command type (SELECT/INSERT/etc) or PERMISSIVE/RESTRICTIVE
--   - To change those, you must DROP and recreate the policy
--   - Be careful with SELECT policies - they affect what users can see
-- ============================================================================


-- ============================================================================
-- LAB 8: Multiple Policies Combining (PERMISSIVE OR Logic)
-- ============================================================================
-- DIFFICULTY: MEDIUM
-- CONCEPTS: Multiple PERMISSIVE policies, OR logic, creating additional policies
--
-- PROBLEM:
--   The table has a SELECT policy that only allows 'hr' department documents.
--   A user in the 'engineering' department tries to insert and view their
--   document, but the SELECT fails because the policy only allows 'hr'.
--
-- SOLUTION:
--   Add another PERMISSIVE SELECT policy for 'engineering' department.
--   Since both policies are PERMISSIVE, they OR together:
--   (department = 'hr' OR department = 'engineering')
--   
--   This demonstrates how to grant access to multiple groups without
--   rewriting existing policies.
--
-- KEY LEARNING:
--   - Multiple PERMISSIVE policies combine with OR logic
--   - You can add policies incrementally without modifying existing ones
--   - This is useful for granting access to multiple departments/roles
--   - Each PERMISSIVE policy adds another way to gain access
--   - Consider: might be cleaner to use IN clause: department IN ('hr', 'engineering')
-- ============================================================================


-- ============================================================================
-- LAB 9: Performance - Use Indexes
-- ============================================================================
-- DIFFICULTY: BEGINNER
-- CONCEPTS: Indexes, query performance, RLS optimization
--
-- PROBLEM:
--   RLS policies work like WHERE conditions. Without an index on the
--   filtered column, PostgreSQL must scan every row (sequential scan).
--   
--   Current policy: USING (auth.uid() = user_id)
--   
--   Without an index on user_id, this becomes very slow on large tables.
--
-- SOLUTION:
--   Create an index on the column used in the RLS policy.
--   
--   For user_id filtering: CREATE INDEX idx_user_id ON table(user_id);
--
-- HOW TO VERIFY:
--   IMPORTANT: RLS only applies when running as a non-superuser role.
--   You MUST set up the session context in a transaction to see the index in action:
--   
-- BEGIN;
-- SET LOCAL ROLE authenticated;
-- SELECT set_config('request.jwt.claims', 
--   '{"role":"authenticated","sub":"00000000-0000-0000-0000-000000000001"}', true);

-- -- This should now use Index Scan instead of Seq Scan
-- EXPLAIN ANALYZE SELECT * FROM rls_lab.lab_9_use_indexes_docs;

-- -- You can also test with explicit WHERE (though RLS adds it automatically)
-- EXPLAIN ANALYZE SELECT count(*) FROM rls_lab.lab_9_use_indexes_docs
--   WHERE user_id = '00000000-0000-0000-0000-000000000001';

-- ROLLBACK;
--   
--   Without the transaction block, SET LOCAL won't work and you'll still be
--   running as postgres (superuser), which bypasses RLS entirely → Seq Scan.
--
-- KEY LEARNING:
--   - RLS policies behave like WHERE conditions
--   - Index the columns used in your RLS policies
--   - Use EXPLAIN to verify index usage (but set role + JWT first!)
--   - SET LOCAL requires a transaction block (BEGIN...ROLLBACK)
--   - Superusers bypass RLS, so EXPLAIN as postgres won't show the real plan
--   - Critical for performance on large tables
--   - Consider composite indexes for complex policies
-- ============================================================================

-- Create index on user_id for better RLS performance

-- Test the index with proper session setup

-- This should now use Index Scan instead of Seq Scan

-- You can also test with explicit WHERE (though RLS adds it automatically)

-- The policies already exist and will now benefit from the index
-- No need to drop/recreate them - indexes are transparent to queries

-- ============================================================================
-- LAB 10: Performance - InitPlan Optimization (SELECT wrapper)
-- ============================================================================
-- DIFFICULTY: INTERMEDIATE
-- CONCEPTS: InitPlan optimization, query performance, EXPLAIN output
--
-- PROBLEM:
--   The current policies use auth.uid() directly without wrapping it in
--   a SELECT subquery:
--   
--     USING (auth.uid() = user_id)
--   
--   This means auth.uid() might be called multiple times during query execution,
--   which is inefficient. While it works, it's slower than necessary.
--
-- SOLUTION:
--   Wrap auth.uid() in a SELECT subquery: (SELECT auth.uid())
--   
--   When three conditions are met:
--   1. Function is wrapped in a subquery
--   2. Subquery returns only 1 row
--   3. Subquery doesn't reference outer query variables
--   
--   PostgreSQL optimizes it as an InitPlan - the subquery runs ONCE and
--   the result is cached for the entire query execution.
--
-- HOW TO VERIFY:
--   Run EXPLAIN on a query and look for "InitPlan" in the output:
--   
--   EXPLAIN SELECT * FROM rls_lab.lab_10_initplan_docs;
--   
--   Before optimization: No InitPlan, auth.uid() called repeatedly
--   After optimization: InitPlan 1 shows auth.uid() called once
--
-- KEY LEARNING:
--   - Wrapping stable functions in (SELECT ...) creates InitPlan optimization
--   - InitPlan runs once and caches the result
--   - This is especially important for RLS policies (run on every row)
--   - Use EXPLAIN to verify the optimization worked
--   - Small syntax change, significant performance improvement
--   - Apply this pattern to any stable function in policies
-- ============================================================================

-- Drop existing non-optimized policies


-- Create optimized policies with (SELECT auth.uid())

-- Verify the InitPlan optimization is working

-- Check the query plan with optimized policy


-- ============================================================================
-- LAB 11: Performance - Security Definer for Cross-Table References
-- ============================================================================
-- DIFFICULTY: ADVANCED
-- CONCEPTS: Security definer functions, cross-table queries, RLS performance penalty
--
-- PROBLEM:
--   The current policies cross-reference another table (lab_11_user_roles)
--   directly in the policy condition using EXISTS and a subquery.
--   
--   When you cross-reference tables in RLS policies, BOTH tables get hit
--   with RLS overhead. This creates a performance penalty on both tables.
--   
--   Additionally, if the referenced table also has RLS policies that
--   reference back, you can get infinite recursion errors.
--
-- SOLUTION:
--   Create a SECURITY DEFINER function that encapsulates the cross-table
--   logic. Security definer functions run with the permissions of their
--   creator (who can bypass RLS), avoiding the RLS penalty.
--   
--   Important security notes:
--   - Put the function in a private schema (not 'public')
--   - Grant EXECUTE permission to the roles that need it
--   - Don't expose the schema through API settings
--   - Use SQL language when possible for better inlining
--
-- PERFORMANCE IMPACT:
--   Before: Both tables get RLS penalty, nested policy evaluation
--   After: Only one table has RLS, lookup function runs once efficiently
--
-- KEY LEARNING:
--   - Cross-table references in policies hurt performance on both tables
--   - Security definer functions run with creator's permissions (bypass RLS)
--   - Create private schemas for security definer functions
--   - Grant EXECUTE but don't expose schema via API
--   - Prefer SQL language over PL/pgSQL for better optimization
--   - This pattern prevents infinite recursion in complex policies
-- ============================================================================

-- Create the security definer function in private schema

-- Drop existing non-optimized policies

-- Create optimized policies using the security definer function

-- Add INSERT and UPDATE policies for lab_11_user_roles so cron job can set up test data
-- (The cron job uses ON CONFLICT DO UPDATE, so we need both INSERT and UPDATE policies)


-- ============================================================================
-- LAB 12: Performance - Specify Roles in Your Policy
-- ============================================================================
-- DIFFICULTY: BEGINNER
-- CONCEPTS: Policy role targeting, planner overhead, security best practices
--
-- PROBLEM:
--   When no TO clause is specified in a policy, it applies to PUBLIC (all roles).
--   
--   Current policies:
--     FOR SELECT USING (...)  -- No TO clause = applies to PUBLIC
--   
--   This causes the planner to evaluate the policy for every role, even
--   if most roles will never use it. It also reduces security clarity.
--
-- SOLUTION:
--   Always specify which roles the policy targets:
--   
--     FOR SELECT TO authenticated USING (...)
--   
--   This helps PostgreSQL's planner optimize queries and makes security
--   intentions explicit.
--
-- HOW TO VERIFY:
--   Query pg_policies to see which roles a policy targets:
--   
--   SELECT policyname, roles FROM pg_policies 
--   WHERE tablename = 'lab_12_specify_roles_docs';
--   
--   Before: roles = {public}
--   After:  roles = {authenticated}
--
-- KEY LEARNING:
--   - Always specify roles in your policies with TO clause
--   - Prevents policies from over-reaching
--   - Helps minimize planner overhead
--   - Makes security intentions explicit
--   - Common roles: authenticated, anon, service_role
-- ============================================================================

-- Drop existing non-optimized policies (they apply to PUBLIC)

-- Create optimized policies that specify roles explicitly


-- ============================================================================
-- LAB 13: DATA SECURITY - Column-Level Security (Hiding Sensitive Columns)
-- ============================================================================
-- DIFFICULTY: INTERMEDIATE
-- CONCEPTS: Column-level security, sensitive data protection, view-based security
--
-- PROBLEM:
--   The table contains sensitive columns (SSN, salary) that should only be
--   visible to the user who owns the record. The current policy uses:
--   
--     USING (true)  -- Everyone can see everything!
--   
--   This means Alice can see Bob's SSN and salary, which is a serious
--   data security violation. Users should only see their own sensitive data.
--
-- SOLUTION APPROACH:
--   PostgreSQL RLS operates at the row level, not column level. You cannot
--   directly say "hide this column from certain users" in a policy.
--   
--   There are several approaches to solve this:
--   
--   1. RECOMMENDED: Change the policy to only show own rows
--      - Simple and performant
--      - Users can only see rows they own (including all columns)
--   
--   2. ALTERNATIVE: Use a view with CASE expressions to mask columns
--      - More complex but allows users to see other rows with masked data
--      - Useful when you need to show some info but hide sensitive fields
--   
--   3. ALTERNATIVE: Split into separate tables (users vs sensitive_data)
--      - Complete separation of concerns
--      - More complex schema management
--
-- We'll use approach #1 (simplest and most common):
--
-- KEY LEARNING:
--   - RLS works at row level, not column level
--   - Restrict access to rows to protect sensitive columns
--   - For partial column visibility, use views with CASE expressions
--   - Consider separating sensitive data into different tables
--   - Always use principle of least privilege (only show what's needed)
-- ============================================================================

-- Drop the overly permissive policy

-- Create a restrictive policy: users can only see their own data

-- Optional: Create a view for non-sensitive data if you want to share it
-- This allows users to see everyone's username/email/department, but not SSN/salary

-- Grant access to the view


-- ============================================================================
-- LAB 14: DATA SECURITY - Multi-Tenancy Isolation (Organization-Based Access)
-- ============================================================================
-- DIFFICULTY: INTERMEDIATE
-- CONCEPTS: Multi-tenancy, organization-based access, JOIN in RLS policies
--
-- PROBLEM:
--   The current policy only allows users to see documents they created:
--   
--     USING (auth.uid() = created_by)
--   
--   But in a multi-tenant application, users should see ALL documents in
--   their organization, not just their own. This breaks team collaboration.
--   
--   Current behavior:
--   - Alice (Acme Corp) can see her docs ✓
--   - Alice cannot see Bob's docs (even though Bob is also in Acme Corp) ✗
--   - Alice cannot see Charlie's docs (Charlie is in Widget Inc) ✓
--
-- SOLUTION:
--   Check if the user belongs to the same organization as the document.
--   This requires a JOIN (or EXISTS) to cross-reference the user_orgs table.
--   
--   For best performance, wrap the check in a security definer function
--   to avoid RLS penalties on both tables.
--
-- KEY LEARNING:
--   - Multi-tenancy requires organization-based access control
--   - Use EXISTS or IN to check organization membership
--   - Security definer functions improve performance for cross-table checks
--   - Always validate org_id on INSERT to prevent data leakage
--   - Index org_id and user_id for better performance
-- ============================================================================

-- Create a security definer function to check org membership

-- Drop the restrictive policies

-- Create proper multi-tenant policies

-- Create an index for better performance



-- ============================================================================
-- LAB 15: DATA SECURITY - Overly Permissive Deletion Policy
-- ============================================================================
-- DIFFICULTY: INTERMEDIATE
-- CONCEPTS: Policy scope, DELETE operations, USING clause without FOR
--
-- PROBLEM:
--   A policy was created without a FOR clause, which applies to ALL operations.
--   With USING (true), this means the authenticated user can delete ANY row:
--   
--     CREATE POLICY delete_all_bad
--       ON rls_lab.lab_15_user_records
--       TO authenticated
--       USING (true);
--   
--   This is dangerous because:
--   - User 1 can delete records owned by User 2, User 3, etc.
--   - The cron job inserts 100 records (30 for User 1, 30 for User 2, 40 for User 3)
--   - User 1 tries: DELETE FROM rls_lab.lab_15_user_records;
--   - Before fix: ALL 100 records are deleted (BAD!)
--   - After fix: Only User 1's 30 records should be deleted
--
-- SOLUTION:
--   You have two options to fix this:
--   
--   Option 1: Add a FOR DELETE clause and restrict by ownership
--     DROP POLICY delete_all_bad ON rls_lab.lab_15_user_records;
--     CREATE POLICY delete_own_records
--       ON rls_lab.lab_15_user_records
--       FOR DELETE
--       TO authenticated
--       USING ((SELECT auth.uid()) = owner_id);
--   
--   Option 2: Use ALTER POLICY to change the USING clause
--     ALTER POLICY delete_all_bad 
--       ON rls_lab.lab_15_user_records
--       USING ((SELECT auth.uid()) = owner_id);
--
-- KEY LEARNING:
--   - Policies without FOR clause apply to ALL operations (SELECT, INSERT, UPDATE, DELETE)
--   - Always be specific with FOR clauses when you want operation-specific rules
--   - DELETE operations use only USING clause (not WITH CHECK)
--   - Test your policies with multiple users to ensure proper isolation
--   - Use (SELECT auth.uid()) = owner_id pattern for user-owned resources
-- ============================================================================

-- Drop the bad policy

-- Create a proper DELETE policy that restricts to owned records only


-- ============================================================================
-- CONGRATULATIONS!
-- ============================================================================
-- You've completed all RLS labs and learned:
--
-- BASICS (Labs 1-4):
-- - How RLS denies by default when enabled
-- - INSERT policies use WITH CHECK
-- - SELECT/UPDATE/DELETE use USING
-- - Different policies for different roles
-- - Creating policies from scratch
-- - Superusers bypass RLS
--
-- INTERMEDIATE (Labs 5-8):
-- - PERMISSIVE vs RESTRICTIVE policies
-- - How policies combine (OR for PERMISSIVE, AND for RESTRICTIVE)
-- - ALTER POLICY to modify existing policies
-- - DROP POLICY to remove policies
-- - Multiple policies working together
--
-- PERFORMANCE OPTIMIZATION (Labs 9-12):
-- - Using indexes for RLS policy columns
-- - InitPlan optimization with (SELECT function())
-- - Security definer functions for cross-table references
-- - Specifying roles explicitly in policies
-- - Avoiding correlated subqueries (pseudo joins)
-- - Using EXPLAIN to verify optimizations
--
-- DATA SECURITY (Labs 13-15):
-- - Column-level security through row restrictions
-- - Multi-tenancy isolation with organization-based access
-- - Security definer functions for complex access checks
-- - Protecting sensitive data (SSN, salary)
-- - Policies without FOR clause apply to ALL operations
-- - Importance of restricting DELETE operations to owned resources
-- - Best practices for handling PII and preventing unauthorized deletions
--
-- NEXT STEPS:
-- - Practice creating policies for your own tables
-- - Use EXPLAIN to analyze query performance
-- - Review the RLS documentation for advanced patterns
-- - Consider organizational hierarchies and multi-tenancy
-- - Always test policies with multiple users and different ownership scenarios
-- - Test your policies thoroughly before production!
-- - Consider encryption for highly sensitive data (pgcrypto)
-- - Implement audit logging for sensitive data access
-- ============================================================================