backend/sql/README.md

# Supabase Row Level Security (RLS) Configuration

## 🔒 Security Issue Resolution

This directory contains SQL scripts to fix the Row Level Security (RLS) issues identified by Supabase Security Advisor.

## 📋 Identified Issues

The following tables were reported as having RLS disabled:
- `public.agents`
- `public.agent_versions`
- `public.datasources`
- `public.documents`
- `public.chunks`
- `public.embeddings`
- `public.collections`
- `public.collection_items`
- `public.pipeline_runs`
- `public.chat_logs`

## 🛠️ Available Scripts

### 1. `enable_rls.sql` - Basic RLS Setup
- Enables RLS on all tables
- Creates policies for service role access only
- Quick fix for immediate security

### 2. `rls_with_auth.sql` - Advanced RLS with User Authentication
- Adds user ownership tracking
- Creates granular access policies
- Supports multi-user scenarios
- Includes performance indexes

## 📝 How to Apply RLS Configuration

### Option 1: Via Supabase Dashboard

1. Go to your Supabase project dashboard
2. Navigate to **SQL Editor**
3. Copy and paste the content of `enable_rls.sql` (for basic) or `rls_with_auth.sql` (for advanced)
4. Click **Run** to execute the script
5. Verify in **Security Advisor** that all RLS errors are resolved

### Option 2: Via Supabase CLI

```bash
# Install Supabase CLI if not already installed
npm install -g supabase

# Link to your project
supabase link --project-ref your-project-ref

# Run the SQL script
supabase db push --file ./backend/sql/enable_rls.sql

# Or for advanced setup
supabase db push --file ./backend/sql/rls_with_auth.sql
```

### Option 3: Via psql

```bash
# Connect to your database
psql "postgresql://postgres:[YOUR-PASSWORD]@[YOUR-PROJECT-REF].supabase.co:5432/postgres"

# Execute the script
\i backend/sql/enable_rls.sql
```

## ⚠️ Important Notes

### For Backend API

1. **Use Service Role Key**: The backend API should use the `service_role` key from Supabase
   ```python
   # In your .env file
   SUPABASE_SERVICE_ROLE_KEY=your-service-role-key-here
   ```

2. **Never Expose Service Role Key**: This key bypasses RLS and should never be exposed to the client

3. **Update Backend Configuration**: Ensure your backend is using the service role key:
   ```python
   # backend/config.py
   supabase = create_client(
       settings.SUPABASE_URL,
       settings.SUPABASE_SERVICE_ROLE_KEY  # Use service role key
   )
   ```

### For Frontend

1. **Use Anon Key**: Frontend should only use the `anon` key
2. **No Direct Database Access**: Frontend should only interact via backend API endpoints

## 🔍 Verification

After applying RLS:

1. **Check Security Advisor**:
   - Go to Supabase Dashboard > Security Advisor
   - All RLS errors should be resolved
   - You should see 0 errors

2. **Test API Endpoints**:
   ```bash
   # Test document upload
   curl -X POST http://localhost:8000/api/ingest/files \
     -F "file=@test.pdf"

   # Test search
   curl -X POST http://localhost:8000/api/search/ \
     -H "Content-Type: application/json" \
     -d '{"query": "test", "top_k": 5}'
   ```

3. **Verify Policies**:
   ```sql
   -- Check enabled RLS
   SELECT tablename, rowsecurity
   FROM pg_tables
   WHERE schemaname = 'public';

   -- View policies
   SELECT * FROM pg_policies
   WHERE schemaname = 'public';
   ```

## 🔄 Rollback (If Needed)

If you need to disable RLS (not recommended for production):

```sql
-- Disable RLS on all tables
ALTER TABLE public.agents DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.agent_versions DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.datasources DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.documents DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.chunks DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.embeddings DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.collections DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.collection_items DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.pipeline_runs DISABLE ROW LEVEL SECURITY;
ALTER TABLE public.chat_logs DISABLE ROW LEVEL SECURITY;

-- Drop all policies
DROP POLICY IF EXISTS "Service role can manage agents" ON public.agents;
-- ... (repeat for all policies)
```

## 📚 Additional Resources

- [Supabase RLS Documentation](https://supabase.com/docs/guides/auth/row-level-security)
- [PostgreSQL RLS Guide](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
- [Supabase Security Best Practices](https://supabase.com/docs/guides/platform/going-into-prod#security)

## ✅ Checklist

- [ ] Choose appropriate RLS script (basic vs advanced)
- [ ] Execute script in Supabase
- [ ] Update backend to use service_role key
- [ ] Verify in Security Advisor
- [ ] Test all API endpoints
- [ ] Document any custom policies needed