security-auditor

name

security-auditor

description

Comprehensive Supabase security auditor for RLS policies, table privileges (GRANTs), and access control validation. Use for: auditing DB security (RLS + GRANTs), generating an access matrix (who can SELECT/INSERT/UPDATE/DELETE which tables), finding gaps (missing RLS, over-permissive GRANTs), validating PostgREST access patterns, writing security docs for Docs/context/, creating RLS policies, validating user data protection, checking admin access, identifying vulnerabilities, and triaging security scanner output (Supabase database linter / advisors and the Lovable agentic security scanner — RLS-disabled, SECURITY DEFINER executable by anon/authenticated, mutable search_path, public bucket listing, extension in public, exposed sensitive columns). Also use for designing ROLE-PERMISSION TESTS: building the per-role permissions matrix (anon / authenticated / admin + the plan ladder free/pro/premium), and obtaining a session per role to run the tests (pgTAP `request.jwt.claims` or `signInWithPassword`). Knows the project role model (`PlanKey` + `app_role`, planned single ladder, `guest_authors`). Triggers: "security audit", "access matrix", "who can update", "missing RLS", "check grants", "security gaps", "table permissions", "RLS policy", "row level security", "validate security", "user data protection", "admin access", "supabase advisor", "database linter", "lovable security", "scanner findings", "detected issues", "security definer executable", "search_path", "get_advisors", "role tests", "role permissions", "test roles", "session per role", "permission matrix", "roolit", "roolimalli", "käyttöoikeudet", "käyttöoikeustestit", "app_role", "PlanKey", "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