Spaces:
Sleeping
Sleeping
File size: 3,050 Bytes
f532bd2 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | -- Migration 010: Vulnerability Hunter Challenges (Blue Team)
-- Creates the table for a new blue-team challenge type:
-- the player reads a vulnerable code snippet and must IDENTIFY the
-- vulnerability (name + class), not fix the code.
--
-- Difficulty uses 5 levels (Beginner / Easy / Medium / Hard / Expert) —
-- mapped to Arabic values already in use in the platform:
-- Beginner -> مبتدئ
-- Easy -> سهل
-- Medium -> متوسط
-- Hard -> صعب
-- Expert -> خبير
--
-- `vulnerability_type` is the canonical short key the player must match
-- (e.g. "sql-injection", "xss", "command-injection", "path-traversal"...).
-- `vulnerability_class` is the OWASP/MITRE family used in hints/UI
-- (e.g. "Injection", "Broken Access Control").
BEGIN;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS public.vulnerability_hunter_challenges (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
team_role text NOT NULL DEFAULT 'blue'
CHECK (team_role IN ('blue')),
language text NOT NULL
CHECK (language IN ('C++','JAVA','PYTHON','JAVASCRIPT','PHP','RUST','GO','CSHARP')),
module text NOT NULL,
title text NOT NULL,
story text NOT NULL,
task_outline text NOT NULL,
vulnerable_code text NOT NULL,
vulnerability_type text NOT NULL, -- canonical key the student types
vulnerability_class text NOT NULL, -- OWASP family for hints
vulnerability_description text NOT NULL,
hints jsonb DEFAULT '[]'::jsonb,
difficulty text NOT NULL
CHECK (difficulty IN ('مبتدئ','سهل','متوسط','صعب','خبير')),
xp_reward integer DEFAULT 150,
created_at timestamptz DEFAULT now()
);
-- Fast pool / dashboard queries
CREATE INDEX IF NOT EXISTS idx_vh_team_lang ON public.vulnerability_hunter_challenges(team_role, language);
CREATE INDEX IF NOT EXISTS idx_vh_difficulty ON public.vulnerability_hunter_challenges(difficulty);
CREATE INDEX IF NOT EXISTS idx_vh_vuln_type ON public.vulnerability_hunter_challenges(vulnerability_type);
-- RLS — open read for anon (same posture as the other pool tables)
ALTER TABLE public.vulnerability_hunter_challenges ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Open read vh" ON public.vulnerability_hunter_challenges;
CREATE POLICY "Open read vh" ON public.vulnerability_hunter_challenges
FOR SELECT USING (true);
DROP POLICY IF EXISTS "Service role insert vh" ON public.vulnerability_hunter_challenges;
CREATE POLICY "Service role insert vh" ON public.vulnerability_hunter_challenges
FOR INSERT WITH CHECK (true);
DROP POLICY IF EXISTS "Service role delete vh" ON public.vulnerability_hunter_challenges;
CREATE POLICY "Service role delete vh" ON public.vulnerability_hunter_challenges
FOR DELETE USING (true);
COMMIT;
|