Supabase Security Audit Prompt (MCP-Executable)

Please perform a comprehensive security audit of this Supabase database using the Supabase MCP tools.

IMPORTANT: After analysis, present your findings and proposed migrations. Wait for my confirmation before applying any migrations to the database.

Follow these steps:

1. Get Security Advisories

Use Supabase MCP to fetch all security advisors:

Use get_advisors with type: "security" and project_id

Review all security recommendations and categorize by severity (CRITICAL, WARN, INFO).

2. Audit RLS Policies

Query the database to analyze all Row Level Security policies. Check for:

• Missing RLS: Tables without RLS enabled or without any policies
• Overly permissive policies: Policies that allow unrestricted access
• Public role exposure: Tables accessible to anonymous users that shouldn't be
• Multiple policies on same operation: Redundant or conflicting policies

3. Review Database Roles and Permissions

Query database roles and their table-level permissions. Identify any excessive privileges granted to roles.

4. Check Foreign Key Constraints

Query all foreign key relationships. Check for:

• Missing CASCADE rules that could lead to orphaned data
• Referential integrity issues

5. Audit Tables for Sensitive Data

Query all tables and columns to identify those containing sensitive data (passwords, tokens, personal information, etc.). Verify they have appropriate RLS policies protecting them.

6. Analyze Auth Schema Usage

Check that auth functions (auth.uid(), auth.jwt(), etc.) are called correctly and securely in RLS policies.

7. Create Remediation Plan

For each security issue found from the queries above, create SQL migration files to fix:

• Enable missing RLS: For tables without RLS enabled, create ALTER TABLE ... ENABLE ROW LEVEL SECURITY statements
• Add missing policies: For tables with RLS enabled but no policies, create appropriate policies based on the table's purpose
• Fix overly permissive policies: Drop policies with USING (true) or missing conditions, replace with restrictive policies
• Restrict public access: Modify policies that allow anon role access to sensitive tables

8. Present Plan and Wait for Confirmation

Before applying any changes:

1. Show all proposed SQL migrations
2. Explain the security risk each migration addresses
3. Highlight any potential impacts on application functionality
4. Wait for explicit confirmation before proceeding

9. Apply Migrations (After Confirmation)

Once confirmed, use apply_migration to apply security fixes:

• Apply each migration with descriptive names (e.g., enable_rls_on_users)
• Save all SQL to your project's migration folder
• Regenerate TypeScript types using generate_typescript_types

---

Output Format

Please provide:

1. Executive summary: Overall security posture of the database
2. Detailed findings: Organized by severity (Critical/High/Medium/Low)
3. Remediation migrations: SQL migration files for each security issue
4. Verification steps: How to verify fixes were applied correctly

Notes

• Only use Supabase MCP tools (execute_sql, get_advisors, apply_migration, generate_typescript_types)
• Focus exclusively on database-level security (RLS, policies, permissions)
• All fixes should be SQL migrations that can be applied via MCP
• Do not include application-level security checks