Spec 0002: PostgreSQL Flexible Server

Task

1. Save this spec to .sdd/specification/spec-0002-postgresql-flexible-server.md in the repo (create the .sdd/specification/ directory if it does not exist).
2. Implement all Terraform changes described below.
3. After completing all work, create a provenance record at .sdd/provenance/spec-0002-postgresql-flexible-server.provenance.md (create the .sdd/provenance/ directory if it does not exist). See the Provenance Record section for the required format.

Prerequisites

• Spec 0001 deployed and verified (two-node K3s cluster running, Key Vault exists at kv-kevinryan-io)
• Read ADR-017 (docs/adr/adr-017-managed-postgresql-shared-database.md) — the architectural decision this spec implements
• Read ADR-018 (docs/adr/adr-018-secret-management-keyvault-eso.md) — explains why credentials go into Key Vault

Context

ADR-017 mandates an Azure Database for PostgreSQL Flexible Server (B1ms, PostgreSQL 16) with private VNet access only. The server hosts umami_db and grafana_db. Credentials are stored in the existing Key Vault (kv-kevinryan-io) per ADR-018. This spec is purely Terraform — no K8s manifests.

Current state (read these files before making changes)

File	What it does
infra/main.tf	Root module — network, node1, node2, keyvault, registry, cloudflare, github-oidc
infra/variables.tf	Root variables
infra/outputs.tf	Root outputs
infra/modules/network/main.tf	VNet 10.0.0.0/16, subnet 10.0.1.0/24, two public IPs, NSG
infra/modules/network/outputs.tf	Includes vnet_id, vnet_name (added in Spec 0001)
infra/modules/keyvault/outputs.tf	key_vault_id, key_vault_uri, key_vault_name

Lesson from Spec 0001: avoid for_each with unknown values

The for_each over vm_principal_ids failed at plan time because the principal IDs were unknown. The fix was switching from list(string) to map(string) with static keys. Apply the same pattern anywhere for_each iterates over apply-time values.

Lesson from Spec 0001: RBAC propagation delay

Azure RBAC role assignments can take several minutes to propagate. The azurerm_key_vault_secret resource failed because the Secrets Officer role hadn’t propagated yet. For this spec, the Key Vault and Secrets Officer role already exist and are propagated — writing new secrets should work on first apply.

1. PostgreSQL Terraform module

Create infra/modules/postgresql/ with main.tf, variables.tf, outputs.tf, versions.tf.

Resources to create

Delegated subnet

• azurerm_subnet — name: snet-postgresql, address prefix: 10.0.2.0/28 (a /28 gives 16 addresses, more than enough for a single Flexible Server). Created inside the existing VNet (pass vnet_name and resource_group_name as variables).
• Delegation: Microsoft.DBforPostgreSQL/flexibleServers with actions Microsoft.Network/virtualNetworks/subnets/join/action.

Private DNS zone

• azurerm_private_dns_zone — name: privatelink.postgres.database.azure.com, resource group same as VNet.
• azurerm_private_dns_zone_virtual_network_link — link the DNS zone to the VNet so the K3s nodes can resolve the PostgreSQL FQDN.

Flexible Server

• azurerm_postgresql_flexible_server — specification from ADR-017:

Parameter	Value
Name	psql-kevinryan-io (or pass via variable)
Location	same as resource group
Resource group	same as VNet
SKU	B_Standard_B1ms
Storage MB	32768 (32 GB)
Auto-grow	enabled
PostgreSQL version	16
Backup retention days	7
Geo-redundant backup	false
High availability	not set (disabled)
Zone	1
Delegated subnet ID	the subnet created above
Private DNS zone ID	the DNS zone created above
Admin username	pgadmin
Admin password	passed as a variable (generated by root module)
Public network access	false

Databases

• azurerm_postgresql_flexible_server_database — umami_db, charset UTF8, collation en_US.utf8
• azurerm_postgresql_flexible_server_database — grafana_db, charset UTF8, collation en_US.utf8

Variables

• location, resource_group_name (standard)
• vnet_name — for creating the subnet inside the existing VNet
• vnet_id — for the private DNS zone VNet link
• server_name — PostgreSQL server name (default: psql-kevinryan-io)
• admin_username — default: pgadmin
• admin_password — sensitive string, generated by root module
• databases — list of database names to create (default: ["umami_db", "grafana_db"])

Outputs

• server_fqdn — the Flexible Server’s FQDN (e.g. psql-kevinryan-io.postgres.database.azure.com)
• server_id — the Flexible Server resource ID
• admin_username — echo back for use in Key Vault secrets
• database_names — list of created database names

2. Root main.tf additions

Add to root module (after the existing module.keyvault block and azurerm_key_vault_secret.k3s_token resource):

resource "random_password" "pg_admin_password" {
  length  = 32
  special = true
}

module "postgresql" {
  source              = "./modules/postgresql"
  location            = module.network.resource_group_location
  resource_group_name = module.network.resource_group_name
  vnet_name           = module.network.vnet_name
  vnet_id             = module.network.vnet_id
  admin_password      = random_password.pg_admin_password.result
}

resource "azurerm_key_vault_secret" "pg_admin_password" {
  name         = "pg-admin-password"
  value        = random_password.pg_admin_password.result
  key_vault_id = module.keyvault.key_vault_id
}

