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 migratorDSN := flag.String("migrator-dsn", os.Getenv("MM_MIGRATOR_DATABASE_URL"), "migrator DSN to the maintenance database (the migrations run as the migrator so it OWNS the tables)") 44 name := flag.String("name", "", "tenant database name (lowercase identifier, e.g. tenant_acme)") 45 dropFirst := flag.Bool("drop-first", false, "DANGER: drop the database first (staging only)") 46 flag.Parse() 47 48 if *adminDSN == "" || *name == "" { 49 log.Fatal("provision-tenant: -admin-dsn and -name are required") 50 } 51 if *migratorDSN == "" { 52 log.Fatal("provision-tenant: -migrator-dsn (or MM_MIGRATOR_DATABASE_URL) is required so the migrator owns the tables") 53 } 54 if !dbNameRe.MatchString(*name) { 55 log.Fatalf("provision-tenant: %q is not a safe database name (want ^[a-z][a-z0-9_]{2,62}$)", *name) 56 } 57 ctx := context.Background() 58 59 if err := provision(ctx, *adminDSN, *migratorDSN, *name, *dropFirst); err != nil { 60 log.Fatalf("provision-tenant: %v", err) 61 } 62 log.Printf("provision-tenant: DONE. Tenant database %q is schema identical to the shared database, RLS forced, eeo segregated, vector(384) ready.", *name) 63 log.Printf("provision-tenant: next, run cmd/embed-backfill against this database once it has candidates.") 64} 65 66func provision(ctx context.Context, adminDSN, migratorDSN, name string, dropFirst bool) error { 67 admin, err := pgx.Connect(ctx, adminDSN) 68 if err != nil { 69 return fmt.Errorf("admin connect: %w", err) 70 } 71 defer admin.Close(ctx) 72 73 // 1. Ensure the cluster roles exist (idempotent DO guards in the bootstrap). 74 bootstrap, err := dbembed.Bootstrap.ReadFile("bootstrap/00_roles.sql") 75 if err != nil { 76 return err 77 } 78 if _, err := admin.Exec(ctx, string(bootstrap)); err != nil { 79 return fmt.Errorf("role bootstrap: %w", err) 80 } 81 82 // 2. Create the database (CREATE DATABASE cannot run inside a transaction or be parameterized; the 83 // name was validated above). 84 if dropFirst { 85 _, _ = admin.Exec(ctx, "DROP DATABASE IF EXISTS "+name) 86 } 87 var exists bool 88 _ = admin.QueryRow(ctx, "SELECT EXISTS(SELECT 1 FROM pg_database WHERE datname=$1)", name).Scan(&exists) 89 if exists { 90 return fmt.Errorf("database %q already exists (use -drop-first in staging to recreate)", name) 91 } 92 if _, err := admin.Exec(ctx, "CREATE DATABASE "+name); err != nil { 93 return fmt.Errorf("create database: %w", err) 94 } 95 log.Printf("provision-tenant: created database %q", name) 96 97 // 3. In the new database: extension, schema usage, and connect privilege scoped to this database. 98 tenantAdminDSN := swapDB(adminDSN, name) 99 tdb, err := pgx.Connect(ctx, tenantAdminDSN) 100 if err != nil { 101 return fmt.Errorf("tenant connect: %w", err) 102 } 103 defer tdb.Close(ctx) 104 for _, stmt := range []string{ 105 "CREATE EXTENSION IF NOT EXISTS vector", 106 "GRANT USAGE ON SCHEMA public TO missionmatch_app, missionmatch_compliance", 107 // The migrations run as the migrator below, so the migrator OWNS every table (it can then write 108 // identity and run future migrations). It needs CREATE on the public schema to create them; it 109 // creates and owns the eeo schema itself in the eeo migration. 110 "GRANT CREATE, USAGE ON SCHEMA public TO missionmatch_migrator", 111 // CREATE on the database lets the migrator create the eeo schema in the eeo migration set. 112 "GRANT CREATE ON DATABASE " + name + " TO missionmatch_migrator", 113 "REVOKE CONNECT ON DATABASE " + name + " FROM PUBLIC", 114 "GRANT CONNECT ON DATABASE " + name + " TO missionmatch_app, missionmatch_compliance, missionmatch_migrator", 115 } { 116 if _, err := tdb.Exec(ctx, stmt); err != nil { 117 return fmt.Errorf("setup %q: %w", stmt, err) 118 } 119 } 120 121 // 4. Run both migration sets via goose AS THE MIGRATOR (not the admin), so the migrator owns the 122 // tables: the app and compliance roles get exactly the grants the migrations spell out, while the 123 // migrator can write identity rows and apply future migrations (migrate-all-tenants). The extension 124 // and the CREATE grants above were done as the admin because they need superuser. 125 migTenantDSN := swapDB(migratorDSN, name) 126 sqlDB, err := sql.Open("pgx", migTenantDSN) 127 if err != nil { 128 return err 129 } 130 defer func() { _ = sqlDB.Close() }() 131 sqlDB.SetConnMaxLifetime(5 * time.Minute) 132 133 if err := goose.SetDialect("postgres"); err != nil { 134 return err 135 } 136 goose.SetBaseFS(dbembed.Migrations) 137 goose.SetTableName("goose_db_version") 138 if err := goose.UpContext(ctx, sqlDB, "migrations"); err != nil { 139 return fmt.Errorf("public migrations: %w", err) 140 } 141 log.Printf("provision-tenant: applied public schema migrations") 142 143 goose.SetBaseFS(dbembed.EEOMigrations) 144 goose.SetTableName("goose_db_version_eeo") 145 if err := goose.UpContext(ctx, sqlDB, "migrations_eeo"); err != nil { 146 return fmt.Errorf("eeo migrations: %w", err) 147 } 148 log.Printf("provision-tenant: applied segregated eeo migrations") 149 150 return verify(ctx, tdb) 151} 152 153// verify asserts the isolation and AI invariants the architecture requires before a tenant is usable. 154func verify(ctx context.Context, tdb *pgx.Conn) error { 155 checks := []struct { 156 name string 157 query string 158 want bool 159 }{ 160 {"pgvector extension", "SELECT EXISTS(SELECT 1 FROM pg_extension WHERE extname='vector')", true}, 161 {"candidates RLS forced", "SELECT relforcerowsecurity FROM pg_class WHERE relname='candidates'", true}, 162 {"eeo schema present", "SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname='eeo')", true}, 163 {"app has NO grant on eeo.candidate_demographics", 164 "SELECT NOT has_table_privilege('missionmatch_app','eeo.candidate_demographics','SELECT')", true}, 165 {"vector(384) column on candidates", 166 "SELECT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='candidates' AND column_name='resume_embedding')", true}, 167 } 168 for _, c := range checks { 169 var got bool 170 if err := tdb.QueryRow(ctx, c.query).Scan(&got); err != nil { 171 return fmt.Errorf("verify %s: %w", c.name, err) 172 } 173 if got != c.want { 174 return fmt.Errorf("verify FAILED: %s = %v, want %v", c.name, got, c.want) 175 } 176 log.Printf("provision-tenant: verified %s", c.name) 177 } 178 return nil 179} 180 181// swapDB replaces the database name in a DSN, so the admin DSN to the maintenance database is reused to 182// reach the new tenant database. 183func swapDB(dsn, name string) string { 184 cfg, err := pgx.ParseConfig(dsn) 185 if err != nil { 186 return dsn 187 } 188 cfg.Database = name 189 // Rebuild a key value DSN (pgx accepts both URL and key value forms). 190 out := fmt.Sprintf("host=%s port=%d user=%s dbname=%s", cfg.Host, cfg.Port, cfg.User, name) 191 if cfg.Password != "" { 192 out += " password=" + cfg.Password 193 } 194 if cfg.TLSConfig == nil { 195 out += " sslmode=disable" 196 } 197 return out 198} 199