Phase 29 — ASK PostgreSQL Clusters (CNPG)

Deploy three production-grade PostgreSQL clusters via CNPG as an App of Apps under the ask ArgoCD group. Each cluster has its own superuser and per-service roles backed by Vault-managed credentials distributed via ESO ExternalSecret.

Prerequisites: CNPG operator (Phase 8), Vault HA + VSO (Phase 16–17), ESO (Phase 19), and Harbor pull-secret injection all running and healthy.

---

ArgoCD Hierarchy

mod-auh1-dev-ask                                    ← root
└── mod-auh1-dev-ask.ask                            ← ask group (watches ask/ directory)
    └── mod-auh1-dev-ask.ask.postgresql             ← App of Apps (watches ask-db/apps/)
        ├── mod-auh1-dev-ask.ask.postgresql.core
        ├── mod-auh1-dev-ask.ask.postgresql.hatchet
        └── mod-auh1-dev-ask.ask.postgresql.knowledge

---

Cluster Summary

ArgoCD App	CNPG Cluster	Managed Roles (DB users)	Instances	Data PVC	WAL PVC	CPU req/lim	Mem req/lim
...postgresql.core	core-pg	core-service, zitadel, assistant-service	3	50 Gi × 3	5 Gi × 3	500m / 2	1Gi / 2Gi
...postgresql.hatchet	hatchet-pg	hatchet	3	50 Gi × 3	4 Gi × 3	250m / 1	512Mi / 1Gi
...postgresql.knowledge	knowledge-pg	knowledge-management-service	3	150 Gi × 3	5 Gi × 3	500m / 2	1Gi / 2Gi

Total storage: 750 Gi data + 42 Gi WAL ≈ 792 Gi on csi-rbd-sc

Component	Value
PostgreSQL image	harbor.cl1.sq4.aegis.internal/ghcr/cloudnative-pg/postgresql:16.8-bookworm
CNPG operator	1.28.1 (chart 0.27.1)
Namespace	ask
StorageClass	csi-rbd-sc (Ceph RBD, reclaimPolicy: Delete)

Each cluster exposes:

Service	Purpose
<cluster>-rw	Primary — port 5432, all writes
<cluster>-ro	Replicas — read-only queries

---

Why Separate ArgoCD Application

Concern	Reason
Independent lifecycle	DB upgraded/patched without touching the app
Survive app redeploy	Deleting ASK app does not delete the database
Separate RBAC	DBA access to DB ops without app deploy permissions
Sync order clarity	ask-postgresql must be Healthy before ask-core-service syncs

---

File Structure in mod-ask-devops

applications/ask-db/
├── apps/                                     ← App of Apps watches this
│   ├── core-pg-app.yaml
│   ├── hatchet-pg-app.yaml
│   └── knowledge-pg-app.yaml
├── core/
│   ├── core-pg-cluster.yaml                  50 Gi data + 5 Gi WAL, 3 managed roles
│   └── core-pg-credentials.yaml             4 × ExternalSecret (superuser + 3 app roles)
├── hatchet/
│   ├── hatchet-pg-cluster.yaml              50 Gi data + 4 Gi WAL, 1 managed role
│   └── hatchet-pg-credentials.yaml          2 × ExternalSecret
└── knowledge/
    ├── knowledge-pg-cluster.yaml            150 Gi data + 5 Gi WAL, 1 managed role + vector
    └── knowledge-pg-credentials.yaml        2 × ExternalSecret

applications/cluster-addons/
├── external-secrets/manifests/
│   ├── clustergenerator-password-alphanumeric-24.yaml   (kept; unused until ESO bug fixed)
│   ├── clustergenerator-password-alphanumeric-32.yaml
│   └── clustergenerator-password-complex-safe-16.yaml
└── vault/manifests/vault-bootstrap-config.yaml          adds eso-postgres-push policy

environment/auh1/dev/02-argocd-application/ask/
└── argocd-application-postgresql.yaml       App of Apps parent

---

Key Manifests

CNPG Cluster CR — core-pg (full spec)

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: core-pg
  namespace: ask
  annotations:
    argocd.argoproj.io/sync-wave: "2"
