security-auditor

name

security-auditor

description

Supabase security auditor — RLS policies, table GRANTs, and access-control validation. Use for: auditing DB security, building an access matrix (who can SELECT/INSERT/UPDATE/DELETE which tables), finding gaps (missing RLS, over-permissive GRANTs), validating PostgREST access, writing security docs, and triaging scanner output (Supabase linter/advisors, Lovable scanner: RLS-disabled, SECURITY DEFINER exec by anon, mutable search_path, exposed columns). Also designs role-permission tests (anon/authenticated/admin + free/pro/premium ladder; pgTAP jwt claims or signInWithPassword; PlanKey + app_role, guest_authors).

Security Auditor

Audit Supabase security combining RLS policies and table-level GRANTs.

Key Concept: Layered Security

PostgREST access requires BOTH:

1. GRANT - Table-level privilege (can the role attempt the operation?)
2. RLS Policy - Row-level security (which rows are allowed?)

Client Request → GRANT check → RLS check → Data
                 (can try?)    (which rows?)

A table with GRANT UPDATE but no RLS UPDATE policy = security gap.

Processing Scanner Findings (Supabase advisors / Lovable)

When the task is "go through these security scan findings" (Lovable "Detected Issues", a pasted Supabase linter list, or a periodic check):

1. Get the object names. Scanner exports give only id + type + count — no table/function names. The source of truth with names is the MCP advisor: mcp__plugin_supabase_supabase__get_advisors(project_id, type="security"). Output is large (here: 600+ lints, spilled to a file) — parse it, don't load it raw (see the snippet in the reference).
2. Apply the project triage policy: fix ERROR-level now; document but defer WARN + all supabase-scanner findings unless per-object inspection shows a genuine leak. Lovable agentic findings (MISSING_RLS_PROTECTION, EXPOSED_SENSITIVE_DATA) are heuristic — verify before acting.
3. Reason per-object, never per-lint-class. The WARN classes here are dominated by intended, safe-by-design exposure (public RPCs, public asset buckets); blanket-fixing breaks the app.

Full per-lint remediation + triage (0011/0013/0014/0025/0028/0029 + the Lovable agentic findings): references/scanner-findings.md.

Quick Audit Commands

1. Generate Full Access Matrix

Run the audit script to generate Docs/context/security-matrix.md:

python .claude/skills/security-auditor/scripts/audit_security.py

2. Quick SQL Checks

Tables with RLS disabled:

SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname IN ('public', 'bible_schema', 'admin', 'notifications', 'feedback')
  AND tablename NOT IN (
    SELECT tablename FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE c.relrowsecurity = true
      AND n.nspname IN ('public', 'bible_schema', 'admin', 'notifications', 'feedback')
  );

GRANTs without matching RLS policies:

SELECT DISTINCT tp.table_schema, tp.table_name, tp.privilege_type
FROM information_schema.table_privileges tp
WHERE tp.grantee = 'authenticated'
  AND tp.privilege_type IN ('UPDATE', 'DELETE', 'INSERT')
  AND tp.table_schema IN ('public', 'bible_schema')
  AND NOT EXISTS (
    SELECT 1 FROM pg_policies pp
    WHERE pp.schemaname = tp.table_schema
      AND pp.tablename = tp.table_name
      AND (pp.cmd = tp.privilege_type OR pp.cmd = 'ALL')
  );

Risky FOR ALL policies (should be explicit per-operation):

SELECT schemaname, tablename, policyname, roles::text,
  CASE WHEN with_check IS NULL THEN 'MISSING WITH CHECK!' ELSE 'OK' END as with_check_status
FROM pg_policies
WHERE cmd = 'ALL'
  AND schemaname IN ('public', 'bible_schema', 'admin', 'notifications', 'feedback');

Policies using TO public (should be role-specific):

SELECT schemaname, tablename, policyname, cmd
FROM pg_policies
WHERE roles::text = '{public}'
  AND schemaname IN ('public', 'bible_schema');

UPDATE/INSERT policies missing WITH CHECK:

SELECT schemaname, tablename, policyname, cmd
FROM pg_policies
WHERE cmd IN ('UPDATE', 'INSERT')
  AND with_check IS NULL
  AND schemaname IN ('public', 'bible_schema');

Check specific table security:

SELECT
  'RLS Status' as check_type,
  CASE WHEN c.relrowsecurity THEN 'Enabled' ELSE 'DISABLED!' END as status
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relname = 'your_table' AND n.nspname = 'public'

UNION ALL

SELECT 'Policy: ' || policyname, cmd || ' for ' || roles::text
FROM pg_policies
WHERE tablename = 'your_table';

Access Matrix Format

The audit generates markdown tables like:

Table	anon	authenticated	Admin Required
verses	R	R	-
profiles	R	RU (own)	-
ai_features	R	CRUD	Yes (write)

