Janady13/missionmatch / internal/integration/rls_integration_test.go

1//go:build integration 2 3// Package integration runs the data layer against a REAL Postgres 16 + pgvector. It proves at 4// runtime the properties the adversarial review could only check statically: tenant isolation, 5// default deny, the composite FK cross tenant injection block, numeric(7,4) round trip, audit 6// immutability, the EEO privilege boundary, and the hardened pgvector/RRF search path. 7// 8// Run: go test -tags=integration ./internal/integration/ -v 9// Env: MM_APP_DSN, MM_MIGRATOR_DSN (required), MM_COMPLIANCE_DSN (optional). 10package integration 11 12import ( 13 "context" 14 "errors" 15 "fmt" 16 "os" 17 "testing" 18 "time" 19 20 "github.com/google/uuid" 21 "github.com/jackc/pgx/v5" 22 "github.com/jackc/pgx/v5/pgconn" 23 "github.com/jackc/pgx/v5/pgxpool" 24 "github.com/pgvector/pgvector-go" 25 "github.com/shopspring/decimal" 26 27 "github.com/missionworkforce/missionmatch/internal/modules/commandcenter" 28 "github.com/missionworkforce/missionmatch/internal/modules/matching" 29 "github.com/missionworkforce/missionmatch/internal/platform/database" 30 "github.com/missionworkforce/missionmatch/internal/platform/database/dbgen" 31 "github.com/missionworkforce/missionmatch/internal/platform/embeddings" 32 "github.com/missionworkforce/missionmatch/internal/platform/tenant" 33) 34 35var ( 36 appDB *tenant.DB 37 migrator *pgxpool.Pool 38 compliance *pgxpool.Pool 39) 40 41func TestMain(m *testing.M) { 42 ctx := context.Background() 43 appDSN, migDSN, compDSN := os.Getenv("MM_APP_DSN"), os.Getenv("MM_MIGRATOR_DSN"), os.Getenv("MM_COMPLIANCE_DSN") 44 if appDSN == "" || migDSN == "" { 45 fmt.Println("integration: MM_APP_DSN / MM_MIGRATOR_DSN not set, skipping integration tests") 46 os.Exit(0) 47 } 48 pool, err := database.NewPool(ctx, appDSN) 49 if err != nil { 50 panic("app pool: " + err.Error()) 51 } 52 appDB = tenant.New(pool) 53 if migrator, err = database.NewPool(ctx, migDSN); err != nil { 54 panic("migrator pool: " + err.Error()) 55 } 56 if compDSN != "" { 57 if compliance, err = database.NewPool(ctx, compDSN); err != nil { 58 panic("compliance pool: " + err.Error()) 59 } 60 } 61 os.Exit(m.Run()) 62} 63 64// ---------- helpers (seed via the BYPASSRLS migrator so we can write across tenants) ---------- 65 66func seedOrg(t *testing.T) uuid.UUID { 67 t.Helper() 68 id := uuid.New() 69 slug := "org-" + id.String()[:12] 70 if _, err := migrator.Exec(context.Background(), 71 "INSERT INTO organizations (id, name, slug) VALUES ($1,$2,$3)", id, slug, slug); err != nil { 72 t.Fatalf("seed org: %v", err) 73 } 74 return id 75} 76 77func seedCandidate(t *testing.T, org uuid.UUID, level, status string, available bool) dbgen.Candidate { 78 t.Helper() 79 c, err := dbgen.New(migrator).CreateCandidate(context.Background(), dbgen.CreateCandidateParams{ 80 OrganizationID: org, FullName: "Cand " + level, ClearanceLevel: level, ClearancePoly: "none", 81 ClearanceStatus: status, Available: available, 82 }) 83 if err != nil { 84 t.Fatalf("seed candidate: %v", err) 85 } 86 return c 87} 88 89func ctxOrg(org uuid.UUID) context.Context { return tenant.WithOrg(context.Background(), org.String()) } 90 91func pgCode(err error) string { 92 var pgErr *pgconn.PgError 93 if errors.As(err, &pgErr) { 94 return pgErr.Code 95 } 96 return "" 97} 98 99// ---------- tests ---------- 100 101func TestCrossTenantReadIsolation(t *testing.T) { 102 orgA, orgB := seedOrg(t), seedOrg(t) 103 candA := seedCandidate(t, orgA, "secret", "active", true) 104 candB := seedCandidate(t, orgB, "secret", "active", true) 105 106 var listed []dbgen.Candidate 107 if err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error { 108 var e error 109 listed, e = dbgen.New(tx).ListCandidates(ctx, dbgen.ListCandidatesParams{Limit: 1000}) 110 return e 111 }); err != nil { 112 t.Fatalf("list: %v", err) 113 } 114 sawA, sawB := false, false 115 for _, c := range listed { 116 sawA = sawA || c.ID == candA.ID 117 sawB = sawB || c.ID == candB.ID 118 } 119 if !sawA { 120 t.Fatal("org A must see its own candidate") 121 } 122 if sawB { 123 t.Fatal("ISOLATION BREACH: org A saw org B's candidate") 124 } 125 126 // Direct read of B's candidate under org A's session must return zero rows (RLS hides it). 127 err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error { 128 _, e := dbgen.New(tx).GetCandidate(ctx, candB.ID) 129 return e 130 }) 131 if !errors.Is(err, pgx.ErrNoRows) { 132 t.Fatalf("cross tenant GetCandidate should be ErrNoRows, got %v", err) 133 } 134} 135 136func TestUnsetGUCDefaultDeny(t *testing.T) { 137 orgA := seedOrg(t) 138 seedCandidate(t, orgA, "secret", "active", true) 139 140 // App role, NO tenant transaction => GUC unset => current_setting is NULL => policy denies => 0 rows. 141 var n int 142 if err := appDB.Pool.QueryRow(context.Background(), "SELECT count(*) FROM candidates").Scan(&n); err != nil { 143 t.Fatalf("count: %v", err) 144 } 145 if n != 0 { 146 t.Fatalf("default deny breached: unscoped app query saw %d rows (want 0)", n) 147 } 148} 149 150func TestCompositeFKBlocksCrossTenantInjection(t *testing.T) { 151 orgA, orgB := seedOrg(t), seedOrg(t) 152 candB := seedCandidate(t, orgB, "secret", "active", true) // victim parent in org B 153 skill, err := dbgen.New(migrator).UpsertSkill(context.Background(), dbgen.UpsertSkillParams{ 154 Name: "FKProbe-" + uuid.NewString(), 155 }) 156 if err != nil { 157 t.Fatalf("seed skill: %v", err) 158 } 159 160 // As org A, try to attach a skill row (org=A) to org B's candidate. RLS WITH CHECK passes (row 161 // org == session org), but the COMPOSITE FK (organization_id, candidate_id) -> candidates has no 162 // matching (A, candB) parent, so the write MUST fail with a foreign key violation (23503). 163 err = appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error { 164 return dbgen.New(tx).AddCandidateSkill(ctx, dbgen.AddCandidateSkillParams{ 165 OrganizationID: orgA, CandidateID: candB.ID, SkillID: skill.ID, 166 }) 167 }) 168 if code := pgCode(err); code != "23503" { 169 t.Fatalf("cross tenant injection NOT blocked: want FK violation 23503, got err=%v code=%q", err, code) 170 } 171} 172 173func TestPerfectScorePersistsViaService(t *testing.T) { 174 orgA := seedOrg(t) 175 // Requirement with nothing required => every component scores 100 => total exactly 100. 176 req, err := dbgen.New(migrator).CreateRequirement(context.Background(), dbgen.CreateRequirementParams{ 177 OrganizationID: orgA, Title: "Open Role", RequiredLevel: "none", RequiredPoly: "none", 178 }) 179 if err != nil { 180 t.Fatalf("seed requirement: %v", err) 181 } 182 cand := seedCandidate(t, orgA, "secret", "active", true) 183 184 svc := matching.NewService(appDB, nil, nil) // exact-only matcher 185 out, err := svc.GenerateSlate(ctxOrg(orgA), matching.GenerateSlateInput{RequirementID: req.ID, Name: "Perfect Slate"}) 186 if err != nil { 187 t.Fatalf("GenerateSlate (numeric overflow would surface here): %v", err) 188 } 189 if len(out.Members) != 1 || out.Members[0].CandidateID != cand.ID.String() { 190 t.Fatalf("unexpected members: %+v", out.Members) 191 } 192 if out.Members[0].Total != "100.00" || out.Members[0].Band != "green" { 193 t.Fatalf("perfect candidate should be 100.00/green, got %s/%s", out.Members[0].Total, out.Members[0].Band) 194 } 195 196 // Read back the frozen decision record, proves numeric(7,4) stored 100.0000 (the SQLC-1 fix). 197 var members []dbgen.SlateMember 198 if err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error { 199 var e error 200 members, e = dbgen.New(tx).ListSlateMembers(ctx, out.SlateID) 201 return e 202 }); err != nil { 203 t.Fatalf("read back: %v", err) 204 } 205 if len(members) != 1 || !members[0].TotalScore.Equal(decimal.NewFromInt(100)) { 206 t.Fatalf("persisted total should be 100, got %+v", members) 207 } 208} 209 210func TestAuditAppendOnly(t *testing.T) { 211 orgA := seedOrg(t) 212 if err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error { 213 _, e := dbgen.New(tx).InsertAuditLog(ctx, dbgen.InsertAuditLogParams{OrganizationID: &orgA, Action: "test.event"}) 214 return e 215 }); err != nil { 216 t.Fatalf("insert audit: %v", err) 217 } 218 219 // App role has no UPDATE/DELETE grant on audit_logs => permission denied (42501). 220 if _, err := appDB.Pool.Exec(context.Background(), "UPDATE audit_logs SET action='tamper' WHERE organization_id=$1", orgA); pgCode(err) != "42501" { 221 t.Fatalf("app UPDATE on audit_logs should be permission-denied (42501), got %v", err) 222 } 223 // Even the owner (migrator) is stopped by the append-only trigger (raise_exception P0001). 224 _, err := migrator.Exec(context.Background(), "UPDATE audit_logs SET action='tamper' WHERE organization_id=$1", orgA) 225 if err == nil { 226 t.Fatal("append-only trigger failed: owner UPDATE on audit_logs succeeded") 227 } 228 if pgCode(err) != "P0001" { 229 t.Fatalf("owner UPDATE should raise the append-only trigger (P0001), got %v", err) 230 } 231} 232 233func TestEEOPrivilegeBoundary(t *testing.T) { 234 // The matching app role must have ZERO access to the eeo schema (permission denied 42501). 235 _, err := appDB.Pool.Exec(context.Background(), "SELECT 1 FROM eeo.candidate_demographics LIMIT 1") 236 if pgCode(err) != "42501" { 237 t.Fatalf("app role must be denied EEO access (42501), got %v", err) 238 } 239 if compliance != nil { 240 // The compliance role CAN reach it (no permission error; RLS may legitimately return 0 rows). 241 if _, err := compliance.Exec(context.Background(), "SELECT 1 FROM eeo.candidate_demographics LIMIT 1"); err != nil { 242 t.Fatalf("compliance role should access EEO, got %v", err) 243 } 244 } 245} 246 247func TestHybridSearchPathExecutes(t *testing.T) { 248 orgA := seedOrg(t) 249 emb := embeddings.NewDeterministic(384) 250 names := []string{"Cloud Security Engineer", "Kubernetes Platform Lead", "Network Defense Analyst"} 251 for _, name := range names { 252 vec, _ := emb.Embed(context.Background(), name) 253 pv := pgvector.NewVector(vec) 254 hn := name 255 if _, err := dbgen.New(migrator).CreateCandidate(context.Background(), dbgen.CreateCandidateParams{ 256 OrganizationID: orgA, FullName: name, Headline: &hn, ClearanceLevel: "secret", 257 ClearancePoly: "none", ClearanceStatus: "active", Available: true, ResumeEmbedding: &pv, 258 }); err != nil { 259 t.Fatalf("seed search candidate: %v", err) 260 } 261 } 262 263 qv := pgvector.NewVector(mustEmbed(emb, "cloud security")) 264 var rows []dbgen.SearchCandidatesHybridRow 265 err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error { 266 if _, e := tx.Exec(ctx, "SET LOCAL hnsw.ef_search = 200"); e != nil { // PGV-3 267 return e 268 } 269 var e error 270 rows, e = dbgen.New(tx).SearchCandidatesHybrid(ctx, dbgen.SearchCandidatesHybridParams{ 271 Column1: qv, Limit: 10, WebsearchToTsquery: "cloud security", 272 }) 273 return e 274 }) 275 if err != nil { 276 t.Fatalf("hybrid search executed against live pgvector failed: %v", err) 277 } 278 if len(rows) == 0 { 279 t.Fatal("hybrid search returned no rows (expected the seeded candidates)") 280 } 281 for _, r := range rows { 282 if r.OrganizationID != orgA { 283 t.Fatalf("hybrid search leaked a foreign-tenant row: %s", r.OrganizationID) 284 } 285 } 286} 287 288func mustEmbed(e embeddings.Embedder, s string) []float32 { 289 v, _ := e.Embed(context.Background(), s) 290 return v 291} 292 293func ptrStr(s string) *string { return &s } 294 295// TestCommandCenterRLSIsolation proves the Workforce Command Center never mixes tenants: org A's 296// dashboard counts only org A's workforce/opportunities, even though org B has its own seeded data on 297// the same cluster. This is the design's #1 risk (RLS boundary escape on an analytics endpoint). 298func TestCommandCenterRLSIsolation(t *testing.T) { 299 ctx := context.Background() 300 orgA, orgB := seedOrg(t), seedOrg(t) 301 302 // org A: exactly 3 cleared, available candidates; NO opportunities. 303 for i := 0; i < 3; i++ { 304 seedCandidate(t, orgA, "secret", "active", true) 305 } 306 // org B: 5 cleared candidates + a uniquely-named opportunity with one requirement. 307 for i := 0; i < 5; i++ { 308 seedCandidate(t, orgB, "top_secret", "active", true) 309 } 310 decoyOpp := uuid.New() 311 if _, err := migrator.Exec(ctx, 312 `INSERT INTO opportunities (id, organization_id, name, status, value) VALUES ($1,$2,'DECOY-ONLY-OPP','open',1000000)`, 313 decoyOpp, orgB); err != nil { 314 t.Fatalf("seed decoy opp: %v", err) 315 } 316 if _, err := migrator.Exec(ctx, 317 `INSERT INTO requirements (organization_id, opportunity_id, title, labor_category, quantity, required_level, required_poly) 318 VALUES ($1,$2,'Decoy Engineer','Decoy Engineer',2,'secret','none')`, 319 orgB, decoyOpp); err != nil { 320 t.Fatalf("seed decoy req: %v", err) 321 } 322 323 svc := commandcenter.NewService(appDB) 324 now := time.Now() 325 326 va, err := svc.CommandCenter(ctxOrg(orgA), now) 327 if err != nil { 328 t.Fatalf("command center org A: %v", err) 329 } 330 if va.Banner.TotalClearedWorkforce != 3 { 331 t.Fatalf("org A cleared workforce = %d, want 3 (leak of org B's 5?)", va.Banner.TotalClearedWorkforce) 332 } 333 if va.Banner.ActiveRequirements != 0 || len(va.ActiveOpportunities) != 0 { 334 t.Fatalf("org A saw foreign opportunities/requirements: reqs=%d opps=%d", va.Banner.ActiveRequirements, len(va.ActiveOpportunities)) 335 } 336 for _, o := range va.ActiveOpportunities { 337 if o.Name == "DECOY-ONLY-OPP" { 338 t.Fatal("ISOLATION BREACH: org A saw org B's DECOY-ONLY-OPP") 339 } 340 } 341 342 vb, err := svc.CommandCenter(ctxOrg(orgB), now) 343 if err != nil { 344 t.Fatalf("command center org B: %v", err) 345 } 346 if vb.Banner.TotalClearedWorkforce != 5 { 347 t.Fatalf("org B cleared workforce = %d, want 5", vb.Banner.TotalClearedWorkforce) 348 } 349 if vb.Banner.ActiveRequirements != 1 || len(vb.ActiveOpportunities) != 1 || vb.ActiveOpportunities[0].Name != "DECOY-ONLY-OPP" { 350 t.Fatalf("org B should see exactly its own opportunity, got reqs=%d opps=%d", vb.Banner.ActiveRequirements, len(vb.ActiveOpportunities)) 351 } 352} 353 354func TestScreenIntelligenceAndOpportunityFit(t *testing.T) { 355 ctx := context.Background() 356 orgA := seedOrg(t) 357 358 req, err := dbgen.New(migrator).CreateRequirement(ctx, dbgen.CreateRequirementParams{ 359 OrganizationID: orgA, Title: "Cyber Engineer", CustomerAgency: ptrStr("DISA"), 360 RequiredLevel: "secret", RequiredPoly: "none", RequiredYears: 5, KeyPersonnel: true, 361 }) 362 if err != nil { 363 t.Fatalf("seed requirement: %v", err) 364 } 365 366 cand := seedCandidate(t, orgA, "ts_sci", "active", true) 367 if _, err := migrator.Exec(ctx, 368 "UPDATE candidates SET clearance_poly='full_scope', years_experience=10, clearance_granted_at='2023-01-15', clearance_expires_at='2029-01-15' WHERE id=$1", 369 cand.ID); err != nil { 370 t.Fatalf("enrich candidate: %v", err) 371 } 372 if err := dbgen.New(migrator).AddCandidateCustomerExperience(ctx, dbgen.AddCandidateCustomerExperienceParams{ 373 OrganizationID: orgA, CandidateID: cand.ID, Agency: "DISA", 374 }); err != nil { 375 t.Fatalf("add customer experience: %v", err) 376 } 377 378 svc := matching.NewService(appDB, nil, nil) 379 380 view, err := svc.Screen(ctxOrg(orgA), cand.ID, req.ID) 381 if err != nil { 382 t.Fatalf("Screen: %v", err) 383 } 384 if view.MissionReadiness.Status != "Mission Ready" || view.MissionReadiness.Score != "100.00" { 385 t.Fatalf("mission readiness = %+v", view.MissionReadiness) 386 } 387 if len(view.MissionReadiness.Categories) != 6 { 388 t.Fatalf("expected 6 readiness categories, got %d", len(view.MissionReadiness.Categories)) 389 } 390 if !view.ClearanceIntel.SCIEligible || !view.ClearanceIntel.FullScopePoly || !view.ClearanceIntel.RecentClassifiedSupport { 391 t.Fatalf("clearance intel = %+v", view.ClearanceIntel) 392 } 393 if view.ClearanceIntel.InvestigationAgeMonths == nil || *view.ClearanceIntel.InvestigationAgeMonths < 30 { 394 t.Fatalf("expected an investigation age ~41mo, got %v", view.ClearanceIntel.InvestigationAgeMonths) 395 } 396 if view.WinImpact.LevelCode != "high" { 397 t.Fatalf("win impact = %+v", view.WinImpact) 398 } 399 keyPersonnel := false 400 for _, rsn := range view.WinImpact.Reasons { 401 if rsn == "Matches Key Personnel requirements" { 402 keyPersonnel = true 403 } 404 } 405 if !keyPersonnel { 406 t.Fatalf("expected Key Personnel reason, got %v", view.WinImpact.Reasons) 407 } 408 409 fits, err := svc.OpportunitiesForCandidate(ctxOrg(orgA), cand.ID, 10) 410 if err != nil { 411 t.Fatalf("OpportunitiesForCandidate: %v", err) 412 } 413 if len(fits) == 0 || fits[0].RequirementID != req.ID || fits[0].Status != "Mission Ready" { 414 t.Fatalf("opportunity fit = %+v", fits) 415 } 416} 417