Supabase Row Level Security — Protect Your Database Row by Row

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

-- For new projects, RLS is enabled by default on all tables
-- Verify RLS status
SELECT relname, relrowsecurity 
FROM pg_class 
WHERE relname = 'files';

# rls_enable.py
# Understanding RLS enablement

def rls_checklist():
    items = [
        "ALTER TABLE files ENABLE ROW LEVEL SECURITY",
        "Create SELECT policy for read access",
        "Create INSERT policy for write access",
        "Create UPDATE policy for modify access",
        "Create DELETE policy for remove access",
        "Test policies with anon key in browser",
        "Test policies with authenticated user",
    ]
    
    print("RLS Setup Checklist:")
    for item in items:
        print(f"  [ ] {item}")

rls_checklist()

-- Allow users to read their own files
CREATE POLICY "Users can view own files"
ON files FOR SELECT
USING (auth.uid() = user_id);

-- Allow users to read any public files
CREATE POLICY "Users can view public files"
ON files FOR SELECT
USING (is_public = true);

-- Allow service role to read all files
CREATE POLICY "Service role can view all files"
ON files FOR SELECT
USING (auth.role() = 'service_role');

# select_policy.py
# Understanding SELECT policies

def explain_select_policy():
    policy_sql = """
    CREATE POLICY "Users can view own files"
    ON files FOR SELECT
    USING (auth.uid() = user_id);
    """
    
    print("SELECT Policy Breakdown:")
    print(f"  Policy name: Users can view own files")
    print(f"  Table: files")
    print(f"  Operation: SELECT")
    print(f"  Condition: Only rows where auth.uid() equals user_id column")
    print()
    print("SQL:")
    print(policy_sql)

explain_select_policy()

-- Allow users to insert their own files
CREATE POLICY "Users can create own files"
ON files FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Allow authenticated users to insert any file
CREATE POLICY "Authenticated users can insert"
ON files FOR INSERT
WITH CHECK (auth.role() = 'authenticated');

# insert_policy.py
# INSERT policy example

def write_insert_policy():
    sql = """
    CREATE POLICY "Users can insert own files"
    ON files FOR INSERT
    WITH CHECK (auth.uid() = user_id);
    """
    
    print("INSERT Policy:")
    print("  Ensures users can only create rows where user_id matches their UID")
    print("  WITH CHECK validates the new row against the condition")
    print()
    print("Key difference from SELECT:")
    print("  SELECT policies use USING (existing rows)")
    print("  INSERT policies use WITH CHECK (new rows)")

write_insert_policy()

-- Allow users to update their own files
CREATE POLICY "Users can update own files"
ON files FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Allow users to delete their own files
CREATE POLICY "Users can delete own files"
ON files FOR DELETE
USING (auth.uid() = user_id);

# update_delete_policy.py
# UPDATE and DELETE policies

def write_update_policy():
    print("UPDATE Policy Example:")
    print()
    print("  USING (auth.uid() = user_id) -- which rows can be updated")
    print("  WITH CHECK (auth.uid() = user_id) -- what values can be set")
    print()
    print("DELETE Policy Example:")
    print()
    print("  USING (auth.uid() = user_id) -- which rows can be deleted")
    print()
    print("Note: DELETE only needs USING, not WITH CHECK")

write_update_policy()

def document_rls_policies():
    policies = [
        "CREATE POLICY read_shared ON docs FOR SELECT USING (auth.uid() = owner_id OR auth.uid() IN (SELECT user_id FROM doc_collaborators WHERE doc_id = id))",
        "CREATE POLICY create_own ON docs FOR INSERT WITH CHECK (auth.uid() = owner_id)",
        "CREATE POLICY update_own ON docs FOR UPDATE USING (auth.uid() = owner_id OR auth.uid() IN (SELECT user_id FROM doc_collaborators WHERE doc_id = id AND role = 'editor'))",
        "CREATE POLICY delete_own ON docs FOR DELETE USING (auth.uid() = owner_id)",
    ]
    
    print("Document App RLS Policies:")
    for p in policies:
        print(f"  {p}")

document_rls_policies()