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

# 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

# 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

   # 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:

   # 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:

   # 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:

   -- 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):

-- 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
• PostgreSQL RLS Guide
• Supabase Security Best Practices

✅ 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