Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / cmd/provision-tenant/main.go |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | cmd/provision-tenant/main.go |
| Original SHA-256 | c29ec07b4b6bdec72df3cde983696e75c56e4d38c50422bd588da48c8e917a02 |
| Git blob SHA | 514846f8d796fad7696725ba9af864174365909e |
| Reading-copy SHA-256 | c29ec07b4b6bdec72df3cde983696e75c56e4d38c50422bd588da48c8e917a02 |
Recovered source transcript
Download complete source text ↓205 lines · 8.4 KB
// Command provision-tenant creates a complete, isolated Postgres database for one client under the
// database per tenant model. It is the tenant isolation primitive: a client's data, embeddings, graphs,
// and memory all live inside this database, and a Postgres connection bound to it physically cannot read
// another tenant's database (there is no cross database query path). RLS inside the database is defense
// in depth and supports the migration from the shared database.
//
// It runs the SAME migrations as the shared database (the embedded db/migrations plus the segregated
// db/migrations_eeo), so a tenant database is schema identical: same tables, same RLS forced, same eeo
// schema the app role cannot reach, same vector(384) columns and HNSW indexes. A new tenant provisions
// EMPTY and fills only from its own data (run cmd/embed-backfill against it once it has candidates).
//
// It is an operator tool: it needs an admin (superuser) DSN to CREATE DATABASE and the cluster roles. It
// does NOT create live client databases on its own; the human runs it as part of the validated cutover.
//
// Usage:
//
// provision-tenant -admin-dsn "postgres://postgres@host/postgres?sslmode=disable" -name tenant_acme
package main
import (
"context"
"database/sql"
"flag"
"fmt"
"log"
"os"
"regexp"
"time"
"github.com/jackc/pgx/v5"
_ "github.com/jackc/pgx/v5/stdlib"
"github.com/pressly/goose/v3"
dbembed "github.com/missionworkforce/missionmatch/db"
)
// dbNameRe constrains the tenant database name to a safe identifier (CREATE DATABASE cannot be
// parameterized, so the name must be validated, never interpolated from untrusted input).
var dbNameRe = regexp.MustCompile(`^[a-z][a-z0-9_]{2,62}$`)
func main() {
adminDSN := flag.String("admin-dsn", "", "superuser DSN to the maintenance database (CREATE DATABASE + roles), defaults to MM_ADMIN_DSN")
migratorDSN := flag.String("migrator-dsn", "", "migrator DSN to the maintenance database (the migrations run as the migrator so it OWNS the tables), defaults to MM_MIGRATOR_DATABASE_URL")
name := flag.String("name", "", "tenant database name (lowercase identifier, e.g. tenant_acme)")
dropFirst := flag.Bool("drop-first", false, "DANGER: drop the database first (staging only)")
flag.Parse()
if *adminDSN == "" {
*adminDSN = os.Getenv("MM_ADMIN_DSN")
}
if *migratorDSN == "" {
*migratorDSN = os.Getenv("MM_MIGRATOR_DATABASE_URL")
}
if *adminDSN == "" || *name == "" {
log.Fatal("provision-tenant: -admin-dsn and -name are required")
}
if *migratorDSN == "" {
log.Fatal("provision-tenant: -migrator-dsn (or MM_MIGRATOR_DATABASE_URL) is required so the migrator owns the tables")
}
if !dbNameRe.MatchString(*name) {
log.Fatalf("provision-tenant: %q is not a safe database name (want ^[a-z][a-z0-9_]{2,62}$)", *name)
}
ctx := context.Background()
if err := provision(ctx, *adminDSN, *migratorDSN, *name, *dropFirst); err != nil {
log.Fatalf("provision-tenant: %v", err)
}
log.Printf("provision-tenant: DONE. Tenant database %q is schema identical to the shared database, RLS forced, eeo segregated, vector(384) ready.", *name)
log.Printf("provision-tenant: next, run cmd/embed-backfill against this database once it has candidates.")
}
func provision(ctx context.Context, adminDSN, migratorDSN, name string, dropFirst bool) error {
admin, err := pgx.Connect(ctx, adminDSN)
if err != nil {
return fmt.Errorf("admin connect: %w", err)
}
defer admin.Close(ctx)
// 1. Ensure the cluster roles exist (idempotent DO guards in the bootstrap).
bootstrap, err := dbembed.Bootstrap.ReadFile("bootstrap/00_roles.sql")
if err != nil {
return err
}
if _, err := admin.Exec(ctx, string(bootstrap)); err != nil {
return fmt.Errorf("role bootstrap: %w", err)
}
// 2. Create the database (CREATE DATABASE cannot run inside a transaction or be parameterized; the
// name was validated above).
if dropFirst {
_, _ = admin.Exec(ctx, "DROP DATABASE IF EXISTS "+name)
}
var exists bool
_ = admin.QueryRow(ctx, "SELECT EXISTS(SELECT 1 FROM pg_database WHERE datname=$1)", name).Scan(&exists)
if exists {
return fmt.Errorf("database %q already exists (use -drop-first in staging to recreate)", name)
}
if _, err := admin.Exec(ctx, "CREATE DATABASE "+name); err != nil {
return fmt.Errorf("create database: %w", err)
}
log.Printf("provision-tenant: created database %q", name)
// 3. In the new database: extension, schema usage, and connect privilege scoped to this database.
tenantAdminDSN := swapDB(adminDSN, name)
tdb, err := pgx.Connect(ctx, tenantAdminDSN)
if err != nil {
return fmt.Errorf("tenant connect: %w", err)
}
defer tdb.Close(ctx)
for _, stmt := range []string{
"CREATE EXTENSION IF NOT EXISTS vector",
"GRANT USAGE ON SCHEMA public TO missionmatch_app, missionmatch_compliance",
// The migrations run as the migrator below, so the migrator OWNS every table (it can then write
// identity and run future migrations). It needs CREATE on the public schema to create them; it
// creates and owns the eeo schema itself in the eeo migration.
"GRANT CREATE, USAGE ON SCHEMA public TO missionmatch_migrator",
// CREATE on the database lets the migrator create the eeo schema in the eeo migration set.
"GRANT CREATE ON DATABASE " + name + " TO missionmatch_migrator",
"REVOKE CONNECT ON DATABASE " + name + " FROM PUBLIC",
"GRANT CONNECT ON DATABASE " + name + " TO missionmatch_app, missionmatch_compliance, missionmatch_migrator",
} {
if _, err := tdb.Exec(ctx, stmt); err != nil {
return fmt.Errorf("setup %q: %w", stmt, err)
}
}
// 4. Run both migration sets via goose AS THE MIGRATOR (not the admin), so the migrator owns the
// tables: the app and compliance roles get exactly the grants the migrations spell out, while the
// migrator can write identity rows and apply future migrations (migrate-all-tenants). The extension
// and the CREATE grants above were done as the admin because they need superuser.
migTenantDSN := swapDB(migratorDSN, name)
sqlDB, err := sql.Open("pgx", migTenantDSN)
if err != nil {
return err
}
defer func() { _ = sqlDB.Close() }()
sqlDB.SetConnMaxLifetime(5 * time.Minute)
if err := goose.SetDialect("postgres"); err != nil {
return err
}
goose.SetBaseFS(dbembed.Migrations)
goose.SetTableName("goose_db_version")
if err := goose.UpContext(ctx, sqlDB, "migrations"); err != nil {
return fmt.Errorf("public migrations: %w", err)
}
log.Printf("provision-tenant: applied public schema migrations")
goose.SetBaseFS(dbembed.EEOMigrations)
goose.SetTableName("goose_db_version_eeo")
if err := goose.UpContext(ctx, sqlDB, "migrations_eeo"); err != nil {
return fmt.Errorf("eeo migrations: %w", err)
}
log.Printf("provision-tenant: applied segregated eeo migrations")
return verify(ctx, tdb)
}
// verify asserts the isolation and AI invariants the architecture requires before a tenant is usable.
func verify(ctx context.Context, tdb *pgx.Conn) error {
checks := []struct {
name string
query string
want bool
}{
{"pgvector extension", "SELECT EXISTS(SELECT 1 FROM pg_extension WHERE extname='vector')", true},
{"candidates RLS forced", "SELECT relforcerowsecurity FROM pg_class WHERE relname='candidates'", true},
{"eeo schema present", "SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname='eeo')", true},
{"app has NO grant on eeo.candidate_demographics",
"SELECT NOT has_table_privilege('missionmatch_app','eeo.candidate_demographics','SELECT')", true},
{"vector(384) column on candidates",
"SELECT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='candidates' AND column_name='resume_embedding')", true},
}
for _, c := range checks {
var got bool
if err := tdb.QueryRow(ctx, c.query).Scan(&got); err != nil {
return fmt.Errorf("verify %s: %w", c.name, err)
}
if got != c.want {
return fmt.Errorf("verify FAILED: %s = %v, want %v", c.name, got, c.want)
}
log.Printf("provision-tenant: verified %s", c.name)
}
return nil
}
// swapDB replaces the database name in a DSN, so the admin DSN to the maintenance database is reused to
// reach the new tenant database.
func swapDB(dsn, name string) string {
cfg, err := pgx.ParseConfig(dsn)
if err != nil {
return dsn
}
cfg.Database = name
// Rebuild a key value DSN (pgx accepts both URL and key value forms).
out := fmt.Sprintf("host=%s port=%d user=%s dbname=%s", cfg.Host, cfg.Port, cfg.User, name)
if cfg.Password != "" {
out += " password=" + cfg.Password
}
if cfg.TLSConfig == nil {
out += " sslmode=disable"
}
return out
}