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

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

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')
  );

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')
  );

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');

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

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

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';

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());

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());

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

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

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