spec:
  instances: 3
  imageName: harbor.cl1.sq4.aegis.internal/ghcr/cloudnative-pg/postgresql:16.8-bookworm
  imagePullSecrets:
    - name: harbor-pull-secret

  enableSuperuserAccess: true
  superuserSecret:
    name: core-pg-superuser           # ← ExternalSecret creates this K8s Secret

  managed:
    roles:
      - name: core-service            # ← PostgreSQL ROLE, not a database
        ensure: present
        login: true
        passwordSecret:
          name: core-service-db-password
      - name: zitadel
        ensure: present
        login: true
        passwordSecret:
          name: zitadel-db-password
      - name: assistant-service
        ensure: present
        login: true
        passwordSecret:
          name: assistant-service-db-password

  bootstrap:
    initdb:
      postInitTemplateSQL:
        # Created in template1 → inherited by every database on this cluster.
        # Service init jobs do NOT need to run CREATE EXTENSION.
        - CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
        - CREATE EXTENSION IF NOT EXISTS "pgcrypto";
        - CREATE EXTENSION IF NOT EXISTS "pg_trgm";
        - CREATE EXTENSION IF NOT EXISTS "unaccent";

  storage:
    size: 50Gi
    storageClass: csi-rbd-sc
  walStorage:
    size: 5Gi
    storageClass: csi-rbd-sc

  resources:
    requests:
      cpu: 500m
      memory: 1Gi
    limits:
      cpu: "2"
      memory: 2Gi

  affinity:
    enablePodAntiAffinity: true
    topologyKey: kubernetes.io/hostname
  primaryUpdateStrategy: unsupervised
  primaryUpdateMethod: switchover

PostgreSQL extensions per cluster

Extensions are created in template1 so every database on the cluster inherits them.

Cluster	Extensions
core-pg	uuid-ossp, pgcrypto, pg_trgm, unaccent
hatchet-pg	uuid-ossp, pgcrypto
knowledge-pg	uuid-ossp, pgcrypto, pg_trgm, unaccent, vector (pgvector — built into 16.8-bookworm)

CNPG Cluster CR — knowledge-pg (adds vector extension)

# Only the differences from core-pg are shown
spec:
  instances: 3
  managed:
    roles:
      - name: knowledge-management-service
        ensure: present
        login: true
        passwordSecret:
          name: knowledge-management-service-db-password
  bootstrap:
    initdb:
      postInitTemplateSQL:
        - CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
        - CREATE EXTENSION IF NOT EXISTS "pgcrypto";
        - CREATE EXTENSION IF NOT EXISTS "pg_trgm";
        - CREATE EXTENSION IF NOT EXISTS "unaccent";
        - CREATE EXTENSION IF NOT EXISTS "vector";   # pgvector — built into 16.8-bookworm
  storage:
    size: 150Gi
    storageClass: csi-rbd-sc
  walStorage:
    size: 5Gi
    storageClass: csi-rbd-sc
  resources:
    requests:
      cpu: 500m
      memory: 1Gi
    limits:
      cpu: "2"
      memory: 2Gi

ExternalSecret pattern (superuser)

apiVersion: external-secrets.io/v1beta1
kind: ExternalSecret
metadata:
  name: core-pg-superuser
  namespace: ask
  annotations:
    argocd.argoproj.io/sync-wave: "0"     # must be ready before Cluster at wave 2
spec:
  refreshInterval: 1h
  secretStoreRef:
    name: vault-backend
    kind: ClusterSecretStore
  target:
    name: core-pg-superuser
    creationPolicy: Owner
    template:
      data:
        username: "postgres"
        password: "{{ .password }}"
  data:
    - secretKey: password
      remoteRef:
        key: ask/postgres/core-pg--SUPERUSER--PASSWORD
        property: password

ExternalSecret pattern (managed role password)

CNPG 1.28.x requires username key in managed role secrets

spec.managed.roles[].passwordSecret must contain both username (the role name) and password. Omitting username causes a continuous reconcile error: "username key doesn't exist inside the secret" — roles are never created.

apiVersion: external-secrets.io/v1beta1
kind: ExternalSecret
metadata:
  name: core-service-db-password
  namespace: ask
  annotations:
    argocd.argoproj.io/sync-wave: "0"
spec:
  refreshInterval: 1h
  secretStoreRef:
    name: vault-backend
    kind: ClusterSecretStore
  target:
    name: core-service-db-password
    creationPolicy: Owner
    template:
      data:
        username: "core-service"    # ← required by CNPG 1.28.x — must match spec.managed.roles[].name
        password: "{{ .password }}"
  data:
    - secretKey: password
      remoteRef:
        key: ask/postgres/core-service--DATABASE--PASSWORD
        property: password

---

Vault-Backed Credentials

