PostgreSQL Provisioning

All PostgreSQL databases and roles in this homelab are managed by OpenTofu via the psql module (tf-modules/psql). This keeps database lifecycle management outside of Kubernetes — no init containers, no privileged DB access from pods.

Why OpenTofu?

Managing databases through IaC gives a single source of truth for what exists, prevents drift, and integrates naturally with secret generation. Credentials are generated at provision time and stored directly in Vault — apps pull them at runtime via ExternalSecrets.

Module design

The psql module (tf-modules/psql) wraps the cyrilgdn/postgresql provider and manages four resource types:

Resource	What it does
postgresql_role	Creates a login role with a generated or existing password
postgresql_database	Creates the database, sets owner
postgresql_grant	Grants CONNECT, CREATE, TEMPORARY on the database and USAGE, CREATE on the public schema
postgresql_default_privileges	Sets default privileges for future objects in the schema

Password lifecycle

create_password: truecreate_password: false

The module generates a random password using an OpenTofu ephemeral resource, writes it to Vault at apps/psql/<role>, and sets it on the role.

On subsequent tofu apply runs the password is not regenerated — increment password_wo_version to force a rotation.

The password already exists in Vault (set externally). The module reads it as an ephemeral secret and uses it to sync the role password without storing it in state.

Ephemeral resources

Both the random password generator and the Vault read use OpenTofu ephemeral resources. Ephemeral values are never written to state, so credentials never appear in tofu.tfstate.

Deployment (tf-deploy/psql)

tf-deploy/psql instantiates one psql module per app via for_each. The locals.tf file defines all roles and their databases in a single map:

locals {
  admin_role_db_mappings = {
    grafana = {
      create_password = true
      databases = {
        grafana = {}
      }
    }
    authentik = {
      create_password = true
      databases = {
        authentik = {}
      }
    }
    radarr = {
      create_password = true
      databases = {
        radarr-main = {}
        radarr-log  = {}
      }
    }
    # ... other apps
  }
}

The roles local expands each entry into the full module input, automatically building database_grants and schema_grants for every database.

Vault credential paths

Credentials are stored in the apps KV v2 mount under psql/<role>, with username and password properties. The admin credentials used by the provider itself live at apps/psql/datui.

ExternalSecret key prefix

The ClusterSecretStore is configured with mount apps, so ExternalSecret key values omit the mount prefix — key: psql/grafana resolves to apps/psql/grafana in Vault.

Managed databases

Role	Database(s)	Password source
grafana	grafana	Generated → Vault
authentik	authentik	Generated → Vault
n8n	n8n	Existing in Vault
mcmap	bluemap-mtb, bluemap-itt	Existing in Vault
vikunja	vikunja	Generated → Vault
radarr	radarr-main, radarr-log	Generated → Vault
sonarr	sonarr-main, sonarr-log	Generated → Vault
lidarr	lidarr-main, lidarr-log	Generated → Vault
prowlarr	prowlarr-main, prowlarr-log	Generated → Vault
tofu	tofu_backend	Existing in Vault

Adding a new database

1. Add an entry to admin_role_db_mappings in tofu/tf-deploy/psql/locals.tf:

   myapp = {
     create_password = true
     databases = {
       myapp = {}
     }
   }
   

2. Apply:

   cd tofu/tf-deploy/psql
   tofu init -backend-config=backend.pg.tfbackend
   tofu plan -out plan.out
   tofu apply plan.out
   

3. Credentials are written to Vault at apps/psql/myapp. Create an ExternalSecret in the app's namespace to consume them:

   apiVersion: external-secrets.io/v1
   kind: ExternalSecret
   metadata:
     name: myapp-db
   spec:
     secretStoreRef:
       name: vault
       kind: ClusterSecretStore
     target:
       name: myapp-db
     data:
     - secretKey: username
       remoteRef:
         key: psql/myapp
         property: username
     - secretKey: password
       remoteRef:
         key: psql/myapp
         property: password
   

Provider authentication

Warning

The postgresql provider reads admin credentials from Vault at plan time. Vault must be reachable for tofu plan to succeed.

data "vault_generic_secret" "psql_admin" {
  path = "apps/psql/datui"
}

provider "postgresql" {
  host     = local.psql.host
  port     = local.psql.port
  sslmode  = "disable"
  username = data.vault_generic_secret.psql_admin.data["username"]
  password = data.vault_generic_secret.psql_admin.data["password"]
}