TazLab K8s: tazlab-db Detail

Level 3 (Detail) — PostgreSQL cluster configuration, databases, users, backup.

Concept

The cluster runs a single shared PostgreSQL instance managed by Crunchy PostgreSQL Operator (PGO, 5.7.2). It serves every stateful workload in the lab: Mnemosyne and Hindsight (vector memory), Grafana (dashboards), pgAdmin, the TazLab test/validation databases, and Multica (Paperclip was removed on 2026-10-19).

It is on PostgreSQL 17.2 since 2026-09-27 (major upgrade from 16, §The 17 major upgrade).

PostgresCluster

File: infrastructure/instances/tazlab-db/cluster.yaml

Databases and Users

Six roles are declared in spec.users (verified live 2026-10-20):

Initialization SQL

File: infrastructure/instances/tazlab-db/init-sql.yaml

Applied as ConfigMap tazlab-db-init-sql. On first cluster creation, it:

mnemosyne database

• Creates vector and uuid-ossp extensions
• Creates memories table with UUID PK, VECTOR(3072) embedding column
• Creates archived_files table for session archival tracking

tazlab database

• Creates a memories table (different schema — serial PK, JSONB metadata/tags)

tazlab-test database

• Creates rebirth_test table
• Inserts a row with timestamp for rebirth validation

grafana database

• Grants all privileges to grafana user

S3 Backup

File: infrastructure/configs/tazlab-db/s3-external-secret.yaml

PGBackrest configuration:

Credentials are injected via ExternalSecret s3-backrest-creds which renders the PGBackrest config file format.

One-off backups are declared, not run by hand: the annotation postgres-operator.crunchydata.com/pgbackrest-backup in metadata makes PGO create the Job, and its value is a counter that must change for each new backup (PGO consumes it once). spec.backups.pgbackrest.manual selects the type (--type=full). Both were used for the 17 upgrade (pre-upgrade-pg17-20260927-0955, then post-upgrade-pg17-20260927-1014) — the second one because a major upgrade starts a new WAL timeline, which invalidates the differentials taken on the old one (live timeline after the upgrade: 3).

Within the maintenance window the schedules were commented out, not left running: with the instance scaled to zero a scheduled backup fails immediately instead of queueing, and a failed stanza is worse than a paused one. They were restored in the same commit that set postgresVersion: 17.

The 17 major upgrade (2026-09-27)

Executed with PGO’s declarative object — PGUpgrade plus shutdown: true plus the postgres-operator.crunchydata.com/allow-upgrade annotation in one commit, then removal of the CR, then the postgresVersion: 17 bump. The whole sequence is in Git, in order:

Measured cost: ~90 seconds of data work, ~6 minutes of total downtime; 80 pods healthy afterwards and every consumer re-checked with a real query (hindsight, paperclip, mnemosyne, grafana).

Two facts about the path, worth keeping because they cost time:

• spec.transferMethod does not exist in PGO 5.7.2’s CRD. It came from the 6.0.x documentation a research pass had cited, and the API server refused the object (field not declared in schema). The image tags were wrong the same way (17.2-1, not 17.2-0).
• There is no in-place rollback. A major upgrade does not go back; the revert path of the Longhorn volume does not exist on a bound PVC and the CSI snapshotter is not installable in this cluster. The accepted way back is restore from the pre-upgrade full backup — which is why that backup is a step in Git rather than a precaution. The operator accepted the irreversibility explicitly before the window opened.

Extensions after the upgrade

pgaudit is re-created at 17.0 in the databases that carry it, and vector is upgraded with ALTER EXTENSION vector UPDATE only — never DROP/CREATE, which destroys the columns and indexes that depend on it. Extension work is declared as one-shot Jobs (hindsight-db-extensions-job.yaml), because databaseInitSQL runs only at first initialisation.

Verified live 2026-09-28 — pgaudit 17.0 in postgres, mnemosyne, grafana, hindsight, tazlab, paperclip; vector 0.8.0 in mnemosyne and hindsight; pgaudit.log = none (preloaded, auditing not switched on). Exception: the multica database reports pgaudit 16.0, and so does template1 — a database created from template1 after the upgrade inherits the stale catalog row. See Known Issues.

Service exposure

File: infrastructure/instances/tazlab-db/tailnet-service.yaml

This is the cluster’s only external PostgreSQL path. The older MetalLB service (tazlab-db-external, 192.168.1.241) was disabled on 2026-05-23 and is no longer a resource in the repository — a page or a runbook that still points at 192.168.1.241 is reading an old revision. The NodePort is pinned because the Tailscale operator would otherwise allocate it dynamically: a Service recreation or a cluster rebuild could move the port silently, and out-of-cluster consumers (the TazPod containers, project paperclip-lxc-deployment) address it literally.

Users Secret Format

PGO generates a Secret for each user in the format <cluster-name>-pguser-<username> (e.g., tazlab-db-pguser-mnemosyne). Each Secret contains:

• user, password, host, port, dbname, uri, jdbc-uri

Applications reference these secrets via secretKeyRef in their env vars.

Server TLS (Vault PKI)

File: infrastructure/instances/tazlab-db/cluster.yaml (PostgresCluster spec)

Both the Postgres server and replication traffic use the same Vault PKI secret, provisioned via a VaultPKISecret resource that requests a server certificate from the Vault PKI engine. This replaces the default PGO-generated self-signed cert with a trusted CA-signed certificate.

PGO CA Trust Bundle

PGO’s operator-managed root CA and the Vault PKI intermediate CA are combined into a single trusted CA bundle. This ensures PostgreSQL accepts both:

• Certificates signed by the PGO internal CA (operator-internal communication)
• Certificates signed by the Vault PKI intermediate (client mTLS connections)

