Installer avec Codex ou Claude Copiez ce prompt, collez-le dans Codex, Claude ou un autre assistant, puis laissez-le vérifier la page du skill et l'installer pour vous.
Une commande directe contourne le prompt de vérification. Examinez la source avant de l'exécuter.
When the task is "go through these security scan findings" (Lovable "Detected
Issues", a pasted Supabase linter list, or a periodic check):
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).
Apply the project triage policy: fix ERROR-level now; document but
defer + all -scanner findings unless per-object inspection
shows a genuine leak. Lovable agentic findings (,
) are heuristic — verify before acting.
WARN
supabase
MISSING_RLS_PROTECTION
EXPOSED_SENSITIVE_DATA
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:
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname IN ('public', 'bible_schema', 'admin', 'notifications', 'feedback')
AND tablename NOTIN (
SELECT tablename FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relrowsecurity =trueAND n.nspname IN ('public', 'bible_schema', 'admin', 'notifications', 'feedback')
);
GRANTs without matching RLS policies:
SELECTDISTINCT 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')
ANDNOTEXISTS (
SELECT1FROM 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,
CASEWHEN with_check ISNULLTHEN'MISSING WITH CHECK!'ELSE'OK'ENDas 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 ISNULLAND schemaname IN ('public', 'bible_schema');
Check specific table security:
SELECT'RLS Status'as check_type,
CASEWHEN c.relrowsecurity THEN'Enabled'ELSE'DISABLED!'ENDas status
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relname ='your_table'AND n.nspname ='public'UNIONALLSELECT'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
GRANTSELECTONtableTO anon, authenticated;
GRANTINSERT, UPDATE, DELETEONtableTO authenticated;
CREATE POLICY "anon_read" ONtableFORSELECTTO anon USING (true);
CREATE POLICY "auth_read" ONtableFORSELECTTO authenticated USING (true);
-- Explicit per-operation policies (avoid FOR ALL):CREATE POLICY "admin_insert" ONtableFORINSERTTO authenticated
WITHCHECK (schema.is_admin());
CREATE POLICY "admin_update" ONtableFORUPDATETO authenticated
USING (schema.is_admin()) WITHCHECK (schema.is_admin());
CREATE POLICY "admin_delete" ONtableFORDELETETO authenticated
USING (schema.is_admin());
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 policyCREATE POLICY "admin_update" ON schema.table
FORUPDATETO authenticated
USING (public.has_role(auth.uid(), 'admin'))
WITHCHECK (public.has_role(auth.uid(), 'admin'));
-- Option 2: Revoke the grant if not neededREVOKEUPDATEON schema.table FROM authenticated;
Missing USING/WITH CHECK
UPDATE policies need both:
CREATE POLICY "user_update" ONtableFORUPDATETO authenticated
USING (user_id = auth.uid()) -- Which rows can be read for updateWITHCHECK (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
