Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / internal/integration/rls_integration_test.go |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 39df94f92b2a33f852481fe1954dabed841524bb |
| Branch | w0-aedt-compliance |
| Path or archive member | internal/integration/rls_integration_test.go |
| Original SHA-256 | 3eb3f52b27daccf0aff45241fa162e25fdeca0e14970a3841316863e6b83908a |
| Git blob SHA | 116379b0d89a0652e461f174fe02b91dc8edff79 |
| Reading-copy SHA-256 | 3eb3f52b27daccf0aff45241fa162e25fdeca0e14970a3841316863e6b83908a |
Recovered source transcript
Download complete source text ↓417 lines · 15.4 KB
//go:build integration
// Package integration runs the data layer against a REAL Postgres 16 + pgvector. It proves at
// runtime the properties the adversarial review could only check statically: tenant isolation,
// default-deny, the composite-FK cross-tenant injection block, numeric(7,4) round-trip, audit
// immutability, the EEO privilege boundary, and the hardened pgvector/RRF search path.
//
// Run: go test -tags=integration ./internal/integration/ -v
// Env: MM_APP_DSN, MM_MIGRATOR_DSN (required), MM_COMPLIANCE_DSN (optional).
package integration
import (
"context"
"errors"
"fmt"
"os"
"testing"
"time"
"github.com/google/uuid"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgconn"
"github.com/jackc/pgx/v5/pgxpool"
"github.com/pgvector/pgvector-go"
"github.com/shopspring/decimal"
"github.com/missionworkforce/missionmatch/internal/modules/commandcenter"
"github.com/missionworkforce/missionmatch/internal/modules/matching"
"github.com/missionworkforce/missionmatch/internal/platform/database"
"github.com/missionworkforce/missionmatch/internal/platform/database/dbgen"
"github.com/missionworkforce/missionmatch/internal/platform/embeddings"
"github.com/missionworkforce/missionmatch/internal/platform/tenant"
)
var (
appDB *tenant.DB
migrator *pgxpool.Pool
compliance *pgxpool.Pool
)
func TestMain(m *testing.M) {
ctx := context.Background()
appDSN, migDSN, compDSN := os.Getenv("MM_APP_DSN"), os.Getenv("MM_MIGRATOR_DSN"), os.Getenv("MM_COMPLIANCE_DSN")
if appDSN == "" || migDSN == "" {
fmt.Println("integration: MM_APP_DSN / MM_MIGRATOR_DSN not set — skipping integration tests")
os.Exit(0)
}
pool, err := database.NewPool(ctx, appDSN)
if err != nil {
panic("app pool: " + err.Error())
}
appDB = tenant.New(pool)
if migrator, err = database.NewPool(ctx, migDSN); err != nil {
panic("migrator pool: " + err.Error())
}
if compDSN != "" {
if compliance, err = database.NewPool(ctx, compDSN); err != nil {
panic("compliance pool: " + err.Error())
}
}
os.Exit(m.Run())
}
// ---------- helpers (seed via the BYPASSRLS migrator so we can write across tenants) ----------
func seedOrg(t *testing.T) uuid.UUID {
t.Helper()
id := uuid.New()
slug := "org-" + id.String()[:12]
if _, err := migrator.Exec(context.Background(),
"INSERT INTO organizations (id, name, slug) VALUES ($1,$2,$3)", id, slug, slug); err != nil {
t.Fatalf("seed org: %v", err)
}
return id
}
func seedCandidate(t *testing.T, org uuid.UUID, level, status string, available bool) dbgen.Candidate {
t.Helper()
c, err := dbgen.New(migrator).CreateCandidate(context.Background(), dbgen.CreateCandidateParams{
OrganizationID: org, FullName: "Cand " + level, ClearanceLevel: level, ClearancePoly: "none",
ClearanceStatus: status, Available: available,
})
if err != nil {
t.Fatalf("seed candidate: %v", err)
}
return c
}
func ctxOrg(org uuid.UUID) context.Context { return tenant.WithOrg(context.Background(), org.String()) }
func pgCode(err error) string {
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
return pgErr.Code
}
return ""
}
// ---------- tests ----------
func TestCrossTenantReadIsolation(t *testing.T) {
orgA, orgB := seedOrg(t), seedOrg(t)
candA := seedCandidate(t, orgA, "secret", "active", true)
candB := seedCandidate(t, orgB, "secret", "active", true)
var listed []dbgen.Candidate
if err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
var e error
listed, e = dbgen.New(tx).ListCandidates(ctx, dbgen.ListCandidatesParams{Limit: 1000})
return e
}); err != nil {
t.Fatalf("list: %v", err)
}
sawA, sawB := false, false
for _, c := range listed {
sawA = sawA || c.ID == candA.ID
sawB = sawB || c.ID == candB.ID
}
if !sawA {
t.Fatal("org A must see its own candidate")
}
if sawB {
t.Fatal("ISOLATION BREACH: org A saw org B's candidate")
}
// Direct read of B's candidate under org A's session must return zero rows (RLS hides it).
err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
_, e := dbgen.New(tx).GetCandidate(ctx, candB.ID)
return e
})
if !errors.Is(err, pgx.ErrNoRows) {
t.Fatalf("cross-tenant GetCandidate should be ErrNoRows, got %v", err)
}
}
func TestUnsetGUCDefaultDeny(t *testing.T) {
orgA := seedOrg(t)
seedCandidate(t, orgA, "secret", "active", true)
// App role, NO tenant transaction => GUC unset => current_setting is NULL => policy denies => 0 rows.
var n int
if err := appDB.Pool.QueryRow(context.Background(), "SELECT count(*) FROM candidates").Scan(&n); err != nil {
t.Fatalf("count: %v", err)
}
if n != 0 {
t.Fatalf("default-deny breached: unscoped app query saw %d rows (want 0)", n)
}
}
func TestCompositeFKBlocksCrossTenantInjection(t *testing.T) {
orgA, orgB := seedOrg(t), seedOrg(t)
candB := seedCandidate(t, orgB, "secret", "active", true) // victim parent in org B
skill, err := dbgen.New(migrator).UpsertSkill(context.Background(), dbgen.UpsertSkillParams{
Name: "FKProbe-" + uuid.NewString(),
})
if err != nil {
t.Fatalf("seed skill: %v", err)
}
// As org A, try to attach a skill row (org=A) to org B's candidate. RLS WITH CHECK passes (row
// org == session org), but the COMPOSITE FK (organization_id, candidate_id) -> candidates has no
// matching (A, candB) parent, so the write MUST fail with a foreign-key violation (23503).
err = appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
return dbgen.New(tx).AddCandidateSkill(ctx, dbgen.AddCandidateSkillParams{
OrganizationID: orgA, CandidateID: candB.ID, SkillID: skill.ID,
})
})
if code := pgCode(err); code != "23503" {
t.Fatalf("cross-tenant injection NOT blocked: want FK violation 23503, got err=%v code=%q", err, code)
}
}
func TestPerfectScorePersistsViaService(t *testing.T) {
orgA := seedOrg(t)
// Requirement with nothing required => every component scores 100 => total exactly 100.
req, err := dbgen.New(migrator).CreateRequirement(context.Background(), dbgen.CreateRequirementParams{
OrganizationID: orgA, Title: "Open Role", RequiredLevel: "none", RequiredPoly: "none",
})
if err != nil {
t.Fatalf("seed requirement: %v", err)
}
cand := seedCandidate(t, orgA, "secret", "active", true)
svc := matching.NewService(appDB, nil, nil) // exact-only matcher
out, err := svc.GenerateSlate(ctxOrg(orgA), matching.GenerateSlateInput{RequirementID: req.ID, Name: "Perfect Slate"})
if err != nil {
t.Fatalf("GenerateSlate (numeric overflow would surface here): %v", err)
}
if len(out.Members) != 1 || out.Members[0].CandidateID != cand.ID.String() {
t.Fatalf("unexpected members: %+v", out.Members)
}
if out.Members[0].Total != "100.00" || out.Members[0].Band != "green" {
t.Fatalf("perfect candidate should be 100.00/green, got %s/%s", out.Members[0].Total, out.Members[0].Band)
}
// Read back the frozen decision record — proves numeric(7,4) stored 100.0000 (the SQLC-1 fix).
var members []dbgen.SlateMember
if err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
var e error
members, e = dbgen.New(tx).ListSlateMembers(ctx, out.SlateID)
return e
}); err != nil {
t.Fatalf("read back: %v", err)
}
if len(members) != 1 || !members[0].TotalScore.Equal(decimal.NewFromInt(100)) {
t.Fatalf("persisted total should be 100, got %+v", members)
}
}
func TestAuditAppendOnly(t *testing.T) {
orgA := seedOrg(t)
if err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
_, e := dbgen.New(tx).InsertAuditLog(ctx, dbgen.InsertAuditLogParams{OrganizationID: &orgA, Action: "test.event"})
return e
}); err != nil {
t.Fatalf("insert audit: %v", err)
}
// App role has no UPDATE/DELETE grant on audit_logs => permission denied (42501).
if _, err := appDB.Pool.Exec(context.Background(), "UPDATE audit_logs SET action='tamper' WHERE organization_id=$1", orgA); pgCode(err) != "42501" {
t.Fatalf("app UPDATE on audit_logs should be permission-denied (42501), got %v", err)
}
// Even the owner (migrator) is stopped by the append-only trigger (raise_exception P0001).
_, err := migrator.Exec(context.Background(), "UPDATE audit_logs SET action='tamper' WHERE organization_id=$1", orgA)
if err == nil {
t.Fatal("append-only trigger failed: owner UPDATE on audit_logs succeeded")
}
if pgCode(err) != "P0001" {
t.Fatalf("owner UPDATE should raise the append-only trigger (P0001), got %v", err)
}
}
func TestEEOPrivilegeBoundary(t *testing.T) {
// The matching app role must have ZERO access to the eeo schema (permission denied 42501).
_, err := appDB.Pool.Exec(context.Background(), "SELECT 1 FROM eeo.candidate_demographics LIMIT 1")
if pgCode(err) != "42501" {
t.Fatalf("app role must be denied EEO access (42501), got %v", err)
}
if compliance != nil {
// The compliance role CAN reach it (no permission error; RLS may legitimately return 0 rows).
if _, err := compliance.Exec(context.Background(), "SELECT 1 FROM eeo.candidate_demographics LIMIT 1"); err != nil {
t.Fatalf("compliance role should access EEO, got %v", err)
}
}
}
func TestHybridSearchPathExecutes(t *testing.T) {
orgA := seedOrg(t)
emb := embeddings.NewDeterministic(384)
names := []string{"Cloud Security Engineer", "Kubernetes Platform Lead", "Network Defense Analyst"}
for _, name := range names {
vec, _ := emb.Embed(context.Background(), name)
pv := pgvector.NewVector(vec)
hn := name
if _, err := dbgen.New(migrator).CreateCandidate(context.Background(), dbgen.CreateCandidateParams{
OrganizationID: orgA, FullName: name, Headline: &hn, ClearanceLevel: "secret",
ClearancePoly: "none", ClearanceStatus: "active", Available: true, ResumeEmbedding: &pv,
}); err != nil {
t.Fatalf("seed search candidate: %v", err)
}
}
qv := pgvector.NewVector(mustEmbed(emb, "cloud security"))
var rows []dbgen.SearchCandidatesHybridRow
err := appDB.RunInTenantTx(ctxOrg(orgA), func(ctx context.Context, tx pgx.Tx) error {
if _, e := tx.Exec(ctx, "SET LOCAL hnsw.ef_search = 200"); e != nil { // PGV-3
return e
}
var e error
rows, e = dbgen.New(tx).SearchCandidatesHybrid(ctx, dbgen.SearchCandidatesHybridParams{
Column1: qv, Limit: 10, WebsearchToTsquery: "cloud security",
})
return e
})
if err != nil {
t.Fatalf("hybrid search executed against live pgvector failed: %v", err)
}
if len(rows) == 0 {
t.Fatal("hybrid search returned no rows (expected the seeded candidates)")
}
for _, r := range rows {
if r.OrganizationID != orgA {
t.Fatalf("hybrid search leaked a foreign-tenant row: %s", r.OrganizationID)
}
}
}
func mustEmbed(e embeddings.Embedder, s string) []float32 {
v, _ := e.Embed(context.Background(), s)
return v
}
func ptrStr(s string) *string { return &s }
// TestCommandCenterRLSIsolation proves the Workforce Command Center never mixes tenants: org A's
// dashboard counts only org A's workforce/opportunities, even though org B has its own seeded data on
// the same cluster. This is the design's #1 risk (RLS boundary escape on an analytics endpoint).
func TestCommandCenterRLSIsolation(t *testing.T) {
ctx := context.Background()
orgA, orgB := seedOrg(t), seedOrg(t)
// org A: exactly 3 cleared, available candidates; NO opportunities.
for i := 0; i < 3; i++ {
seedCandidate(t, orgA, "secret", "active", true)
}
// org B: 5 cleared candidates + a uniquely-named opportunity with one requirement.
for i := 0; i < 5; i++ {
seedCandidate(t, orgB, "top_secret", "active", true)
}
decoyOpp := uuid.New()
if _, err := migrator.Exec(ctx,
`INSERT INTO opportunities (id, organization_id, name, status, value) VALUES ($1,$2,'DECOY-ONLY-OPP','open',1000000)`,
decoyOpp, orgB); err != nil {
t.Fatalf("seed decoy opp: %v", err)
}
if _, err := migrator.Exec(ctx,
`INSERT INTO requirements (organization_id, opportunity_id, title, labor_category, quantity, required_level, required_poly)
VALUES ($1,$2,'Decoy Engineer','Decoy Engineer',2,'secret','none')`,
orgB, decoyOpp); err != nil {
t.Fatalf("seed decoy req: %v", err)
}
svc := commandcenter.NewService(appDB)
now := time.Now()
va, err := svc.CommandCenter(ctxOrg(orgA), now)
if err != nil {
t.Fatalf("command center org A: %v", err)
}
if va.Banner.TotalClearedWorkforce != 3 {
t.Fatalf("org A cleared workforce = %d, want 3 (leak of org B's 5?)", va.Banner.TotalClearedWorkforce)
}
if va.Banner.ActiveRequirements != 0 || len(va.ActiveOpportunities) != 0 {
t.Fatalf("org A saw foreign opportunities/requirements: reqs=%d opps=%d", va.Banner.ActiveRequirements, len(va.ActiveOpportunities))
}
for _, o := range va.ActiveOpportunities {
if o.Name == "DECOY-ONLY-OPP" {
t.Fatal("ISOLATION BREACH: org A saw org B's DECOY-ONLY-OPP")
}
}
vb, err := svc.CommandCenter(ctxOrg(orgB), now)
if err != nil {
t.Fatalf("command center org B: %v", err)
}
if vb.Banner.TotalClearedWorkforce != 5 {
t.Fatalf("org B cleared workforce = %d, want 5", vb.Banner.TotalClearedWorkforce)
}
if vb.Banner.ActiveRequirements != 1 || len(vb.ActiveOpportunities) != 1 || vb.ActiveOpportunities[0].Name != "DECOY-ONLY-OPP" {
t.Fatalf("org B should see exactly its own opportunity, got reqs=%d opps=%d", vb.Banner.ActiveRequirements, len(vb.ActiveOpportunities))
}
}
func TestScreenIntelligenceAndOpportunityFit(t *testing.T) {
ctx := context.Background()
orgA := seedOrg(t)
req, err := dbgen.New(migrator).CreateRequirement(ctx, dbgen.CreateRequirementParams{
OrganizationID: orgA, Title: "Cyber Engineer", CustomerAgency: ptrStr("DISA"),
RequiredLevel: "secret", RequiredPoly: "none", RequiredYears: 5, KeyPersonnel: true,
})
if err != nil {
t.Fatalf("seed requirement: %v", err)
}
cand := seedCandidate(t, orgA, "ts_sci", "active", true)
if _, err := migrator.Exec(ctx,
"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",
cand.ID); err != nil {
t.Fatalf("enrich candidate: %v", err)
}
if err := dbgen.New(migrator).AddCandidateCustomerExperience(ctx, dbgen.AddCandidateCustomerExperienceParams{
OrganizationID: orgA, CandidateID: cand.ID, Agency: "DISA",
}); err != nil {
t.Fatalf("add customer experience: %v", err)
}
svc := matching.NewService(appDB, nil, nil)
view, err := svc.Screen(ctxOrg(orgA), cand.ID, req.ID)
if err != nil {
t.Fatalf("Screen: %v", err)
}
if view.MissionReadiness.Status != "Mission Ready" || view.MissionReadiness.Score != "100.00" {
t.Fatalf("mission readiness = %+v", view.MissionReadiness)
}
if len(view.MissionReadiness.Categories) != 6 {
t.Fatalf("expected 6 readiness categories, got %d", len(view.MissionReadiness.Categories))
}
if !view.ClearanceIntel.SCIEligible || !view.ClearanceIntel.FullScopePoly || !view.ClearanceIntel.RecentClassifiedSupport {
t.Fatalf("clearance intel = %+v", view.ClearanceIntel)
}
if view.ClearanceIntel.InvestigationAgeMonths == nil || *view.ClearanceIntel.InvestigationAgeMonths < 30 {
t.Fatalf("expected an investigation age ~41mo, got %v", view.ClearanceIntel.InvestigationAgeMonths)
}
if view.WinImpact.LevelCode != "high" {
t.Fatalf("win impact = %+v", view.WinImpact)
}
keyPersonnel := false
for _, rsn := range view.WinImpact.Reasons {
if rsn == "Matches Key Personnel requirements" {
keyPersonnel = true
}
}
if !keyPersonnel {
t.Fatalf("expected Key Personnel reason, got %v", view.WinImpact.Reasons)
}
fits, err := svc.OpportunitiesForCandidate(ctxOrg(orgA), cand.ID, 10)
if err != nil {
t.Fatalf("OpportunitiesForCandidate: %v", err)
}
if len(fits) == 0 || fits[0].RequirementID != req.ID || fits[0].Status != "Mission Ready" {
t.Fatalf("opportunity fit = %+v", fits)
}
}