> ## Documentation Index
> Fetch the complete documentation index at: https://continue-docs-dependabot-npm-and-yarn-docs-typeorm-0-3-30.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Supabase RLS Security Audit and Fixes with MCP
> Learn how to audit Row Level Security in your Supabase database, identify vulnerabilities, and automatically generate fixes using Continue CLI and Supabase MCP.
export const OSAutoDetect = () => {
return ;
};
A security audit workflow that uses Continue CLI with Supabase MCP to identify RLS vulnerabilities, generate secure policies, fix permission issues, and ensure your database follows security best practices.
## What You'll Learn
This cookbook teaches you to:
* Use [Supabase MCP](https://supabase.com/docs/guides/getting-started/mcp) to audit database security
* Identify tables without Row Level Security (RLS) enabled
* Find and fix overly permissive or missing RLS policies
* Generate secure RLS migrations following best practices
* Automate security audits with GitHub Actions
## Prerequisites
Before starting, ensure you have:
* [Supabase account](https://supabase.com) with an active project
* Node.js 18+ installed locally
* [Continue CLI](https://docs.continue.dev/guides/cli) with **active credits** (required for API usage)
* Basic understanding of SQL and database concepts
```bash theme={null}
curl -fsSL https://raw.githubusercontent.com/continuedev/continue/main/extensions/cli/scripts/install.sh | bash
```
```powershell theme={null}
irm https://raw.githubusercontent.com/continuedev/continue/main/extensions/cli/scripts/install.ps1 | iex
```
If you already have Node.js 20+:
```bash theme={null}
npm i -g @continuedev/cli
```
1. Visit [Continue Organizations](https://continue.dev/settings/organizations)
2. Sign up or log in to your Continue account
3. Navigate to your organization settings
4. Click **"API Keys"** and then **"+ New API Key"**
5. Copy the API key immediately (you won't see it again!)
6. Login to the CLI: `cn login`
Continue CLI can analyze your database schema and generate complex SQL queries - you just need to describe what you want in plain language!
## Step 1: Set Up Your Credentials
First, you'll need to set up access to your Supabase project.
**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
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
```
The MCP server can be scoped to a specific project for better security. Configure this during setup.
To use Supabase MCP effectively, ensure your project has:
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 theme={null}
-- 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 theme={null}
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
```
Use [Supabase Branching](https://supabase.com/docs/guides/deployment/branching) to create isolated development branches for safe testing.
## Supabase Database Workflow Options
Skip the manual setup and use our pre-built Supabase Continuous AI agent that includes
optimized prompts, rules, and the Supabase MCP for intelligent database management.
**How Supabase MCP Works**:
* Connects to your Supabase project via OAuth
* Provides tools for database queries, schema inspection, and migrations
* Supports read-only mode for safer operations
* Can be scoped to specific projects for security
**Perfect for:** Immediate database analysis with AI-powered query optimization and schema insights
Run:
```bash theme={null}
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
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.
**Why Use the Agent?** Get consistent results with pre-tested prompts and built-in SQL optimization rules.
Configure the [Supabase MCP](https://supabase.com/docs/guides/getting-started/mcp) using OAuth:
The MCP server will automatically prompt for OAuth authentication when you first use it.
Test your Supabase MCP connection with this prompt:
```
List all tables in my Supabase database
```
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)
```
To use the pre-built agent, you need either:
* **Continue CLI Pro Plan** with the models add-on, OR
* **Your own API keys** configured as environment variables
The agent will automatically detect and use your configuration. For Supabase MCP:
* **Supabase account** with at least one project
* **OAuth authentication** (handled automatically)
* Development or staging environment (not production)
***
**Security Best Practices**:
* **Never use MCP with production databases** - Always use development or staging environments
* **Enable read-only mode** when analyzing data to prevent accidental modifications
* **Scope to specific projects** to limit access
* **Use branch databases** for testing schema changes
## Step 2: Analyze Your Database with AI
Use Continue CLI to perform intelligent database analysis. Enter these prompts in the Continue CLI TUI:
**Prompt:**
```
Analyze my Supabase database schema and provide:
- Complete table structure with data types
- Relationships between tables
- Missing indexes that could improve performance
- Unused or redundant columns
- Suggestions for normalization improvements
```
**Prompt:**
```
Perform a comprehensive RLS (Row Level Security) audit on my Supabase database:
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
```
**Prompt:**
```
Based on the RLS audit, generate SQL migrations to fix all security issues:
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.
```
**Available Supabase MCP Capabilities**:
* **Database Queries**: Execute SQL queries and analyze results
* **Schema Inspection**: View table structures, relationships, and constraints
* **Performance Analysis**: Identify slow queries and optimization opportunities
* **Security Review**: Check RLS policies and access controls
* **Migration Generation**: Create SQL migrations for schema changes
## Step 3: Generate Database Migrations
Create and apply database migrations based on AI recommendations. Enter this prompt in the Continue CLI TUI:
**Example: Complete RLS Security Fix**
```
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
```
**Expected Output:**
The AI will generate a complete SQL migration file that:
* Enables RLS on all tables
* Creates secure, well-documented policies
* Follows Supabase security best practices
* Includes rollback capabilities
* Provides a security summary for your team
**Best Practice**: Always review AI-generated migrations before applying them. Test in a development branch first using Supabase's branching feature.
## Step 4: Set Up Automated Database Monitoring
Automate database health checks with Continue CLI and GitHub Actions:
```yaml theme={null}
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
```
**Required GitHub Secrets**:
* `CONTINUE_API_KEY`: Your Continue API key from [continue.dev/settings/api-keys](https://continue.dev/settings/api-keys)
Add this at: **Repository Settings → Secrets and variables → Actions**
## What You've Built
After completing this guide, you have a complete **AI-powered database management system** that:
* **Analyzes schema design** - Identifies optimization opportunities and best practices
* **Optimizes queries** - Suggests indexes and query rewrites for better performance
* **Generates migrations** - Creates SQL code for schema improvements
* **Monitors health** - Runs automated checks for performance and security issues
* **Provides insights** - Delivers actionable recommendations based on data patterns
Your system now operates at **[Level 2 Continuous AI](https://blog.continue.dev/what-is-continuous-ai-a-developers-guide/)** - AI handles routine database analysis and optimization with human oversight for migration approval.
## Advanced Database Prompts
Enhance your workflow with these advanced Continue CLI prompts:
Monitor query performance in real-time and get alerts for slow queries exceeding threshold times
Automatically validate data integrity, find duplicates, and ensure consistency across related tables
Analyze API logs to understand data access patterns and optimize indexes accordingly
Review database usage and suggest ways to reduce costs while maintaining performance
## Security Best Practices
**Database Security Guidelines**:
* Always use development/staging environments with MCP
* Enable read-only mode for analysis tasks
* Never store production credentials in code
* Review all AI-generated SQL before execution
* Use RLS policies to enforce access control
* Regularly audit database permissions
## Troubleshooting
### Supabase MCP Connection Issues
If you encounter connection issues:
1. Verify OAuth authentication is complete
2. Check your Supabase project is accessible
3. Ensure you're not connecting to a production database
4. Verify the MCP URL is correct: `https://mcp.supabase.com/mcp`
### Common Database Analysis Issues
| Issue | Solution |
| :------------------ | :------------------------------------------ |
| No tables found | Verify your database has tables created |
| Permission denied | Check OAuth scopes and project permissions |
| Slow query analysis | Ensure your database has query logs enabled |
| Migration failures | Test migrations in a branch database first |
## Next Steps
* Set up [Supabase Edge Functions](https://supabase.com/docs/guides/functions) for automated workflows
* Configure [Supabase Realtime](https://supabase.com/docs/guides/realtime) for live data monitoring
* Implement [Database Webhooks](https://supabase.com/docs/guides/database/webhooks) for event-driven automation
## Resources
* [Supabase MCP Documentation](https://supabase.com/docs/guides/getting-started/mcp)
* [Supabase Database Guide](https://supabase.com/docs/guides/database)
* [Supabase Security Best Practices](https://supabase.com/docs/guides/database/security)
* [Model Context Protocol Docs](https://modelcontextprotocol.io/introduction)
* [Continue CLI Guide](https://docs.continue.dev/guides/cli)
* [Continuous AI Best Practices](https://blog.continue.dev/what-is-continuous-ai-a-developers-guide/)