# Audit Item C5: Fix RLS INSERT policies — matches + payments allow ANY user to insert (WITH CHECK true)

**Status:** ✅ COMPLETED  
**Date:** 2026-02-07  
**Auditor:** Clawdbot Subagent  
**Priority:** CRITICAL (Security Vulnerability)

## Summary
Fixed critical Row Level Security (RLS) policies that allowed ANY authenticated user to insert records into the `matches` and `payments` tables. This was a severe security vulnerability that could lead to data corruption, financial fraud, and system abuse.

## Security Risk Analysis

### **Original Vulnerable Policies:**
1. **Matches table**: `CREATE POLICY "System can insert matches" ON matches FOR INSERT WITH CHECK (true);`
2. **Payments table**: `CREATE POLICY "System can insert payments" ON payments FOR INSERT WITH CHECK (true);`

### **What `WITH CHECK (true)` Means:**
- Any user with a valid JWT token (any authenticated user) can insert records
- No restrictions on what data they can insert
- Complete bypass of authorization checks

### **Potential Exploits:**
- Users could insert fake matches between any job and worker
- Users could insert fake payment records
- Financial fraud through manipulated payment records
- Data corruption by linking unrelated entities
- System abuse through spam/malicious data insertion

## Changes Made

### **1. Fixed Matches Table INSERT Policy**
**New Policy:** `"Secure matches insert"`
```sql
WITH CHECK (
  -- Worker applying for a job
  (worker_id = auth.uid() AND status = 'applied')
  OR
  -- Contractor suggesting a worker for their job
  (
    job_id IN (
      SELECT j.id FROM jobs j
      JOIN companies c ON j.company_id = c.id
      WHERE c.profile_id = auth.uid()
    )
    AND status = 'suggested'
  )
  OR
  -- System/service role (for automated matching)
  (auth.jwt() ->> 'role' = 'service_role')
)
```

**What it allows:**
- ✅ Workers can apply for jobs (must use their own worker_id)
- ✅ Contractors can suggest matches for their own jobs
- ✅ System/service role for automated matching algorithms
- ❌ Users cannot insert matches for other users
- ❌ Users cannot insert matches for jobs they don't own

### **2. Fixed Payments Table INSERT Policy**
**New Policy:** `"Secure payments insert"`
```sql
WITH CHECK (
  -- Must be service role
  (auth.jwt() ->> 'role' = 'service_role')
  AND
  -- Payment must be for a hired match
  EXISTS (
    SELECT 1 FROM matches 
    WHERE id = match_id AND status = 'hired'
  )
  AND
  -- Payment company must own the job
  EXISTS (
    SELECT 1 FROM matches m
    JOIN jobs j ON m.job_id = j.id
    WHERE m.id = match_id AND j.company_id = company_id
  )
)
```

**What it allows:**
- ✅ Only system/service role can insert payments
- ✅ Payments only for matches with status 'hired'
- ✅ Payment company must own the job
- ❌ No user can directly insert payment records
- ❌ No payments for non-hired matches

### **3. Added Data Validation Trigger**
Created `validate_payment_creation()` trigger that:
- Ensures payments are only created for hired matches
- Ensures payment company owns the job
- Provides clear error messages for violations

## Migration File
Created: `/home/ccuser/the-50-dollar-app/supabase/fix-critical-rls-insert-policies.sql`

**To apply the fix:**
1. Go to Supabase SQL Editor
2. Copy and paste the entire migration file
3. Run the SQL script
4. Verify policies with the verification queries

## Impact on Application

### **Frontend/API Changes Required:**
1. **Match Creation:**
   - Worker apply: Frontend must call API endpoint, not direct Supabase insert
   - Contractor invite: Frontend must call API endpoint
   - API will validate authorization before inserting

2. **Payment Creation:**
   - Must happen server-side after Stripe payment success
   - Use service role key in server-side code
   - Cannot be done directly from frontend

3. **Service Role Key:**
   - Need to use Supabase service role key for server-side operations
   - Keep this key secure (server-side only)

### **Backward Compatibility:**
- Existing data remains intact
- Application will break if frontend tries to insert matches/payments directly
- API endpoints need to be updated to handle authorization

## Verification

### **SQL to Verify New Policies:**
```sql
SELECT 
  tablename,
  policyname,
  cmd,
  qual,
  with_check
FROM pg_policies 
WHERE tablename IN ('matches', 'payments')
ORDER BY tablename, policyname;
```

### **Expected Output:**
```
 tablename |         policyname         |  cmd   |                         qual                          |                       with_check
-----------+----------------------------+--------+-------------------------------------------------------+-------------------------------------------------------
 matches   | Secure matches insert      | INSERT |                                                       | ((worker_id = auth.uid()) AND (status = 'applied'::match_status)) OR ...
 matches   | Users can view their own matches | SELECT | (worker_id = auth.uid()) OR ...                      |
 matches   | Involved parties can update matches | UPDATE | (worker_id = auth.uid()) OR ...                      |
 payments  | Company owners can view their payments | SELECT | (company_id IN ( SELECT companies.id ... ))          |
 payments  | Secure payments insert     | INSERT |                                                       | ((auth.jwt() ->> 'role' = 'service_role'::text)) AND ...
```

## Rollback Procedure
In case of issues, use the rollback script in the migration file. However, the original policies were **INSECURE** and should only be restored temporarily for debugging.

## Next Steps

1. **Apply Migration:** Run the SQL script in Supabase
2. **Update API Endpoints:** Ensure match/payment creation goes through authorized endpoints
3. **Test Thoroughly:** 
   - Worker applying for jobs
   - Contractor inviting workers
   - Payment flow after hire
4. **Monitor Logs:** Watch for authorization failures during transition

## Security Improvement
This fix transforms the system from "completely open to abuse" to "properly secured with least privilege access." Each user can only perform actions that make business sense for their role.

**Risk Level Before:** 🔴 CRITICAL (9/10)  
**Risk Level After:** 🟢 LOW (2/10)