메뉴
Expert assistant for validating and creating Row Level Security (RLS) policies in the KR92 Bible Voice Supabase database. Use when creating RLS policies, validating user data protection, checking admin access, or identifying security gaps.
기본적으로 소스를 먼저 확인하는 Prompt가 선택됩니다. 직접 명령으로 전환하거나 로컬 사본을 다운로드할 수도 있습니다.
설치 여부를 결정하기 전에 SKILL.md와 SkillsMP에 표시된 보조 파일을 읽어 보세요.
Codex 또는 Claude로 설치 이 Prompt를 복사해 Codex, Claude 또는 다른 어시스턴트에 붙여 넣으면 Skill 페이지를 검토하고 설치를 진행할 수 있습니다.
직접 명령은 검토 Prompt를 거치지 않습니다. 실행하기 전에 소스를 확인하세요.
npx skills add https://github.com/Spectaculous-Code/raamattu-nyt --skill rls-policy-validator명령은 한 줄로 유지됩니다. 복사하기 전에 가로로 스크롤해 전체 내용을 확인하세요.
로컬 사본을 원하시나요? SkillsMP에서 현재 제공할 수 있는 파일을 다운로드하세요.
SOC 직업 분류 기준
SKILL.md 표시 중
| name | rls-policy-validator |
| description | Expert assistant for validating and creating Row Level Security (RLS) policies in the KR92 Bible Voice Supabase database. Use when creating RLS policies, validating user data protection, checking admin access, or identifying security gaps. |
For tables and security patterns, read from Docs/context/:
Docs/context/db-schema-short.md - Table structuresDocs/context/supabase-map.md - Access matrixCross-cutting learnings: See .claude/LEARNINGS.md → "Supabase/Database" section for RLS+GRANT two-layer patterns, common 403 error causes, and GRANT audit checklist.
All user-specific data MUST be protected by RLS policies that check auth.uid().
Bible text, lexicons, and reference data should be publicly readable.
Admin operations require role checks via has_role() function.
Privileged operations use SECURITY DEFINER functions with role checks.
User: "I created a new table user_notes for Bible study notes. Add RLS policies."
Assistant:
public.user_notes ENABLE LEVEL SECURITY;
POLICY "Users can view own notes"
public.user_notes
authenticated
(user_id auth.uid());
POLICY "Users can insert own notes"
public.user_notes
authenticated
(user_id auth.uid());
POLICY "Users can update own notes"
public.user_notes
authenticated
(user_id auth.uid())
(user_id auth.uid());
POLICY "Users can delete own notes"
public.user_notes
authenticated
(user_id auth.uid());
POLICY "Admins can view all notes"
public.user_notes
authenticated
(public.has_role(auth.uid(), ));
User: "Check if my bookmarks table is secure"
Assistant: Let me review the RLS policies:
-- Check if RLS is enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND tablename = 'bookmarks';
-- List existing policies
SELECT * FROM pg_policies
WHERE schemaname = 'public' AND tablename = 'bookmarks';
Validation checklist:
auth.uid()?user_id = auth.uid()?User: "Create RLS for admin_audit_logs table"
Assistant:
-- Enable RLS
ALTER TABLE public.admin_audit_logs ENABLE ROW LEVEL SECURITY;
-- Policy: Only admins can read audit logs
CREATE POLICY "Admins can view audit logs"
ON public.admin_audit_logs
FOR SELECT
TO authenticated
USING (public.has_role(auth.uid(), 'admin'));
-- Policy: System can insert (via SECURITY DEFINER function)
CREATE POLICY "System can insert audit logs"
ON public.admin_audit_logs
FOR INSERT
TO authenticated
WITH CHECK (true); -- Controlled by SECURITY DEFINER function
-- Note: No UPDATE or DELETE policies - audit logs are immutable
User: "Set up RLS for topics table - everyone can read, only admins can write"
Assistant:
-- Enable RLS
ALTER TABLE bible_schema.topical_topics ENABLE ROW LEVEL SECURITY;
-- Policy: Public can read topics
CREATE POLICY "Public can read topics"
ON bible_schema.topical_topics
FOR SELECT
TO anon, authenticated
USING (true);
-- Policy: Only admins can insert topics
CREATE POLICY "Admins can insert topics"
ON bible_schema.topical_topics
FOR INSERT
TO authenticated
WITH CHECK (public.has_role(auth.uid(), 'admin'));
-- Policy: Only admins can update topics
CREATE POLICY "Admins can update topics"
ON bible_schema.topical_topics
FOR UPDATE
TO authenticated
USING (public.has_role(auth.uid(), 'admin'))
WITH CHECK (public.has_role(auth.uid(), 'admin'));
-- Policy: Only admins can delete topics
CREATE POLICY "Admins can delete topics"
ON bible_schema.topical_topics
FOR DELETE
TO authenticated
USING (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "user_owned_data"
ON table_name
FOR ALL
TO authenticated
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());
CREATE POLICY "public_read"
ON table_name
FOR SELECT
USING (true);
CREATE POLICY "user_write"
ON table_name
FOR INSERT, UPDATE, DELETE
TO authenticated
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());
CREATE POLICY "admin_full_access"
ON table_name
FOR ALL
TO authenticated
USING (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "moderator_access"
ON table_name
FOR SELECT, UPDATE
TO authenticated
USING (
public.has_role(auth.uid(), 'admin') OR
public.has_role(auth.uid(), 'moderator')
);
The project uses a helper function for role checks:
CREATE FUNCTION public.has_role(_user_id uuid, _role app_role)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
SELECT EXISTS (
SELECT 1 FROM user_roles
WHERE user_id = _user_id AND role = _role
)
$$;
auth.uid()user_id = auth.uid()USING (true))has_role(auth.uid(), 'admin')-- DANGEROUS: No RLS protection
CREATE TABLE user_data (...);
-- Anyone can query via service key or bypass
-- DANGEROUS: No user isolation
CREATE POLICY "bad_policy"
ON user_data FOR SELECT
USING (true); -- All users see all data!
// DANGEROUS: Never use service key in client
const supabase = createClient(url, SERVICE_ROLE_KEY); // ❌
-- DANGEROUS: Anyone can become admin
INSERT INTO user_roles (user_id, role)
VALUES (auth.uid(), 'admin'); -- Should be SECURITY DEFINER function
-- Set role to authenticated user
SET ROLE authenticated;
SET request.jwt.claims.sub TO 'user-uuid-here';
-- Try to access data
SELECT * FROM user_notes; -- Should only see own notes
-- Reset
RESET ROLE;
SET ROLE anon;
SELECT * FROM verses; -- Should work for public data
SELECT * FROM user_notes; -- Should return empty/error
RESET ROLE;
-- Verify has_role works
SELECT public.has_role('admin-user-uuid', 'admin'); -- Should return true
SELECT public.has_role('regular-user-uuid', 'admin'); -- Should return false
For privileged operations, use SECURITY DEFINER:
CREATE FUNCTION delete_user_by_admin(target_user_id uuid)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
BEGIN
-- Verify admin role
IF NOT public.has_role(auth.uid(), 'admin') THEN
RAISE EXCEPTION 'Only admins can delete users';
END IF;
-- Prevent self-deletion
IF target_user_id = auth.uid() THEN
RAISE EXCEPTION 'Cannot delete own account';
END IF;
-- Perform deletion with elevated privileges
DELETE FROM auth.users WHERE id = target_user_id;
RETURN true;
END;
$$;
Docs/02-DESIGN.md for security architectureDocs/03-API.md for table structuresDocs/07-ADMIN-GUIDE.md for admin role managementKeeps documentation synchronized with code: API/architecture docs, README, CLAUDE.md, the AI index manifest (Docs/ai-index/manifest.yml — domains, task_playbooks, generated catalogues), ASCII flow diagrams, feature/system explanations, and reporting changes to the Obsidian vault. Use when (1) updating or generating docs, (2) updating the AI index / manifest ("ai index", "päivitä manifesti", "add domain/task playbook", "regenerate catalog"), (3) drawing ASCII flow/workflow diagrams ("piirrä kaavio"), (4) explaining how something works ("miten toimii", "selitä arkkitehtuuri"), or (5) reporting to Obsidian ("vault", "raportoi obsidianiin").
Manage biblical topics in Raamattu Nyt. Use for creating/editing topics + Finnish translations, topic relations (related/opposite/broader/narrower), validating FI translations & pronunciations (Voikko/Omorfi), reviewing qa_status='unchecked', bulk translation updates, aliases/synonyms, fixing mistranslated FI topic names, and the taxonomy tree (parent_id hierarchy, roots, orphans, cycles, re-parenting after merges). ALSO the structured topic schema (`structured_schema`): type templates, claims + sources, the async generation queue (/ohjaamo/topics/generation), draft review, and the Matthew Henry claim-verification pass — plus the PLANNED verse schema generated from Matthew Henry (design lives in Obsidian, not yet in code). ALSO the source corpora behind topic content — which reference work feeds which column, where the local files live (`Docs/data/`), and how to expand ISBE coverage without re-scraping. Triggers: aihe, topic, teema, taksonomia, hierarkia, alias, merge/yhdistäminen, aiheskeema, jae-skeema, str
Analyse a Bible verse's topic areas and link verses to topics in Raamattu Nyt (bible_schema.topical_references — the jae↔aihe link table). Use for: (1) tagging a single verse/passage — given a reference, find/assign its topics and insert topical_references; (2) per-book/chapter COVERAGE scans for under-tagged verses; (3) junk/QA CLEANUP of bad references (over-wide range refs, cluster bleed, wrong scope); (4) SOURCE-based rematch from easton_articles / smiths_articles to topical_references. Triggers: "tägää jae", "liitä jae aiheeseen", "jakeen aihealueet", "analysoi jakeen aiheet", "aihekattavuus", "topical_references", "roskaviite", "junk ref", "cluster bleed", "coverage gap", "Easton/Smith rematch", "verse topic tagging". Also owns (5) the VERSE SCHEMA (jae-skeema) — Matthew Henry turned into a structured Finnish exposition per outline point, and the verification of its claims against the bounded English source; triggers: "jae-skeema", "jakeiden skeema", "Matthew Henry", "rakenteinen suomennos", "structured