Automating Tenant Database Provisioning with Terraform

Manual provisioning is fine for the first ten dedicated tenants and unmanageable by the hundredth, so this page turns it into a parameterised module invoked by the control plane. It is a focused part of Database-Per-Tenant Isolation: state layout, secrets, the registry handshake, and teardown that cannot be aimed at the wrong tenant.

Problem Framing

Provisioning a dedicated tenant is not one action but a sequence that must be atomic from the application's point of view: create the database, create a least-privilege role, apply every migration, register the placement, and only then let traffic route to it. A partially provisioned tenant — database exists, migrations not applied, registry already updated — is worse than no tenant at all, because requests will reach a schema-less database and fail in confusing ways.

Terraform is a good fit for the infrastructure half and a poor fit for the sequencing half. It excels at declaring the database, role, parameter group, backup policy and network rules as one reconcilable unit. It is not a workflow engine, so migrations and the registry handshake belong in the control plane, with Terraform invoked as one step.

The state layout is the decision that determines whether this scales. A single state file containing four thousand tenants becomes a multi-minute plan where one tenant's change risks every other. One state per tenant keeps plans small, blast radius tiny, and lets provisioning run concurrently.

Activation last is the whole safety property. Until the registry flips, a half-provisioned tenant is simply a database nobody can reach.

Step-by-Step Guide

1. Write one module, parameterised by tenant

# modules/tenant-database/main.tf
variable "tenant_id"  { type = string }
variable "tier"       { type = string }
variable "region"     { type = string }

locals {
  # Deterministic, collision-free, and readable in a console listing.
  db_name = "tenant_${replace(var.tenant_id, "-", "")}"
  sizes   = { standard = "db.t4g.medium", enterprise = "db.r6g.large" }
}

resource "aws_db_instance" "tenant" {
  identifier              = local.db_name
  engine                  = "postgres"
  engine_version          = "16.4"
  instance_class          = local.sizes[var.tier]
  allocated_storage       = 20
  max_allocated_storage   = 500              # storage autoscaling, not manual resizes
  storage_encrypted       = true
  kms_key_id              = aws_kms_key.tenant.arn
  backup_retention_period = 35
  deletion_protection     = true             # teardown must clear this explicitly
  skip_final_snapshot     = false
  final_snapshot_identifier = "${local.db_name}-final"
  availability_zone       = "${var.region}a"

  tags = { tenant_id = var.tenant_id, tier = var.tier, managed_by = "control-plane" }
}

deletion_protection = true is doing real work: it means an accidental terraform destroy against the wrong workspace fails instead of deleting a customer's database.

2. Keep one state file per tenant

# generated per tenant by the control plane
terraform {
  backend "s3" {
    bucket         = "tf-state-tenants"
    key            = "tenants/${TENANT_ID}/terraform.tfstate"
    region         = "eu-west-1"
    dynamodb_table = "tf-locks"
    encrypt        = true
  }
}

Per-tenant state keeps each plan to a handful of resources, allows concurrent provisioning without lock contention, and means a corrupted state file affects one customer rather than the fleet.

3. Generate credentials outside Terraform state

Terraform state records resource attributes in plaintext, so a password passed through it is a password stored in the state bucket forever. Let the secret manager generate it and give the database only the reference.

resource "aws_secretsmanager_secret" "tenant_db" {
  name       = "tenant/${var.tenant_id}/db"
  kms_key_id = aws_kms_key.tenant.arn
}

resource "aws_db_instance" "tenant" {
  # …
  manage_master_user_password   = true                                  # provider-managed rotation
  master_user_secret_kms_key_id = aws_kms_key.tenant.arn
  # No `password = ...` anywhere: nothing sensitive enters the state file.
}

4. Drive the sequence from the control plane

// provision.ts
export async function provisionTenant(tenantId: string, tier: Tier, region: string) {
  await setPlacement(tenantId, { status: 'provisioning' });          // not routable yet
  try {
    const tf = await terraform.apply({
      module: 'tenant-database',
      workspace: `tenant-${tenantId}`,
      vars: { tenant_id: tenantId, tier, region },
    });

    await migrate.toLatest({ cluster: tf.outputs.endpoint, database: tf.outputs.db_name });
    await smokeTest(tf.outputs.endpoint, tf.outputs.db_name, tenantId);

    await setPlacement(tenantId, {                                    // the activation step
      status: 'active', tier: 'siloed',
      clusterKey: tf.outputs.cluster_key, databaseName: tf.outputs.db_name, region,
    });
  } catch (err) {
    await setPlacement(tenantId, { status: 'failed', lastError: String(err) });
    throw err;                                                        // leave infra for inspection
  }
}

Deliberately not rolling back the infrastructure on failure is the right default: a failed provision usually needs to be looked at, and destroying the evidence makes diagnosis impossible. Reconcile abandoned resources with a scheduled sweep instead.

5. Make the smoke test prove isolation, not just connectivity

