Terraform State Database¶

Overview¶

The Terraform State Database provides a PostgreSQL-based backend for storing Terraform state files using CloudNativePG. This eliminates the need for external state storage services and integrates seamlessly with the Skatzi platform's existing infrastructure.

Why PostgreSQL for Terraform State?¶

• State Locking: Native support prevents concurrent modifications
• Encryption at Rest: Data encrypted on Hetzner volumes
• ACID Compliance: Reliable transactions ensure state consistency
• Self-Hosted: No dependency on external services
• OpenBao Integration: Credentials managed centrally

Key Features¶

• CloudNativePG Operator: Automated PostgreSQL cluster management
• Persistent Storage: Dedicated 10GB Hetzner volume
• ExternalSecret Integration: Credentials synced from OpenBao
• High Availability Ready: Can be scaled to multiple instances
• Flux GitOps: Declarative deployment with dependency management

Architecture¶

┌─────────────────────────────────────────────────────────────┐
│                    Gitea Action Runner                      │
│                  (Terraform Execution)                      │
└────────────────────────┬────────────────────────────────────┘
                         │ postgres://
                         │ Connection String
                         ▼
┌─────────────────────────────────────────────────────────────┐
│            PostgreSQL Cluster (CloudNativePG)               │
│                terraform-state-db-rw.svc                    │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  Database: terraform_state                                  │
│  User: terraform_backend                                    │
│  Table: states                                              │
│    - id (SERIAL PRIMARY KEY)                                │
│    - name (TEXT UNIQUE)                                     │
│    - data (TEXT)                                            │
│                                                             │
└────────────────────────┬────────────────────────────────────┘
                         │
                         ▼
┌─────────────────────────────────────────────────────────────┐
│              Hetzner Cloud Volume (10GB)                    │
│                   Volume ID: 104482472                      │
│                Persistent Storage (Retain)                  │
└─────────────────────────────────────────────────────────────┘

                    Credentials Flow

┌──────────┐      ┌─────────────┐      ┌──────────────┐
│ OpenBao  │─────▶│ External    │─────▶│ Kubernetes   │
│          │      │ Secrets     │      │ Secret       │
│ secret/  │      │ Operator    │      │              │
│ hetzner- │      │             │      │ terraform-   │
│ mgmt/    │      │             │      │ state-db-    │
│ terraform│      │             │      │ credentials  │
│ -state-db│      │             │      │              │
└──────────┘      └─────────────┘      └──────────────┘

Components¶

1. CloudNativePG Operator¶

• Namespace: cnpg-system
• Purpose: Manages PostgreSQL cluster lifecycle
• Helm Chart: cloudnative-pg/cloudnative-pg v0.27.0

2. PostgreSQL Cluster¶

• Namespace: terraform-state
• Cluster Name: terraform-state-db
• Instances: 1 (scalable to 3 for HA)
• Storage: 10Gi on Hetzner volume
• Database: terraform_state
• Owner: terraform_backend

3. Persistent Storage¶

• PersistentVolume: terraform-state-db-pv
• Hetzner Volume ID: 104482472
• Size: 10GB
• Reclaim Policy: Retain (survives cluster deletion)
• Storage Class: hcloud-volumes

4. Credentials Management¶

• ExternalSecret: Syncs from OpenBao
• OpenBao Path: secret/hetzner-mgmt/terraform-state-db/credentials
• Kubernetes Secret: terraform-state-db-credentials
• Fields: username, password

Database Schema¶

The states table stores Terraform state files:

CREATE TABLE states (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    data TEXT
);

• id: Auto-incrementing primary key
• name: Unique identifier for the state (e.g., workspace name)
• data: JSON-serialized Terraform state
• Permissions: Full CRUD granted to terraform_backend user

Flux Kustomizations¶

The component is deployed via three Flux Kustomizations:

1. cloudnative-pg: Deploys the operator
2. terraform-state-db-secrets: Creates ExternalSecret
3. terraform-state-db: Deploys PostgreSQL cluster

Dependency Chain¶

cloudnative-pg (operator)
    ↓
terraform-state-db-secrets (credentials)
    ↓
terraform-state-db (PostgreSQL cluster)

Connection Details¶

Internal (from Kubernetes)¶

Host: terraform-state-db-rw.terraform-state.svc.cluster.local
Port: 5432
Database: terraform_state
User: terraform_backend

Connection String Format¶

postgres://terraform_backend:PASSWORD@terraform-state-db-rw.terraform-state.svc.cluster.local:5432/terraform_state?sslmode=disable

Usage in Terraform¶

Backend Configuration¶

terraform {
  backend "pg" {}
}

Gitea Actions Integration¶

steps:
  - name: Terraform Init
    run: terraform init
    env:
      TF_CLI_ARGS_init: "-backend-config=conn_str=${{ secrets.TF_STATE_CONN_STR }}"

Security¶

Authentication¶

• Database credentials stored in OpenBao
• Synced to Kubernetes via ExternalSecret
• Gitea Action Secrets for CI/CD pipelines

Network Security¶

• Traffic within cluster encrypted by Cilium
• No external access to database
• Accessible only from within Kubernetes cluster

Data Protection¶

• Encryption at rest on Hetzner volumes
• State locking prevents concurrent modifications
• Retain policy prevents accidental data loss

Monitoring¶

CloudNativePG provides built-in monitoring:

# Check cluster status
kubectl get cluster -n terraform-state

# View PostgreSQL logs
kubectl logs -n terraform-state terraform-state-db-1

# Monitor operator
kubectl logs -n cnpg-system deployment/cloudnative-pg

Quick Links¶

• Installation Guide - How the component was deployed
• Usage Guide - Managing Terraform state files

Resources¶

Troubleshooting¶

Connection Issues¶

# Test database connectivity from within cluster
kubectl run -it --rm debug --image=postgres:15 --restart=Never -- \
  psql "postgres://terraform_backend:PASSWORD@terraform-state-db-rw.terraform-state.svc.cluster.local:5432/terraform_state"

View Stored States¶

kubectl exec -n terraform-state terraform-state-db-1 -- \
  psql -U postgres -d terraform_state -c "SELECT name FROM states;"

Check Cluster Health¶

kubectl describe cluster terraform-state-db -n terraform-state
kubectl get pods -n terraform-state
kubectl logs -n terraform-state -l postgresql=terraform-state-db

kubectl patch pv terraform-state-db-pv -p '{"spec":{"claimRef":null}}'