Each service gets its own PostgreSQL role and password. Passwords are generated once and stored in Vault KV v2 at secret/ask/postgres/. ESO ExternalSecret (refreshInterval 1h) reads them back into K8s Secrets that CNPG consumes via spec.managed.roles and spec.superuserSecret.

PushSecret + generatorRef disabled (ESO 0.14.x bug)

The original design used ESO PushSecret with selector.generatorRef to auto-generate and push passwords to Vault. This hits a bug in the ESO 0.14.x Vault provider: the generated password bytes are accidentally used as the Vault HTTP response body, causing infinite "error unmarshalling vault secret: invalid character 'X'" loops. PushSecrets are removed from the repo. Passwords are seeded manually (see Seed Vault step below) and rotation is manual. See troubleshooting → ESO PushSecret generatorRef Vault Unmarshal Loop.

Flow

Admin (one-time, using vault-0 exec)
  │  vault kv put -mount=secret ask/postgres/<cluster>--SUPERUSER--PASSWORD  password=<24-char>
  │  vault kv put -mount=secret ask/postgres/<service>--DATABASE--PASSWORD   password=<24-char>
  ▼
Vault KV v2  (secret/ask/postgres/*)
  │
  │  ESO ExternalSecret  (sync-wave: 0, refreshInterval: 1h)
  ▼
K8s Secrets in namespace: ask
  ├── core-pg-superuser            { username: postgres, password: ... }
  ├── core-service-db-password     { password: ... }
  ├── zitadel-db-password          { password: ... }
  ├── assistant-service-db-password  { password: ... }
  ├── hatchet-pg-superuser         { username: postgres, password: ... }
  ├── hatchet-db-password          { password: ... }
  ├── knowledge-pg-superuser       { username: postgres, password: ... }
  └── knowledge-management-service-db-password  { password: ... }
  │
  │  CNPG Cluster CR  (sync-wave: 2)
  ▼
PostgreSQL roles: core-service, zitadel, assistant-service, hatchet, knowledge-management-service
  │
  │  Service Helm chart's job-postgres-init (runs as superuser)
  ▼
Databases: core_service_db, zitadel_db, etc. — with GRANT to each role

Why per-service roles instead of a single app user? Principle of least privilege. Each service can only access its own database. A compromise of core-service credentials does not expose the hatchet or zitadel database.

Why managed.roles? CNPG continuously reconciles each role. When the K8s secret changes, CNPG issues ALTER ROLE <name> PASSWORD '...' automatically — no cluster restart.

Who creates the databases? The service Helm chart's job-postgres-init runs as superuser and issues CREATE DATABASE + GRANT. CNPG manages only users.

Vault paths

Vault KV path	Field	K8s Secret	Consumer
secret/ask/postgres/core-pg--SUPERUSER--PASSWORD	password	core-pg-superuser	spec.superuserSecret
secret/ask/postgres/core-service--DATABASE--PASSWORD	password	core-service-db-password	spec.managed.roles
secret/ask/postgres/zitadel--DATABASE--PASSWORD	password	zitadel-db-password	spec.managed.roles
secret/ask/postgres/assistant-service--DATABASE--PASSWORD	password	assistant-service-db-password	spec.managed.roles
secret/ask/postgres/hatchet-pg--SUPERUSER--PASSWORD	password	hatchet-pg-superuser	spec.superuserSecret
secret/ask/postgres/hatchet--DATABASE--PASSWORD	password	hatchet-db-password	spec.managed.roles
secret/ask/postgres/knowledge-pg--SUPERUSER--PASSWORD	password	knowledge-pg-superuser	spec.superuserSecret
secret/ask/postgres/knowledge-management-service--DATABASE--PASSWORD	password	knowledge-management-service-db-password	spec.managed.roles

All 8 paths must be seeded manually before the first ArgoCD sync.

Vault policy — eso-postgres-push

The eso Vault Kubernetes auth role has two policies:

Policy	Capability	Path
eso-read	read, list	secret/data/*, secret/metadata/*
eso-postgres-push	create, update, read	secret/data/ask/postgres/*

eso-postgres-push is applied by the vault-bootstrap-config.yaml PostSync Job:

vault policy write eso-postgres-push - << 'POLICY'
path "secret/data/ask/postgres/*"     { capabilities = ["create", "update", "read"] }
path "secret/metadata/ask/postgres/*" { capabilities = ["read", "list"] }
POLICY

vault write auth/kubernetes/role/eso \
  bound_service_account_names=external-secrets \
  bound_service_account_namespaces=external-secrets \
  policies=eso-read,eso-postgres-push \
  ttl=1h

---

Deploy Runbook

Step 1 — Seed Vault (once per environment)

Run before any ArgoCD sync. Uses vault-bootstrap-token (the root-equivalent Vault token stored as a K8s Secret during initial cluster bootstrap):

BOOT_TOKEN=$(kubectl get secret -n vault vault-bootstrap-token \
  -o jsonpath='{.data.token}' | base64 -d)

kubectl exec -n vault vault-0 -- sh -c "
export VAULT_ADDR=https://127.0.0.1:8200
export VAULT_SKIP_VERIFY=true
export VAULT_TOKEN='$BOOT_TOKEN'

gen() { cat /dev/urandom | tr -dc 'a-zA-Z0-9' | head -c 24; }

vault kv put -mount=secret ask/postgres/core-pg--SUPERUSER--PASSWORD           password=\$(gen) && echo OK
vault kv put -mount=secret ask/postgres/core-service--DATABASE--PASSWORD        password=\$(gen) && echo OK
vault kv put -mount=secret ask/postgres/zitadel--DATABASE--PASSWORD             password=\$(gen) && echo OK
vault kv put -mount=secret ask/postgres/assistant-service--DATABASE--PASSWORD   password=\$(gen) && echo OK
vault kv put -mount=secret ask/postgres/hatchet-pg--SUPERUSER--PASSWORD         password=\$(gen) && echo OK
vault kv put -mount=secret ask/postgres/hatchet--DATABASE--PASSWORD             password=\$(gen) && echo OK
vault kv put -mount=secret ask/postgres/knowledge-pg--SUPERUSER--PASSWORD       password=\$(gen) && echo OK
vault kv put -mount=secret ask/postgres/knowledge-management-service--DATABASE--PASSWORD password=\$(gen) && echo OK

vault kv list -mount=secret ask/postgres/
"

Expected: 8 OK lines followed by 8 key names in the list.

Step 2 — Clean up existing clusters (re-deploy only)

Skip on a fresh cluster. Required when re-initialising CNPG clusters that have existing data PVCs:

kubectl delete cluster core-pg hatchet-pg knowledge-pg -n ask
kubectl get pods -n ask -w   # wait for all cluster pods to terminate

# csi-rbd-sc has reclaimPolicy=Delete — PVCs auto-delete when cluster is deleted.
# If they did not auto-delete:
kubectl delete pvc -n ask -l cnpg.io/cluster=core-pg
kubectl delete pvc -n ask -l cnpg.io/cluster=hatchet-pg
kubectl delete pvc -n ask -l cnpg.io/cluster=knowledge-pg

kubectl get pvc -n ask   # expected: No resources found

Step 3 — Commit and push

cd /path/to/mod-ask-devops
git add applications/ask-db/ \
        applications/cluster-addons/external-secrets/manifests/ \
        applications/cluster-addons/vault/manifests/vault-bootstrap-config.yaml
git commit -m "feat: CNPG clusters — per-service credentials, managed.roles, postInitTemplateSQL extensions"
git push

Step 4 — Sync supporting infra

# Apply eso-postgres-push Vault policy via PostSync bootstrap hook
argocd app sync mod-auh1-dev-ask.cluster-addons.vault --grpc-web --assumeYes

# Deploy ClusterGenerators (harmless; kept for future PushSecret fix upgrade)
argocd app sync mod-auh1-dev-ask.cluster-addons.external-secrets --grpc-web --assumeYes

Step 5 — Sync PostgreSQL App of Apps

# Create/update the App of Apps and all three child apps
argocd app sync mod-auh1-dev-ask.ask --grpc-web --assumeYes
argocd app sync mod-auh1-dev-ask.ask.postgresql --grpc-web --assumeYes

# Child apps sync in wave order:
#   wave 0 — ExternalSecrets → read from Vault → create K8s Secrets
#   wave 2 — CNPG Clusters → init with superuserSecret + managed.roles + extensions
argocd app sync mod-auh1-dev-ask.ask.postgresql.core \
                mod-auh1-dev-ask.ask.postgresql.hatchet \
                mod-auh1-dev-ask.ask.postgresql.knowledge \
                --grpc-web --assumeYes

Step 6 — Verify

# ExternalSecrets — all READY=True
kubectl get externalsecret -n ask

# Expected (8 rows, all READY=True, SYNCED with recent timestamp):
# core-pg-superuser            vault-backend   1h     True    InSync
# core-service-db-password     vault-backend   1h     True    InSync
# ...

# CNPG Clusters — all Healthy
kubectl get cluster -n ask
# Expected:
# NAME           AGE   INSTANCES   READY   STATUS                     PRIMARY
# core-pg        ...   3           3       Cluster in healthy state    core-pg-1
# hatchet-pg     ...   3           3       Cluster in healthy state    hatchet-pg-1
# knowledge-pg   ...   3           3       Cluster in healthy state    knowledge-pg-1

# Verify managed roles exist on core-pg
PRIMARY=$(kubectl get pod -n ask -l cnpg.io/cluster=core-pg,role=primary \
  -o jsonpath='{.items[0].metadata.name}')
kubectl exec -n ask ${PRIMARY} -- psql -U postgres -c "\du"
# Expected: rows for core-service, zitadel, assistant-service

# Verify extensions in template1
kubectl exec -n ask ${PRIMARY} -- psql -U postgres -d template1 -c "\dx"
# Expected: uuid-ossp, pgcrypto, pg_trgm, unaccent present

# knowledge-pg — verify vector extension
PRIMARY_KP=$(kubectl get pod -n ask -l cnpg.io/cluster=knowledge-pg,role=primary \
  -o jsonpath='{.items[0].metadata.name}')
kubectl exec -n ask ${PRIMARY_KP} -- psql -U postgres -d template1 -c "\dx" | grep vector

---

Password Rotation

Write a new value to Vault; ESO picks it up within refreshInterval (1h). Optionally force an immediate refresh:

BOOT_TOKEN=$(kubectl get secret -n vault vault-bootstrap-token \
  -o jsonpath='{.data.token}' | base64 -d)

kubectl exec -n vault vault-0 -- sh -c "
export VAULT_ADDR=https://127.0.0.1:8200
export VAULT_SKIP_VERIFY=true
export VAULT_TOKEN='$BOOT_TOKEN'
vault kv put -mount=secret ask/postgres/core-service--DATABASE--PASSWORD \
  password=\$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | head -c 24)
"

# Force immediate ESO refresh (optional)
kubectl annotate externalsecret core-service-db-password -n ask \
  force-sync=$(date +%s) --overwrite

# CNPG detects the K8s secret change and issues ALTER ROLE automatically
kubectl get cluster core-pg -n ask -o jsonpath='{.status.conditions}' | jq .

---

Storage Resize Runbook

CNPG does not auto-grow storage. Alert at 80% and resize manually — zero downtime via online PVC resize (requires csi-rbd-sc to support allowVolumeExpansion: true).

# Example: grow core-pg data PVCs from 50Gi to 100Gi
kubectl patch cluster core-pg -n ask \
  --type merge -p '{"spec":{"storage":{"size":"100Gi"}}}'

# Watch PVCs expand
kubectl get pvc -n ask -l cnpg.io/cluster=core-pg -w

Alert metric: cnpg_pg_database_size_bytes / cnpg_pg_volume_size_bytes > 0.8

---

Troubleshooting

Symptom	Cause	Fix
ExternalSecret SecretSyncedError	Vault path not seeded yet	Run Step 1 — Seed Vault
ExternalSecret No External Secrets	ESO token lacks eso-read policy	Re-sync vault app; restart ESO deployment
ImagePullBackOff on cluster pod	harbor-pull-secret not in ask namespace	Check ESO ClusterExternalSecret; verify harbor-pull-secret: "true" label on namespace
Cluster stuck Setting up primary	ExternalSecret not synced yet (wave race)	Wait for all ExternalSecrets READY=True, then re-sync cluster app
CNPG role password not updating	ESO refresh not fired yet	Annotate ExternalSecret with force-sync=$(date +%s)
app path does not exist on sync	Files not pushed to GitLab	git push first, then sync
PermissionDenied on argocd sync	Session expired	argocd login; sync parent mod-auh1-dev-ask.ask first
App stuck Deleting	resources-finalizer.argocd.io waiting	kubectl patch application <name> -n argocd --type json -p '[{"op":"remove","path":"/metadata/finalizers"}]'
postgresInit job could not connect	Cluster not ready when ASK app syncs	Ensure all 3 clusters Healthy before syncing ask-core-service
PushSecret error unmarshalling vault secret	ESO 0.14.x bug	See troubleshooting → ESO PushSecret generatorRef Vault Unmarshal Loop