IKF Security Review Gate Skill

Objective

Perform comprehensive security audit on IKF CENTRAL backend changes to ensure forecast data remains private and access-controlled. Acts as a mandatory gate before phase completion.

When to Use

•Post-execution validation: After any GSD phase involving backend changes
•Pre-merge: Before merging branches with database/API/auth changes
•Hotfix validation: After quick fixes to security-critical areas
•Periodic audits: Monthly review of entire security posture

Inputs

•Changed files: Via git diff since last phase or specific commit range
•Database schema: Current state of all tables
•API endpoints: All defined routes/functions
•Supabase configuration: Auth settings, RLS policies, storage rules
•Environment files: Check for exposed secrets

Security Checklist

1. Authentication Enforcement ✓

• All API endpoints require authentication (no anonymous reads)
• Supabase client initialized with anon key only (never service_role)
• No service_role key in frontend bundle or .env files committed to git
• Auth state properly validated before data access

2. Row Level Security (RLS) ✓

• RLS enabled on ALL tables containing forecast data
• RLS policies use auth.uid() or auth.jwt() for user filtering
• No policies using USING (true) without auth checks
• Policies tested with multiple user roles (admin, regular user, unauthenticated)
• New tables automatically have RLS enabled in migration

3. Storage Security ✓

• All forecast file buckets are private (not public)
• Storage policies require authentication
• Raw forecast files only accessible to admin roles
• No temporary public URLs generated without expiry

4. API Surface Minimization ✓

• Endpoints return ONLY required fields (no SELECT *)
• Response schemas documented and enforced
• No endpoints exposing raw forecast_runs table
• Aggregations done server-side (no client-side raw data filtering)
• Query parameters validated and sanitized

5. SQL Injection Prevention ✓

• All queries use parameterized statements
• No string concatenation in SQL queries
• Supabase client .select(), .insert(), .update() used correctly
• Raw SQL (if any) reviewed for injection vectors

6. Data Leakage Prevention ✓

• Error messages don't expose internal data structure
• Logging doesn't include forecast values or PII
• API errors return generic messages to client
• No forecast data in client-side error tracking (e.g., Sentry)

7. CORS & CSP ✓

• CORS properly configured (if applicable)
• Content Security Policy headers set
• No Access-Control-Allow-Origin: * in production

8. Secrets Management ✓

• .env in .gitignore
• .env.example provided without real secrets
• No hardcoded API keys, passwords, or tokens in code
• Supabase keys loaded from environment variables

9. Third-Party Dependencies ✓

• Dependencies reviewed for known vulnerabilities (npm audit)
• TradingView widgets don't receive forecast data
• External APIs called server-side, not from client

10. Access Control Logic ✓

• User roles properly defined (admin vs. regular user)
• Admin-only operations (file upload, ingest) protected
• No privilege escalation vectors
• Wishlists scoped to user or team correctly

Constraints (from CLAUDE.md)

•Privacy: Forecast data must never be publicly accessible
•Least-privilege: APIs return only what UI needs
•Auditability: Forecast runs are append-only/immutable
•Authentication-first: Every data access requires valid auth

Execution Workflow

Step 1: Identify Changed Files

bash

# If in GSD phase execution
git diff --name-only HEAD~5 HEAD

# Or specific range
git diff --name-only <start-commit> <end-commit>

Focus on:

•src/lib/supabase.ts
•src/hooks/*.ts (data fetching)
•supabase/migrations/*.sql
•.env or .env.example
•API route files (if using Edge Functions)

Step 2: Review Each Category

For each file changed, run through relevant checklist sections above.

Step 3: Test RLS Policies

sql

-- Test as unauthenticated user
SET ROLE anon;
SELECT * FROM forecasts; -- Should fail or return empty

-- Test as authenticated user
SET LOCAL request.jwt.claims.sub = 'user-uuid-here';
SELECT * FROM forecasts; -- Should return only user's data

-- Test as admin
SET LOCAL request.jwt.claims.role = 'admin';
SELECT * FROM forecasts; -- Should return all data (if admin has full access)

Step 4: Check Supabase Configuration

bash

# List all tables and RLS status
psql $DATABASE_URL -c "\dt+"

# Check for tables without RLS
psql $DATABASE_URL -c "
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'public'
  AND rowsecurity = false;
"

Step 5: Review API Responses

bash

# Test endpoint without auth
curl http://localhost:5173/api/forecasts

# Test with auth header
curl -H "Authorization: Bearer <anon-key>" http://localhost:5173/api/forecasts

Step 6: Scan for Secrets

bash

# Check for accidentally committed secrets
git log -p | grep -i 'service_role'
git log -p | grep -i 'password'

# Check current files
grep -r "service_role" src/
grep -r "eyJ.*" .env 2>/dev/null

Outputs

Pass Scenario

markdown

## Security Review: Phase N - PASSED ✅

### Summary
All security checks passed. No violations detected.

### Checked Areas
- Authentication: ✅ All endpoints protected
- RLS: ✅ Enabled on forecasts, forecast_runs, wishlists
- Storage: ✅ Buckets private, policies require auth
- API Surface: ✅ Minimal field exposure
- SQL Injection: ✅ Parameterized queries only
- Secrets: ✅ No leaks detected

### Notes
- forecast_runs table has append-only RLS policy (correct)
- Admin role properly gated for file uploads
- Wishlists scoped to auth.uid()

**APPROVED FOR PHASE COMPLETION**

Fail Scenario

markdown

## Security Review: Phase N - FAILED ❌

### Critical Issues Found

#### 🔴 CRITICAL: RLS Not Enabled
- **Table**: `forecast_runs`
- **Issue**: Row Level Security disabled
- **Risk**: Any authenticated user can read all forecasts
- **Fix**:
  ```sql
  ALTER TABLE forecast_runs ENABLE ROW LEVEL SECURITY;
  CREATE POLICY "Users read own org forecasts"
    ON forecast_runs FOR SELECT
    USING (auth.uid() IS NOT NULL);

🔴 CRITICAL: Service Role Key Exposed

•File: src/lib/supabase.ts:15
•Issue: Using SUPABASE_SERVICE_ROLE_KEY in client code
•Risk: Complete database access from browser
•Fix: Remove service_role, use anon key only

🟡 WARNING: Overly Broad SELECT

•File: src/hooks/useForecasts.ts:42
•Issue: SELECT * returns all columns including internal scores
•Risk: Unnecessary data exposure
•Fix: Specify fields: SELECT ticker, signal, pred, horizon

Blocking Phase Completion

Phase CANNOT proceed until critical issues resolved.

Remediation Plan

•Fix RLS on forecast_runs
•Remove service_role key from client
•Test with unauthenticated user
•Rerun security review gate

PHASE MUST BE FIXED BEFORE MERGE

code


## Integration with GSD

### In GSD Plan (XML format)
```xml
<task type="manual">
  <name>Security Review Gate</name>
  <files>All changed files in phase</files>
  <action>Run /ikf-security-review-gate skill</action>
  <verify>All checklist items pass</verify>
  <done>Security review returns PASSED status</done>
</task>

Auto-run Configuration

Add to .planning/config.json:

json

{
  "ikf": {
    "auto_run_skills": {
      "post_execution": ["ikf-security-review-gate"]
    }
  }
}

Definition of Done

•✅ All 10 checklist categories reviewed
•✅ Critical issues: 0
•✅ Warnings: documented and accepted or fixed
•✅ RLS policies tested with multiple roles
•✅ No secrets in git history
•✅ API responses validated for minimal exposure
•✅ Output report generated (pass/fail)

Testing Checklist

For each phase involving backend:

•Run skill immediately after /gsd:execute-phase N
•If failures, create fix tasks and rerun phase
•Only proceed to /gsd:verify-work N after security PASS
•Document any accepted warnings in STATE.md

References

•CLAUDE.md "Security requirements (Phase 0 north star)"
•Supabase RLS documentation: https://supabase.com/docs/guides/auth/row-level-security
•OWASP Top 10: https://owasp.org/www-project-top-ten/

Critical Principle: Security is not optional. This gate must pass before any backend phase completes.