Supabase Security Audit

Supabase Security Audit

Run a full security audit on a Supabase project. Outputs a visual ASCII report with pass/fail/warn status for each check.

Requirements

• Supabase MCP plugin connected (mcp__plugin_supabase_supabase__execute_sql)
• Project ID or connected project

Tools

Use mcp__plugin_supabase_supabase__execute_sql for all SQL queries. Use mcp__plugin_supabase_supabase__get_project to confirm the project. If the user provides a project ID, pass it as the project_id parameter.

Workflow

1. Confirm which Supabase project to audit
2. Run the Quick Full Audit query first (single query, all checks)
3. For any findings, run the detailed step queries
4. Before migrating any PUBLIC_SELECT policies: check if the table is used on unauthenticated pages (see "Landing Page Check" below)
5. Present the ASCII visual report
6. Offer to auto-fix issues found

Quick Full Audit (run this first)

Single query that covers all automated checks:

SELECT 'NO_RLS' as check_type, tablename as name, '' as detail
FROM pg_tables WHERE schemaname = 'public' AND NOT rowsecurity

UNION ALL

SELECT 'PUBLIC_WRITE', tablename, policyname
FROM pg_policies
WHERE schemaname = 'public' AND cmd IN ('INSERT','UPDATE','DELETE','ALL')
  AND roles::text LIKE '%public%'

UNION ALL

SELECT 'PERMISSIVE_WRITE', tablename, policyname || ' (' || cmd || ')'
FROM pg_policies
WHERE schemaname = 'public' AND cmd IN ('INSERT','UPDATE','DELETE','ALL')
  AND (qual = 'true' OR with_check = 'true')

UNION ALL

SELECT 'PUBLIC_SELECT', tablename, policyname
FROM pg_policies
WHERE schemaname = 'public' AND cmd = 'SELECT'
  AND roles::text LIKE '%public%'

UNION ALL

SELECT 'ANON_FUNCTION', routine_name, ''
FROM information_schema.routine_privileges
WHERE routine_schema = 'public' AND grantee = 'anon'

UNION ALL

SELECT 'SEC_DEFINER', p.proname, ''
FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public' AND p.prosecdef = true

UNION ALL

SELECT 'STORAGE_PUBLIC_WRITE', policyname, cmd
FROM pg_policies
WHERE schemaname = 'storage' AND cmd IN ('INSERT','UPDATE','DELETE')
  AND roles::text LIKE '%public%'

ORDER BY check_type, name;

Also run these supplementary queries:

-- Sensitive columns on public SELECT tables
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public'
  AND column_name IN (
    'is_admin', 'is_banned', 'is_staff', 'role',
    'email', 'phone', 'password_hash',
    'tokens', 'api_key', 'secret',
    'spotify_token', 'provider_token', 'refresh_token',
    'ip_address', 'last_login_ip'
  )
ORDER BY table_name;

-- Storage buckets
SELECT id, name, public FROM storage.buckets ORDER BY name;

Interpreting Results

Map each check_type from the query to a check:

If a check_type has zero rows, it PASSES.

ASCII Visual Report

ALWAYS output the results using this ASCII format. This is the signature output of this skill.

Building the report

Count the results per check_type from the query. Then build the ASCII box:

• [✓] = PASS (0 findings)
• [✗] = FAIL (findings in NO_RLS, PUBLIC_WRITE, ANON_FUNCTION, STORAGE_PUBLIC_WRITE)
• [!] = WARN (findings in PERMISSIVE_WRITE, PUBLIC_SELECT)
• [~] = REVIEW (findings in SEC_DEFINER - needs human review)
• [ ] = MANUAL (auth config, API exposure - can't check via SQL)

Progress bars:

• ████████ = PASS
• ████░░░░ = WARN/REVIEW (with count)
• ░░░░░░░░ = FAIL or MANUAL

Score = (PASS checks) / (total automated checks, which is 7)

Template (all passing):

╔══════════════════════════════════════════════════╗
║           SUPABASE SECURITY AUDIT                ║
║           {project_name} · {project_id}          ║
╠══════════════════════════════════════════════════╣
║                                                  ║
║  [✓] RLS on all tables            ████████  OK   ║
║  [✓] No public write policies     ████████  OK   ║
║  [✓] No overly permissive writes  ████████  OK   ║
║  [✓] SELECT policies reviewed     ████████  OK   ║
║  [✓] No anon functions            ████████  OK   ║
║  [✓] SECURITY DEFINER reviewed    ████████  OK   ║
║  [✓] Storage policies             ████████  OK   ║
║  [ ] Auth config                  ░░░░░░░░  --   ║
║  [ ] API exposure                 ░░░░░░░░  --   ║
║                                                  ║
║  Score: 7/7  ████████████████████  100%           ║
║                                                  ║
║  ALL AUTOMATED CHECKS PASSED                     ║
║  Don't forget manual checks (auth + API)         ║
║                                                  ║
║  [✓] pass  [!] warn  [~] review  [ ] manual      ║
╚══════════════════════════════════════════════════╝

Template (with issues):

╔══════════════════════════════════════════════════╗
║           SUPABASE SECURITY AUDIT                ║
║           {project_name} · {project_id}          ║
╠══════════════════════════════════════════════════╣
║                                                  ║
║  [✗] RLS on all tables            ░░░░░░░░   3   ║
║  [✗] No public write policies     ░░░░░░░░  12   ║
║  [!] No overly permissive writes  ████░░░░   5   ║
║  [!] SELECT policies reviewed     ████░░░░  11   ║
║  [✗] No anon functions            ░░░░░░░░   4   ║
║  [~] SECURITY DEFINER reviewed    ██████░░   2   ║
║  [✗] Storage policies             ░░░░░░░░   3   ║
║  [ ] Auth config                  ░░░░░░░░  --   ║
║  [ ] API exposure                 ░░░░░░░░  --   ║
║                                                  ║
║  Score: 0/7  ░░░░░░░░░░░░░░░░░░░░   0%           ║
║                                                  ║
║  CRITICAL ISSUES FOUND - FIX BEFORE PRODUCTION   ║
║                                                  ║
║  [✓] pass  [✗] fail  [!] warn  [~] review        ║
╚══════════════════════════════════════════════════╝

Score thresholds:

• 7/7 (100%): "ALL AUTOMATED CHECKS PASSED"
• 5-6/7: "MOSTLY SECURE - review warnings"
• 3-4/7: "NEEDS ATTENTION - fix failures"
• 0-2/7: "CRITICAL ISSUES FOUND - FIX BEFORE PRODUCTION"

After the ASCII box, list details for each non-passing check with the specific tables/policies/functions found.

Detailed Steps (for remediation)

Fix: Tables Without RLS

ALTER TABLE public.<table_name> ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.<table_name> FORCE ROW LEVEL SECURITY;

Fix: Public Write Policies

Migrate from {public} to {authenticated}. Important - each command type has different syntax:

DROP POLICY "<policy_name>" ON public.<table_name>;

-- For INSERT (WITH CHECK only, no USING):
CREATE POLICY "<policy_name>" ON public.<table_name>
  FOR INSERT TO authenticated
  WITH CHECK (<original_condition>);

-- For UPDATE (both USING and WITH CHECK):
CREATE POLICY "<policy_name>" ON public.<table_name>
  FOR UPDATE TO authenticated
  USING (<original_using>)
  WITH CHECK (<original_with_check>);

-- For DELETE (USING only, no WITH CHECK):
CREATE POLICY "<policy_name>" ON public.<table_name>
  FOR DELETE TO authenticated
  USING (<original_condition>);

Fix: Overly Permissive Writes

Replace true with auth.uid() = user_id where appropriate:

-- Instead of WITH CHECK (true):
WITH CHECK (auth.uid() = user_id)

-- Instead of USING (true) on UPDATE/DELETE:
USING (auth.uid() = user_id)

Tables where true is OK: shared/collaborative tables, admin-managed lookup tables.

Fix: Public SELECT with Sensitive Columns

IMPORTANT - Landing Page Check: Before migrating ANY public SELECT policy to {authenticated}, you MUST verify the table is not used on unauthenticated pages (landing page, marketing pages, public profiles, etc.).

How to check:

1. If you have access to the codebase, search for the table name in the frontend code:
   • Look for supabase.from("<table_name>") in components used by public routes
   • Check the router to identify which pages don't require auth
   • Trace the component tree from public pages to find all Supabase queries
2. If you don't have codebase access, ASK the user: "Is <table_name> used on any page that doesn't require login (landing page, public profiles, etc.)?"

Tables that commonly need to stay public:

• Artist/creator profiles displayed on landing pages
• Published content shown to visitors (blog posts, listings)
• Public directories or catalogs
• Any data shown before the user logs in

If the table IS used without auth: Keep it on {public} but verify it has no sensitive columns. If it has sensitive columns, create a Postgres VIEW that excludes them and point the public policy at the view.

If the table is NOT used without auth: Safe to migrate:

DROP POLICY "<policy_name>" ON public.<table_name>;
CREATE POLICY "<policy_name>" ON public.<table_name>
  FOR SELECT TO authenticated USING (<condition>);

Lesson learned: Migrating a public SELECT policy on a table used by the landing page will break the landing page silently - queries return 0 rows with no error, and the UI just shows empty.

Fix: Anon-callable Functions

REVOKE EXECUTE ON FUNCTION public.<function_name> FROM anon;

Review: SECURITY DEFINER Functions

Get full definition:

SELECT p.proname, pg_get_functiondef(p.oid)
FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public' AND p.prosecdef = true;

Check that each function:

• Has auth.uid() checks if it accesses user data
• Uses SET search_path TO '' (prevents search_path hijacking)
• Actually needs DEFINER (trigger functions usually do, helpers might not)

Fix: Storage Write Policies on Public

-- Same pattern: DROP + recreate with authenticated role
DROP POLICY "<policy_name>" ON storage.objects;

-- For INSERT:
CREATE POLICY "<policy_name>" ON storage.objects
  FOR INSERT TO authenticated
  WITH CHECK (<original_condition>);

-- For UPDATE:
CREATE POLICY "<policy_name>" ON storage.objects
  FOR UPDATE TO authenticated
  USING (<original_condition>);

-- For DELETE:
CREATE POLICY "<policy_name>" ON storage.objects
  FOR DELETE TO authenticated
  USING (<original_condition>);

Also check for missing DELETE policies on buckets where users upload.

Manual Checks (remind user)

Auth Configuration

Check in Dashboard (Authentication > Providers > Email):

• Leaked Password Protection enabled (requires Pro plan)
• Email templates customized
• Redirect URLs configured (no wildcards in production)
• Email confirmations enabled
• Rate limiting on auth endpoints

API Exposure

Check in Dashboard (Settings > API):

• Swagger/OpenAPI endpoint restricted
• Max rows configured (default 1000 is fine)
• "Harden Data API" considered for custom schema migration

Key RLS Patterns (reference)

-- User reads own data only
FOR SELECT TO authenticated USING (auth.uid() = user_id)

-- User inserts own data only
FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id)

-- User reads own + public content
FOR SELECT TO authenticated USING (auth.uid() = user_id OR is_public = true)

-- Any authenticated user can read (shared content)
FOR SELECT TO authenticated USING (true)

-- Only owner can update/delete
FOR UPDATE TO authenticated USING (auth.uid() = user_id)
FOR DELETE TO authenticated USING (auth.uid() = user_id)

Why This Exists

Supabase and Lovable defaults create RLS policies with the {public} role. This means unauthenticated users (and anyone with cURL) can read/write data through the REST API. CORS does not protect against direct API calls - only browsers respect CORS headers.

This is fine for development but must be hardened before production. This skill automates that audit.