# Supabase Security Audit

> Run a comprehensive Supabase security audit with ASCII visual report. Checks RLS policies, function permissions, storage policies, API exposure, and auth config. Use before going to production or after any security concern.

- Skill: `carolmonroe22/supabase-security-audit` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx skillmds@latest add carolmonroe22/supabase-security-audit`
- Raw SKILL.md: https://api.skillmd.com/api/skills/carolmonroe22/supabase-security-audit/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: carolmonroe22 (https://skillmd.com/u/carolmonroe22)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/carolmonroe22/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:

```sql
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:

```sql
-- 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;
```

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

---

## Interpreting Results

Map each `check_type` from the query to a check:

| check_type | Check Name | If found = |
|-----------|------------|------------|
| `NO_RLS` | RLS on all tables | FAIL |
| `PUBLIC_WRITE` | No public write policies | FAIL |
| `PERMISSIVE_WRITE` | No overly permissive writes | WARN |
| `PUBLIC_SELECT` | SELECT policies reviewed | WARN |
| `ANON_FUNCTION` | No anon functions | FAIL |
| `SEC_DEFINER` | SECURITY DEFINER reviewed | REVIEW |
| `STORAGE_PUBLIC_WRITE` | Storage policies | FAIL |

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

```sql
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:

```sql
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:

```sql
-- 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:
```sql
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

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

### Review: SECURITY DEFINER Functions

Get full definition:
```sql
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

```sql
-- 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)

```sql
-- 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.