The bundle is injected into the PostgresCluster’s customTLSSecret configuration so the server presents the Vault-issued cert while trusting clients signed by either CA.

Client VaultPKISecrets

Six VaultPKISecret resources issue client certificates for authentication to the database (verified live 2026-09-28; notice that each lives in the namespace of its consumer, not in tazlab-db):

Each secret requests a client certificate from the Vault PKI intermediate, enabling certificate-based authentication (pg_hba cert method) for those users.

Application mTLS Authentication

Every application in the cluster authenticates with a client certificate. Paperclip was the one exception (password + md5); it was removed on 2026-10-19, and the reject-row pattern it demonstrated lives on in the multica row. The Vault Database Engine (database/) has been removed — no dynamic credentials are needed.

File: infrastructure/instances/tazlab-db/cluster.yaml (PostgresCluster patroni.dynamicConfiguration)

The live pg_hba block (28 rules, verified 2026-10-20) is order-significant: pg_hba is first-match-wins, so each cert rule that should be the only way in must be followed by a reject rule scoped to that user before the generic 10.0.0.0/8 / 10.244.0.0/16 md5 rows at the bottom. The rows are also (DATABASE, USER, ADDRESS) — a reject written as host pgadmin all would let the user fall through to the generic rules on every other database; it must read host postgres pgadmin.

hostssl grafana    grafana    0.0.0.0/0 cert        # then md5 fallbacks for grafana's own user (retained)
hostssl mnemosyne  mnemosyne  0.0.0.0/0 cert
hostssl hindsight  hindsight  0.0.0.0/0 cert
hostssl postgres   pgadmin    0.0.0.0/0 cert
host    postgres   pgadmin    0.0.0.0/0 reject
host    tazlab-admin all      0.0.0.0/0 md5
host    all        all        10.0.0.0/8     md5
host    all        all        10.244.0.0/16  md5

(IPv6 ::/0 rows mirror each 4-address rule; the listing above shows one family for readability.)

Grafana

Certificate: db-client-grafana-tls (CN=grafana). Mounted via extraInitContainer that copies from VaultPKISecret. grafana.ini.database sets ssl_mode: require, ca_cert_path, client_cert_path, client_key_path.

mnemosyne

Certificate: db-client-mnemosyne-tls (CN=mnemosyne). Go code refactored in 12-mnemosyne-mtls-refactor to support sslmode=verify-full. DB_PASS and md5 rules removed. Verified via pg_stat_ssl: ssl=t, client_dn=/CN=mnemosyne.

pgAdmin

Certificate: db-client-pgadmin-tls (CN=pgadmin). Pre-configured servers.json ConfigMap with cert paths pre-filled. rolloutRestartTargets added for cert rotation.

paperclip (password, deliberately)

hostssl paperclip paperclip … md5 plus user-scoped rejects. The application hands its connection URL to postgres.js and never builds an ssl option, so sslcert/sslkey/sslrootcert are ignored and a certificate cannot be presented. The rejects are what keep the role out of every other database. Its connection string uses sslmode=require against the pinned LAN NodePort 192.168.1.211:32227, and the server certificate carries only in-cluster names, so hostname verification is not available from there (TD-085).

Vault Database Engine — Removed

The database/ engine has been disabled (vault secrets disable database, 2026-07-09). All apps use mTLS — no dynamic credentials needed. TD-044 closed. The Terraform configuration remains in the repository for re-creation if needed.

Schema grants for late-added databases

databaseInitSQL (tazlab-db-init-sql) runs only at first cluster initialisation, so a database added afterwards does not get its schema ownership from it. The Flux-managed equivalent is a one-shot Job: multica-db-grants-job.yaml (GRANT ALL ON SCHEMA public + ALTER SCHEMA public OWNER TO multica, run as the postgres identity). The Job carries manualSelector: true — without it the API server injects controller-uid/job-name labels into spec.template on creation, the next server-side apply sees an immutable diff, and the whole instances Kustomization freezes until the Job is deleted by hand.

DAG Position

operators-data (Level 0, installs PGO)
→ configs (Level 2, creates s3-backrest-creds ExternalSecret)
→ instances (Level 3, creates PostgresCluster + services + grants Jobs)
→ apps-data (Level 3, mnemosyne-mcp depends on DB being ready)

Known Issues

• The memories table is created with VECTOR(3072) but gemini-embedding-001 produces 768-dim vectors (TD-002). This works because the init SQL runs only on first creation and the dimension mismatch is handled by the auto-detection fallback.
• Only 1 replica — no HA for the database. Acceptable for lab, inadequate for production.
• pgaudit catalog version is stale in multica and template1 (16.0 while the binaries are 17.0). Observed 2026-09-28: pg_available_extensions reports default 17.0, the other six databases report 17.0, the multica database and template1 report 16.0. The server is unaffected today (pgaudit.log = none, the module is loaded from the 17 binary), but a database created from template1 inherits the stale row and a dump/restore of multica would carry a version the 17 binaries do not offer. Remediation is ALTER EXTENSION pgaudit UPDATE in both — reported to the Cluster agent, not done by the wiki.
• A major upgrade has no in-place rollback (§The 17 major upgrade): the only way back is restoring the pre-upgrade full backup. Treat any future major bump as a one-way door.
• The Flux dependency stall observed during the upgrade (TD-096) is an operator-layer defect, not a database one: infrastructure-instances stays DependencyNotReady after every commit on tazlab-k8s even when the dependency is Ready, so instance changes need ordered forced reconciliations (vso → configs → instances → auth).

See Also

• Parent topics: Repository Mapping, Operators Inventory
• Sibling details: External Secrets Detail, Mnemosyne Deployment Detail
• Consumers: Multica Detail · Hindsight entity · Paperclip container detail
• Reference: ExternalSecret Example