Janady13/missionmatch / cmd/provision-tenant/main.go

1// Command provision-tenant creates a complete, isolated Postgres database for one client under the 2// database per tenant model. It is the tenant isolation primitive: a client's data, embeddings, graphs, 3// and memory all live inside this database, and a Postgres connection bound to it physically cannot read 4// another tenant's database (there is no cross database query path). RLS inside the database is defense 5// in depth and supports the migration from the shared database. 6// 7// It runs the SAME migrations as the shared database (the embedded db/migrations plus the segregated 8// db/migrations_eeo), so a tenant database is schema identical: same tables, same RLS forced, same eeo 9// schema the app role cannot reach, same vector(384) columns and HNSW indexes. A new tenant provisions 10// EMPTY and fills only from its own data (run cmd/embed-backfill against it once it has candidates). 11// 12// It is an operator tool: it needs an admin (superuser) DSN to CREATE DATABASE and the cluster roles. It 13// does NOT create live client databases on its own; the human runs it as part of the validated cutover. 14// 15// Usage: 16// 17// provision-tenant -admin-dsn postgres://postgres@host/postgres?sslmode=disable -name tenant_acme 18package main 19 20import ( 21 "context" 22 "database/sql" 23 "flag" 24 "fmt" 25 "log" 26 "os" 27 "regexp" 28 "time" 29 30 "github.com/jackc/pgx/v5" 31 _ "github.com/jackc/pgx/v5/stdlib" 32 "github.com/pressly/goose/v3" 33 34 dbembed "github.com/missionworkforce/missionmatch/db" 35) 36 37// dbNameRe constrains the tenant database name to a safe identifier (CREATE DATABASE cannot be 38// parameterized, so the name must be validated, never interpolated from untrusted input). 39var dbNameRe = regexp.MustCompile(`^[a-z][a-z0-9_]{2,62}$`) 40 41func main() { 42 adminDSN := flag.String("admin-dsn", os.Getenv("MM_ADMIN_DSN"), "superuser DSN to the maintenance database (CREATE DATABASE + roles)") 43 name := flag.String("name", "", "tenant database name (lowercase identifier, e.g. tenant_acme)") 44 dropFirst := flag.Bool("drop-first", false, "DANGER: drop the database first (staging only)") 45 flag.Parse() 46 47 if *adminDSN == "" || *name == "" { 48 log.Fatal("provision-tenant: -admin-dsn and -name are required") 49 } 50 if !dbNameRe.MatchString(*name) { 51 log.Fatalf("provision-tenant: %q is not a safe database name (want ^[a-z][a-z0-9_]{2,62}$)", *name) 52 } 53 ctx := context.Background() 54 55 if err := provision(ctx, *adminDSN, *name, *dropFirst); err != nil { 56 log.Fatalf("provision-tenant: %v", err) 57 } 58 log.Printf("provision-tenant: DONE. Tenant database %q is schema identical to the shared database, RLS forced, eeo segregated, vector(384) ready.", *name) 59 log.Printf("provision-tenant: next, run cmd/embed-backfill against this database once it has candidates.") 60} 61 62func provision(ctx context.Context, adminDSN, name string, dropFirst bool) error { 63 admin, err := pgx.Connect(ctx, adminDSN) 64 if err != nil { 65 return fmt.Errorf("admin connect: %w", err) 66 } 67 defer admin.Close(ctx) 68 69 // 1. Ensure the cluster roles exist (idempotent DO guards in the bootstrap). 70 bootstrap, err := dbembed.Bootstrap.ReadFile("bootstrap/00_roles.sql") 71 if err != nil { 72 return err 73 } 74 if _, err := admin.Exec(ctx, string(bootstrap)); err != nil { 75 return fmt.Errorf("role bootstrap: %w", err) 76 } 77 78 // 2. Create the database (CREATE DATABASE cannot run inside a transaction or be parameterized; the 79 // name was validated above). 80 if dropFirst { 81 _, _ = admin.Exec(ctx, "DROP DATABASE IF EXISTS "+name) 82 } 83 var exists bool 84 _ = admin.QueryRow(ctx, "SELECT EXISTS(SELECT 1 FROM pg_database WHERE datname=$1)", name).Scan(&exists) 85 if exists { 86 return fmt.Errorf("database %q already exists (use -drop-first in staging to recreate)", name) 87 } 88 if _, err := admin.Exec(ctx, "CREATE DATABASE "+name); err != nil { 89 return fmt.Errorf("create database: %w", err) 90 } 91 log.Printf("provision-tenant: created database %q", name) 92 93 // 3. In the new database: extension, schema usage, and connect privilege scoped to this database. 94 tenantAdminDSN := swapDB(adminDSN, name) 95 tdb, err := pgx.Connect(ctx, tenantAdminDSN) 96 if err != nil { 97 return fmt.Errorf("tenant connect: %w", err) 98 } 99 defer tdb.Close(ctx) 100 for _, stmt := range []string{ 101 "CREATE EXTENSION IF NOT EXISTS vector", 102 "GRANT USAGE ON SCHEMA public TO missionmatch_app, missionmatch_compliance", 103 "REVOKE CONNECT ON DATABASE " + name + " FROM PUBLIC", 104 "GRANT CONNECT ON DATABASE " + name + " TO missionmatch_app, missionmatch_compliance, missionmatch_migrator", 105 } { 106 if _, err := tdb.Exec(ctx, stmt); err != nil { 107 return fmt.Errorf("setup %q: %w", stmt, err) 108 } 109 } 110 111 // 4. Run both migration sets via goose against the new database (database/sql over pgx stdlib). 112 sqlDB, err := sql.Open("pgx", tenantAdminDSN) 113 if err != nil { 114 return err 115 } 116 defer func() { _ = sqlDB.Close() }() 117 sqlDB.SetConnMaxLifetime(5 * time.Minute) 118 119 if err := goose.SetDialect("postgres"); err != nil { 120 return err 121 } 122 goose.SetBaseFS(dbembed.Migrations) 123 goose.SetTableName("goose_db_version") 124 if err := goose.UpContext(ctx, sqlDB, "migrations"); err != nil { 125 return fmt.Errorf("public migrations: %w", err) 126 } 127 log.Printf("provision-tenant: applied public schema migrations") 128 129 goose.SetBaseFS(dbembed.EEOMigrations) 130 goose.SetTableName("goose_db_version_eeo") 131 if err := goose.UpContext(ctx, sqlDB, "migrations_eeo"); err != nil { 132 return fmt.Errorf("eeo migrations: %w", err) 133 } 134 log.Printf("provision-tenant: applied segregated eeo migrations") 135 136 return verify(ctx, tdb) 137} 138 139// verify asserts the isolation and AI invariants the architecture requires before a tenant is usable. 140func verify(ctx context.Context, tdb *pgx.Conn) error { 141 checks := []struct { 142 name string 143 query string 144 want bool 145 }{ 146 {"pgvector extension", "SELECT EXISTS(SELECT 1 FROM pg_extension WHERE extname='vector')", true}, 147 {"candidates RLS forced", "SELECT relforcerowsecurity FROM pg_class WHERE relname='candidates'", true}, 148 {"eeo schema present", "SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname='eeo')", true}, 149 {"app has NO grant on eeo.candidate_demographics", 150 "SELECT NOT has_table_privilege('missionmatch_app','eeo.candidate_demographics','SELECT')", true}, 151 {"vector(384) column on candidates", 152 "SELECT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='candidates' AND column_name='resume_embedding')", true}, 153 } 154 for _, c := range checks { 155 var got bool 156 if err := tdb.QueryRow(ctx, c.query).Scan(&got); err != nil { 157 return fmt.Errorf("verify %s: %w", c.name, err) 158 } 159 if got != c.want { 160 return fmt.Errorf("verify FAILED: %s = %v, want %v", c.name, got, c.want) 161 } 162 log.Printf("provision-tenant: verified %s", c.name) 163 } 164 return nil 165} 166 167// swapDB replaces the database name in a DSN, so the admin DSN to the maintenance database is reused to 168// reach the new tenant database. 169func swapDB(dsn, name string) string { 170 cfg, err := pgx.ParseConfig(dsn) 171 if err != nil { 172 return dsn 173 } 174 cfg.Database = name 175 // Rebuild a key value DSN (pgx accepts both URL and key value forms). 176 out := fmt.Sprintf("host=%s port=%d user=%s dbname=%s", cfg.Host, cfg.Port, cfg.User, name) 177 if cfg.Password != "" { 178 out += " password=" + cfg.Password 179 } 180 if cfg.TLSConfig == nil { 181 out += " sslmode=disable" 182 } 183 return out 184} 185