resource "azurerm_key_vault_secret" "pg_fqdn" {
  name         = "pg-fqdn"
  value        = module.postgresql.server_fqdn
  key_vault_id = module.keyvault.key_vault_id
}

resource "azurerm_key_vault_secret" "pg_admin_username" {
  name         = "pg-admin-username"
  value        = module.postgresql.admin_username
  key_vault_id = module.keyvault.key_vault_id
}

Key Vault secrets stored

Secret name	Value	Purpose
pg-admin-password	Random 32-char password	DB admin password
pg-fqdn	psql-kevinryan-io.postgres.database.azure.com	Server hostname
pg-admin-username	pgadmin	DB admin username

These three secrets are the building blocks ESO will use in Spec 0003 to construct connection strings for Umami and Grafana.

No changes to existing modules

The network module already exports vnet_id and vnet_name. The keyvault module already has the Secrets Officer role assigned. No changes needed to existing modules.

3. Root outputs additions

Add to infra/outputs.tf:

output "postgresql_fqdn" {
  description = "FQDN of the PostgreSQL Flexible Server"
  value       = module.postgresql.server_fqdn
}

4. Azure resource provider registration

The Microsoft.DBforPostgreSQL resource provider must be registered on the subscription. The root module has resource_provider_registrations = "none" in the azurerm provider config. If the provider is not already registered, terraform apply will fail.

Note this in the provenance record as a manual prerequisite. The operator should check with:

az provider show --namespace Microsoft.DBforPostgreSQL --query "registrationState" -o tsv

If not Registered, run:

az provider register --namespace Microsoft.DBforPostgreSQL

Manual steps (not performed by the agent)

After the code changes are merged:

1. Ensure Microsoft.DBforPostgreSQL provider is registered (see above)
2. terraform plan — expect ~6 new resources (subnet, DNS zone, DNS link, server, 2 databases) + 3 Key Vault secrets + 1 random_password
3. terraform apply — the Flexible Server takes 5-10 minutes to provision
4. Verify DNS from node1: az vm run-command invoke --resource-group rg-kevinryan-io --name vm-kevinryan-node1 --command-id RunShellScript --scripts "nslookup psql-kevinryan-io.postgres.database.azure.com"
5. Verify Key Vault secrets: az keyvault secret list --vault-name kv-kevinryan-io --query "[].name" -o tsv — should show pg-admin-password, pg-fqdn, pg-admin-username alongside existing secrets
6. Verify connectivity from node1: az vm run-command invoke --resource-group rg-kevinryan-io --name vm-kevinryan-node1 --command-id RunShellScript --scripts "apt-get install -y postgresql-client && PGPASSWORD=\$(az keyvault secret show --vault-name kv-kevinryan-io --name pg-admin-password --query value -o tsv) psql -h psql-kevinryan-io.postgres.database.azure.com -U pgadmin -d umami_db -c 'SELECT 1'"

Provenance Record

After completing the work, create .sdd/provenance/spec-0002-postgresql-flexible-server.provenance.md with the following structure:

# Provenance: Spec 0002 — PostgreSQL Flexible Server

**Spec:** `.sdd/specification/spec-0002-postgresql-flexible-server.md`
**Executed:** <timestamp>
**Agent:** <agent identifier if available>

## Actions Taken

Chronological list of every action performed (files created, files modified, commands run).

## Decisions Made

Any decisions the agent made during execution that were not explicitly specified in the spec. For each:

| Decision | Options Considered | Chosen | Rationale |
|----------|-------------------|--------|-----------|
| ... | ... | ... | ... |

If no autonomous decisions were required, state: "No autonomous decisions were required — all actions were explicitly specified in the spec."

## Deviations from Spec

Any points where the agent deviated from the spec, and why. If none, state: "No deviations from spec."

## Artifacts Produced

| File | Status |
|------|--------|
| ... | Created / Modified |

## Validation Results

Results of each validation step from the spec (pass/fail with details).

Validation steps

After completing all work, confirm:

1. This spec has been saved to .sdd/specification/spec-0002-postgresql-flexible-server.md
2. infra/modules/postgresql/ exists with main.tf, variables.tf, outputs.tf, versions.tf
3. The PostgreSQL module creates a delegated subnet snet-postgresql with prefix 10.0.2.0/28 and delegation to Microsoft.DBforPostgreSQL/flexibleServers
4. The PostgreSQL module creates a private DNS zone privatelink.postgres.database.azure.com linked to the VNet
5. The Flexible Server resource uses SKU B_Standard_B1ms, PostgreSQL version 16, 32 GB storage with autogrow, 7-day backup retention
6. Public network access is disabled on the Flexible Server
7. Two databases are created: umami_db and grafana_db
8. infra/main.tf contains random_password.pg_admin_password and three azurerm_key_vault_secret resources (pg-admin-password, pg-fqdn, pg-admin-username)
9. infra/main.tf wires the postgresql module with correct variables from network and keyvault modules
10. No for_each over unknown values — verify all for_each uses static keys
11. terraform fmt -check -recursive infra/ passes
12. terraform validate passes in the infra/ directory (if terraform is available)
13. pnpm lint passes (no site code changed, but confirm no regressions)
14. The provenance record exists at .sdd/provenance/spec-0002-postgresql-flexible-server.provenance.md and contains all required sections
15. All files (spec, infrastructure changes, provenance) are committed together