Legend: R=Read, C=Create, U=Update, D=Delete, (own)=user's own rows only

Common Security Patterns

Pattern 1: Public Read, Admin Write

GRANT SELECT ON table TO anon, authenticated;
GRANT INSERT, UPDATE, DELETE ON table TO authenticated;

CREATE POLICY "anon_read" ON table FOR SELECT TO anon USING (true);
CREATE POLICY "auth_read" ON table FOR SELECT TO authenticated USING (true);
-- Explicit per-operation policies (avoid FOR ALL):
CREATE POLICY "admin_insert" ON table FOR INSERT TO authenticated
  WITH CHECK (schema.is_admin());
CREATE POLICY "admin_update" ON table FOR UPDATE TO authenticated
  USING (schema.is_admin()) WITH CHECK (schema.is_admin());
CREATE POLICY "admin_delete" ON table FOR DELETE TO authenticated
  USING (schema.is_admin());

Pattern 2: User-Owned Data

GRANT SELECT, INSERT, UPDATE, DELETE ON table TO authenticated;

CREATE POLICY "user_crud" ON table FOR ALL TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

Pattern 3: Service Role Only (Edge Functions)

-- No GRANTs to anon/authenticated
-- Access only via SECURITY DEFINER functions or service_role

Security Checklist

For Each Table:

• RLS enabled (ALTER TABLE ... ENABLE ROW LEVEL SECURITY)
• GRANTs match intended access (no excess privileges)
• RLS policies exist for each granted operation
• UPDATE policies have both USING and WITH CHECK
• DELETE policies have USING clause
• Admin operations check has_role(auth.uid(), 'admin') or schema-specific is_admin()
• Indexes exist on RLS-referenced columns (user_id, created_by, status)

Red Flags:

• GRANT UPDATE without UPDATE RLS policy
• GRANT DELETE without DELETE RLS policy
• RLS policies with USING (true) for write operations
• TO public grants (allows unauthenticated access)
• FOR ALL policies - Avoid these; use explicit per-operation policies instead
• Policies targeting public role - Use explicit TO anon or TO authenticated
• Missing WITH CHECK on UPDATE/INSERT policies
• SECURITY DEFINER without SET search_path - hardened default is SET search_path = '' (every reference fully schema-qualified); prefer SECURITY INVOKER (the default) when definer rights aren't needed
• Privileged SECURITY DEFINER function executable by anon/authenticated with no internal authz - the function must self-guard (has_role, auth.uid()) or be revoked; see scanner-findings reference

Fixing Security Gaps

Missing RLS Policy for GRANT

-- Option 1: Add restrictive policy
CREATE POLICY "admin_update" ON schema.table
FOR UPDATE TO authenticated
USING (public.has_role(auth.uid(), 'admin'))
WITH CHECK (public.has_role(auth.uid(), 'admin'));

-- Option 2: Revoke the grant if not needed
REVOKE UPDATE ON schema.table FROM authenticated;

Missing USING/WITH CHECK

UPDATE policies need both:

CREATE POLICY "user_update" ON table
FOR UPDATE TO authenticated
USING (user_id = auth.uid())      -- Which rows can be read for update
WITH CHECK (user_id = auth.uid()); -- What the updated row must satisfy

Role Model & Permission Testing

The project has two role axes: plan/entitlement (PlanKey: unauth→basic→pro→premium→admin, app-side) and DB role (app_role: user→moderator→admin, public.user_roles via has_role). A planned model collapses these into one ladder, deprecates moderator, and moves "Vieraskynä" to a bible_schema.guest_authors table. Canonical spec (read before role work):

• ~/dev/obsidian/opeejii/pages/hankkeet/Raamattu Nyt/dev/Architecture/Raamattu Nyt Roolit.md (current)
• ~/dev/obsidian/opeejii/.../Raamattu Nyt Roolimalli (suunnitelma).md (planned)

Critical: RLS distinguishes only anon / authenticated / admin — free/pro/premium are the same Postgres role, so the ladder is tested at the app layer (quota/useAIQuota), not the DB. Don't make a "pro session" for DB tests.

For the permissions matrix + how to obtain a session per role (pgTAP request.jwt.claims vs signInWithPassword, the service_role-in-app CI ban, provisioning, and the isolation/negative tests that actually catch leaks), see: references/role-permission-tests.md

RLS Policy Patterns Reference

For detailed policy templates, testing procedures, and SECURITY DEFINER patterns, see: references/rls-patterns.md

Contents:

• User-owned data (full CRUD example)
• Public read, admin write
• Admin-only tables (audit logs)
• Role-based access
• has_role() function
• SECURITY DEFINER functions
• Testing RLS policies
• Common vulnerabilities table
• Validation checklists

Related Skills

Situation	Delegate To
Database migrations	supabase-migration-writer
Edge Function security	edge-function-generator