-- smoke.sql — run as the application role, never as the owner
SET ROLE tenant_app;
BEGIN;
SELECT set_config('app.current_tenant_id', :'tenant_id', true);
INSERT INTO health_probe (tenant_id, note) VALUES (:'tenant_id'::uuid, 'provision smoke');
SELECT count(*) = 1 AS own_row_visible FROM health_probe;
SELECT set_config('app.current_tenant_id', '00000000-0000-0000-0000-000000000000', true);
SELECT count(*) = 0 AS other_tenant_blind FROM health_probe;
ROLLBACK;

A smoke test that only opens a connection tells you the network works. This one tells you the migrations landed, the application role exists, and Row-Level Security is enforcing — which is the actual precondition for routing customer traffic.

Three properties, all decided by one choice. There is no scale at which the single state file becomes the better option.

Verification

# 1. Provisioning is idempotent — a second apply changes nothing.
terraform -chdir=envs/tenant-acme plan -detailed-exitcode
# exit 0 = no changes, 2 = drift. Anything but 0 after a successful provision is a defect.

# 2. No secret material in state.
terraform -chdir=envs/tenant-acme show -json \
  | jq -r '.. | strings' | grep -Ei 'password|secret' | grep -v 'arn:aws:secretsmanager' | head
# expected: no output

# 3. Deletion protection is on for every provisioned tenant.
aws rds describe-db-instances \
  --query 'DBInstances[?starts_with(DBInstanceIdentifier, `tenant_`) && DeletionProtection==`false`].DBInstanceIdentifier'
# expected: []

-- 4. Registry and reality agree: every active siloed tenant has a real database.
SELECT tenant_id, database_name FROM tenant_placement
WHERE  tier = 'siloed' AND database_name NOT IN (SELECT name FROM discovered_databases);
-- expected: no rows

Teardown is the operation worth over-engineering. Everything else here optimises for speed; this one optimises for being impossible to do by accident.

Failure Modes & Gotchas

Symptom: the wrong tenant's database is destroyed. Cause: a shared workspace and a mistyped variable. Fix: one workspace and one state file per tenant, deletion protection on by default, and a registry precondition on any destroy.
Symptom: credentials appear in the state bucket. Cause: a password passed as a Terraform variable. Fix: use provider-managed master passwords or generate in the secret manager; never let a secret transit state.
Symptom: traffic reaches a database with no tables. Cause: the registry is updated before migrations complete. Fix: activate last, and gate activation on a smoke test that proves the schema and Row-Level Security are in place.
Symptom: plans slow to a crawl as the fleet grows. Cause: all tenants share one state file. Fix: split state per tenant; the migration is mechanical with terraform state mv and pays for itself immediately.

FAQ

Should Terraform run the migrations too? No. Terraform reconciles declared state; migrations are an ordered, versioned sequence with their own failure semantics and their own tracking table. Wrapping them in a null_resource provisioner gives you the worst of both — no version tracking, no resume, and an apply that fails in a way Terraform cannot reason about. Keep migrations in the runner described in running migrations across thousands of tenant databases.

How long does provisioning a tenant realistically take? Five to fifteen minutes for a managed instance, dominated by the provider creating and making it available; migrations and the smoke test add seconds. Set customer expectations accordingly and make the tenant visible in a "provisioning" state rather than blocking a signup flow on it. If the tier needs sub-minute onboarding, that tenant belongs in a pooled or bridged tier until it grows.

What about the noisy pre-provisioning trick? Keeping a small warm pool of pre-created, migrated databases turns fifteen minutes into seconds and is worth it when dedicated tenants onboard frequently. The costs are real: idle instances accrue charges, and pooled databases drift behind the current schema version, so the warm pool has to be included in every fleet migration. Below a handful of dedicated signups a week, it is not worth the complexity.

How should provisioning failures be surfaced to the customer? As a state rather than an error. A tenant in a provisioning state with an estimated completion time is a reasonable thing to show; a spinner that never resolves is not, and an error message referring to infrastructure is worse. Expose three customer-visible states — provisioning, ready, and needs attention — and let the last one open a support conversation rather than a stack trace. Internally the state carries the last error and the workspace name so an engineer can go straight to the failed apply.

Does this work the same way outside a single cloud provider? The structure does, and only the module contents change. Per-tenant state, a control-plane-driven sequence, activation last and a smoke test that proves isolation are all provider-agnostic; what differs is the resource set and the mechanics of secret handling. Platforms running across two providers usually end up with one module per provider behind a common interface, which keeps the control plane unaware of which cloud a given tenant landed in — and that indifference is worth preserving, because it is what makes a later migration a module change rather than a rewrite.

How should the module handle provider version upgrades? Pin the provider version in the module and upgrade deliberately, because a provider release can change a resource's default and produce a plan that wants to modify every tenant database at once. Upgrading with a pinned version, running a plan against a single tenant workspace first, and reviewing that diff before rolling forward is the difference between a routine dependency bump and an unexpected fleet-wide change during a Friday deploy.