Supabase RLS Security Audit and Fixes with MCP

<Warning>
  **Security First**: Follow [Supabase's security best practices](https://supabase.com/docs/guides/getting-started/mcp#security-risks) when using MCP:
  - Never connect to production databases directly
  - Use development or staging environments
  - Enable read-only mode when possible
  - Scope MCP access to specific projects
</Warning>

The Supabase MCP supports OAuth authentication for secure access:

**OAuth Configuration (Recommended)**

The Supabase MCP will prompt for OAuth authentication when first used. Simply follow the authorization flow.

**Remote MCP URL**
```
https://mcp.supabase.com/mcp
```

<Info>
  The MCP server can be scoped to a specific project for better security. Configure this during setup.
</Info>

1. **Development Environment** - Create a separate development project:
   - Go to [Supabase Dashboard](https://supabase.com/dashboard)
   - Create a new project for development/testing
   - Copy the project URL and anon key

2. **Database Schema** - Set up some initial tables for testing:
   ```sql
   -- Example schema for testing
   CREATE TABLE users (
     id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
     email TEXT UNIQUE NOT NULL,
     created_at TIMESTAMPTZ DEFAULT NOW()
   );

   CREATE TABLE posts (
     id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
     user_id UUID REFERENCES users(id),
     title TEXT NOT NULL,
     content TEXT,
     published BOOLEAN DEFAULT false,
     created_at TIMESTAMPTZ DEFAULT NOW()
   );
   ```

3. **Row Level Security (RLS)** - Enable RLS for security:
   ```sql
   ALTER TABLE users ENABLE ROW LEVEL SECURITY;
   ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
   ```

<Tip>
  Use [Supabase Branching](https://supabase.com/docs/guides/deployment/branching) to create isolated development branches for safe testing.
</Tip>

<Steps>
  <Step title="Add the Pre-Built Agent">
    Visit the [Supabase Agent](https://continue.dev/continuedev/supabase-agent) on Continue Mission Control and click **"Install Agent"** or run:
    
    ```bash
    cn --agent continuedev/supabase-agent
    ```

    This agent includes:
    - **Optimized prompts** for database analysis and query generation
    - **Built-in rules** for SQL best practices and security
    - **[Supabase MCP](https://supabase.com/docs/guides/getting-started/mcp)** for secure database access
    - **Automatic authentication** via OAuth flow
  </Step>

  <Step title="Run Database Analysis">
    Navigate to your project directory and enter this prompt in the Continue CLI TUI:
    
    ```
    Analyze my Supabase database schema and suggest performance optimizations
    ```

    That's it! The agent handles everything automatically.
  </Step>
</Steps>

<Info>
  **Why Use the Agent?** Get consistent results with pre-tested prompts and built-in SQL optimization rules.
</Info>

    The MCP server will automatically prompt for OAuth authentication when you first use it.
  </Step>

  <Step title="Verify Supabase Connection">
    Test your Supabase MCP connection with this prompt:
    
    ```
    List all tables in my Supabase database
    ```
  </Step>

  <Step title="Create Custom Database Analysis Prompts">
    Use this prompt template with Continue CLI to analyze your database:

    ```
    Analyze my Supabase database:
    - List all tables with row counts
    - Identify tables without indexes
    - Find potential N+1 query patterns
    - Suggest missing foreign key constraints
    - Recommend index optimizations based on table structure
    - Generate SQL migrations for suggested improvements
    - Check for security issues (missing RLS policies)
    ```
  </Step>
</Steps>

For each table:
- Check if RLS is enabled
- List all existing RLS policies
- Identify tables without any RLS policies (security risk)
- Find overly permissive policies (e.g., allowing all operations)
- Suggest missing policies based on common patterns
- Generate SQL to fix identified security issues

Prioritize findings by risk level:
1. Critical: Tables with sensitive data but no RLS
2. High: Overly permissive policies
3. Medium: Missing common policies (e.g., users can only see own data)
4. Low: Policy optimization opportunities
```

1. Enable RLS on all tables that don't have it:
   - Include ALTER TABLE statements
   - Add comment explaining why RLS is needed

2. Create secure default policies for common patterns:
   - Users can only read/update their own records
   - Admins have full access (with role checking)
   - Public read-only access where appropriate
   - Proper INSERT policies for new records

3. Fix overly permissive policies:
   - Replace 'true' conditions with proper checks
   - Add user_id or role-based restrictions
   - Include USING clauses for read operations
   - Include WITH CHECK clauses for write operations

4. For each policy, include:
   - Clear naming convention (e.g., 'users_select_own')
   - Comments explaining the security model
   - Rollback statements

Generate the complete migration file with all fixes.
```

I need to secure my Supabase database. Please:

1. First, audit all tables for RLS security issues
2. Generate a complete migration to fix all issues found
3. Create a security report I can share with my team

Here's what I need fixed:
- Enable RLS on all tables
- Create policies so users can only access their own data
- Ensure admins (role = 'admin') have full access
- Add policies for service accounts (role = 'service')
- Include audit logging for sensitive operations

For the users table specifically:
- Users can read their own profile
- Users can update their own profile (except role field)
- Only admins can view all users
- Only admins can delete users
- New users can insert their own record during signup

Generate the complete migration with:
- All SQL statements
- Clear policy names and comments
- Rollback statements
- A summary of what each policy does

name: Database Health Monitor

on:
  schedule:
    # Run daily at 2 AM UTC
    - cron: "0 2 * * *"
  workflow_dispatch: # Allow manual triggers

jobs:
  monitor-database:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-node@v4
        with:
          node-version: "22"

      - name: Install Continue CLI
        run: |
          npm install -g @continuedev/cli
          echo "✅ Continue CLI installed"

      - name: Analyze Database Security
        env:
          CONTINUE_API_KEY: ${{ secrets.CONTINUE_API_KEY }}
        run: |
          echo "🔍 Performing security audit..."
          
          # Use Continue CLI to audit RLS and generate fixes
          cn -p "Using Supabase MCP, perform a comprehensive RLS security audit:
                 1. Check all tables for RLS enablement
                 2. Identify tables with missing or weak RLS policies
                 3. Find overly permissive policies (e.g., 'true' conditions)
                 4. Check for common security anti-patterns:
                    - Missing user_id checks
                    - No role-based access control
                    - Unrestricted DELETE operations
                    - Missing WITH CHECK clauses
                 5. Generate fixes for all security issues found
                 6. Create a security report with:
                    - Critical vulnerabilities (tables without RLS)
                    - High-risk policies that need immediate fixes
                    - SQL migrations to fix all issues
                    - Best practice recommendations
                    - Compliance checklist (GDPR, SOC2, etc.)
                 
                 If critical security issues are found:
                 - Generate the complete fix migration
                 - Create a GitHub issue with severity labels
                 - Tag security team members for review"

      - name: Save Health Report
        run: |
          echo "## 📊 Database Health Report" >> $GITHUB_STEP_SUMMARY
          echo "Generated at $(date)" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "Check the 'Analyze Database Security' step for the full report" >> $GITHUB_STEP_SUMMARY

      - name: Upload Health Report
        uses: actions/upload-artifact@v4
        with:
          name: database-health-report
          path: |
            *.md
            *.sql