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 288// TestHybridSearchMissionScopeExecutes proves the WS6 mission scoped query (SearchCandidatesHybridMission) 289// against live pgvector + full text: when a mission keyword is supplied, only candidates whose full_name, 290// headline, or location text matches that mission are returned, and the RLS boundary still holds so a foreign tenant's SIGINT 291// candidate never surfaces. The mission filter is text match only (the documented WS6 Option A limit). 292func TestHybridSearchMissionScopeExecutes(t *testing.T) { 293 orgA, orgB := seedOrg(t), seedOrg(t) 294 emb := embeddings.NewDeterministic(384) 295 296 seed := func(org uuid.UUID, name, headline string) { 297 vec, _ := emb.Embed(context.Background(), name+" "+headline) 298 pv := pgvector.NewVector(vec) 299 hn := headline 300 if _, err := dbgen.New(migrator).CreateCandidate(context.Background(), dbgen.CreateCandidateParams{ 301 OrganizationID: org, FullName: name, Headline: &hn, ClearanceLevel: "top_secret", 302 ClearancePoly: "none", ClearanceStatus: "active", Available: true, ResumeEmbedding: &pv, 303 }); err != nil { 304 t.Fatalf("seed mission candidate %q: %v", name, err) 305 } 306 } 307 // org A: two SIGINT candidates (text contains SIGINT) and two off mission candidates. 308 seed(orgA, "Alice Analyst", "SIGINT collection and analysis") 309 seed(orgA, "Bob Signals", "SIGINT systems engineer") 310 seed(orgA, "Carol Cloud", "Kubernetes and AWS platform") 311 seed(orgA, "Dave Defender", "network defense operations") 312 // org B: a SIGINT candidate that must NOT leak into org A's scoped retrieval. 313 seed(orgB, "Eve Foreign", "SIGINT foreign tenant") 314 315 qv := pgvector.NewVector(mustEmbed(emb, "intelligence analyst")) 316 var rows []dbgen.SearchCandidatesHybridMissionRow 317 err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error { 318 if _, e := tx.Exec(ctx, "SET LOCAL hnsw.ef_search = 200"); e != nil { 319 return e 320 } 321 var e error 322 rows, e = dbgen.New(tx).SearchCandidatesHybridMission(ctx, dbgen.SearchCandidatesHybridMissionParams{ 323 QueryVector: qv, RowLimit: 10, TextQuery: "intelligence analyst", MissionQuery: "SIGINT", 324 }) 325 return e 326 }) 327 if err != nil { 328 t.Fatalf("mission scoped hybrid search failed: %v", err) 329 } 330 if len(rows) != 2 { 331 names := make([]string, len(rows)) 332 for i, r := range rows { 333 names[i] = r.FullName 334 } 335 t.Fatalf("mission scope returned %d rows %v, want exactly the 2 SIGINT candidates", len(rows), names) 336 } 337 for _, r := range rows { 338 if r.OrganizationID != orgA { 339 t.Fatalf("mission scope leaked a foreign-tenant row: %s (%s)", r.FullName, r.OrganizationID) 340 } 341 if r.FullName != "Alice Analyst" && r.FullName != "Bob Signals" { 342 t.Fatalf("mission scope returned an off-mission candidate: %s", r.FullName) 343 } 344 } 345} 346 347func mustEmbed(e embeddings.Embedder, s string) []float32 { 348 v, _ := e.Embed(context.Background(), s) 349 return v 350} 351 352func ptrStr(s string) *string { return &s } 353 354// TestCommandCenterRLSIsolation proves the Workforce Command Center never mixes tenants: org A's 355// dashboard counts only org A's workforce/opportunities, even though org B has its own seeded data on 356// the same cluster. This is the design's #1 risk (RLS boundary escape on an analytics endpoint). 357func TestCommandCenterRLSIsolation(t *testing.T) { 358 ctx := context.Background() 359 orgA, orgB := seedOrg(t), seedOrg(t) 360 361 // org A: exactly 3 cleared, available candidates; NO opportunities. 362 for i := 0; i < 3; i++ { 363 seedCandidate(t, orgA, "secret", "active", true) 364 } 365 // org B: 5 cleared candidates + a uniquely-named opportunity with one requirement. 366 for i := 0; i < 5; i++ { 367 seedCandidate(t, orgB, "top_secret", "active", true) 368 } 369 decoyOpp := uuid.New() 370 if _, err := migrator.Exec(ctx, 371 `INSERT INTO opportunities (id, organization_id, name, status, value) VALUES ($1,$2,'DECOY-ONLY-OPP','open',1000000)`, 372 decoyOpp, orgB); err != nil { 373 t.Fatalf("seed decoy opp: %v", err) 374 } 375 if _, err := migrator.Exec(ctx, 376 `INSERT INTO requirements (organization_id, opportunity_id, title, labor_category, quantity, required_level, required_poly) 377 VALUES ($1,$2,'Decoy Engineer','Decoy Engineer',2,'secret','none')`, 378 orgB, decoyOpp); err != nil { 379 t.Fatalf("seed decoy req: %v", err) 380 } 381 382 svc := commandcenter.NewService(appDB) 383 now := time.Now() 384 385 va, err := svc.CommandCenter(ctxOrg(orgA), now) 386 if err != nil { 387 t.Fatalf("command center org A: %v", err) 388 } 389 if va.Banner.TotalClearedWorkforce != 3 { 390 t.Fatalf("org A cleared workforce = %d, want 3 (leak of org B's 5?)", va.Banner.TotalClearedWorkforce) 391 } 392 if va.Banner.ActiveRequirements != 0 || len(va.ActiveOpportunities) != 0 { 393 t.Fatalf("org A saw foreign opportunities/requirements: reqs=%d opps=%d", va.Banner.ActiveRequirements, len(va.ActiveOpportunities)) 394 } 395 for _, o := range va.ActiveOpportunities { 396 if o.Name == "DECOY-ONLY-OPP" { 397 t.Fatal("ISOLATION BREACH: org A saw org B's DECOY-ONLY-OPP") 398 } 399 } 400 401 vb, err := svc.CommandCenter(ctxOrg(orgB), now) 402 if err != nil { 403 t.Fatalf("command center org B: %v", err) 404 } 405 if vb.Banner.TotalClearedWorkforce != 5 { 406 t.Fatalf("org B cleared workforce = %d, want 5", vb.Banner.TotalClearedWorkforce) 407 } 408 if vb.Banner.ActiveRequirements != 1 || len(vb.ActiveOpportunities) != 1 || vb.ActiveOpportunities[0].Name != "DECOY-ONLY-OPP" { 409 t.Fatalf("org B should see exactly its own opportunity, got reqs=%d opps=%d", vb.Banner.ActiveRequirements, len(vb.ActiveOpportunities)) 410 } 411} 412 413func TestScreenIntelligenceAndOpportunityFit(t *testing.T) { 414 ctx := context.Background() 415 orgA := seedOrg(t) 416 417 req, err := dbgen.New(migrator).CreateRequirement(ctx, dbgen.CreateRequirementParams{ 418 OrganizationID: orgA, Title: "Cyber Engineer", CustomerAgency: ptrStr("DISA"), 419 RequiredLevel: "secret", RequiredPoly: "none", RequiredYears: 5, KeyPersonnel: true, 420 }) 421 if err != nil { 422 t.Fatalf("seed requirement: %v", err) 423 } 424 425 cand := seedCandidate(t, orgA, "ts_sci", "active", true) 426 if _, err := migrator.Exec(ctx, 427 "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", 428 cand.ID); err != nil { 429 t.Fatalf("enrich candidate: %v", err) 430 } 431 if err := dbgen.New(migrator).AddCandidateCustomerExperience(ctx, dbgen.AddCandidateCustomerExperienceParams{ 432 OrganizationID: orgA, CandidateID: cand.ID, Agency: "DISA", 433 }); err != nil { 434 t.Fatalf("add customer experience: %v", err) 435 } 436 437 svc := matching.NewService(appDB, nil, nil) 438 439 view, err := svc.Screen(ctxOrg(orgA), cand.ID, req.ID) 440 if err != nil { 441 t.Fatalf("Screen: %v", err) 442 } 443 if view.MissionReadiness.Status != "Mission Ready" || view.MissionReadiness.Score != "100.00" { 444 t.Fatalf("mission readiness = %+v", view.MissionReadiness) 445 } 446 if len(view.MissionReadiness.Categories) != 6 { 447 t.Fatalf("expected 6 readiness categories, got %d", len(view.MissionReadiness.Categories)) 448 } 449 if !view.ClearanceIntel.SCIEligible || !view.ClearanceIntel.FullScopePoly || !view.ClearanceIntel.RecentClassifiedSupport { 450 t.Fatalf("clearance intel = %+v", view.ClearanceIntel) 451 } 452 if view.ClearanceIntel.InvestigationAgeMonths == nil || *view.ClearanceIntel.InvestigationAgeMonths < 30 { 453 t.Fatalf("expected an investigation age ~41mo, got %v", view.ClearanceIntel.InvestigationAgeMonths) 454 } 455 if view.WinImpact.LevelCode != "high" { 456 t.Fatalf("win impact = %+v", view.WinImpact) 457 } 458 keyPersonnel := false 459 for _, rsn := range view.WinImpact.Reasons { 460 if rsn == "Matches Key Personnel requirements" { 461 keyPersonnel = true 462 } 463 } 464 if !keyPersonnel { 465 t.Fatalf("expected Key Personnel reason, got %v", view.WinImpact.Reasons) 466 } 467 468 fits, err := svc.OpportunitiesForCandidate(ctxOrg(orgA), cand.ID, 10) 469 if err != nil { 470 t.Fatalf("OpportunitiesForCandidate: %v", err) 471 } 472 if len(fits) == 0 || fits[0].RequirementID != req.ID || fits[0].Status != "Mission Ready" { 473 t.Fatalf("opportunity fit = %+v", fits) 474 } 475} 476