401 lines
21 KiB
YAML
401 lines
21 KiB
YAML
---
|
|
# Nextcloud's PostgreSQL — a CloudNativePG Cluster (CR of the CNPG CRDs from platform/cnpg), modeled
|
|
# on platform/keycloak/postgres-cluster.yaml. This is the first CNPG-DB in apps/ and establishes the
|
|
# reusable pattern for later DB-backed services (semaphore, unifi, ...).
|
|
#
|
|
# WHY A DEDICATED `nextcloud-db` NAMESPACE (not co-located in `nextcloud`):
|
|
# The app->db edge in catalog/nextcloud.yaml (`dependencies: to: nextcloud-db`) compiles to a
|
|
# CiliumNetworkPolicy whose egress selector pins BOTH the destination pod label
|
|
# (app.kubernetes.io/name=nextcloud-db) AND its namespace (k8s:io.kubernetes.pod.namespace=
|
|
# nextcloud-db) — cross-namespace Cilium selectors must name the namespace (catalog/render.py
|
|
# r_dependency_policies). So the DB MUST live in a namespace literally named `nextcloud-db` and its
|
|
# pods MUST carry app.kubernetes.io/name=nextcloud-db, injected below via inheritedMetadata.
|
|
#
|
|
# DEFAULT-DENY POSTURE — mirrors keycloak (platform/keycloak/namespace.yaml): this namespace is
|
|
# INTENTIONALLY not homelab.keithjackson.dev/app-namespace=true, so Kyverno does NOT generate the app
|
|
# default-deny baseline here and CNPG runs under Cilium's cluster-wide default-allow — the SAME posture
|
|
# the verified keycloak-db uses. The nextcloud SIDE keeps its default-deny; its rendered egress-allow
|
|
# opens ONLY nextcloud -> nextcloud-db:5432, and the DB side's default-allow accepts that reciprocal
|
|
# ingress. No app-namespace policy (auth-mode/host-namespace/secret-provenance) is weakened.
|
|
apiVersion: v1
|
|
kind: Namespace
|
|
metadata:
|
|
name: nextcloud-db
|
|
labels:
|
|
# Match the Cilium egress selector's namespace pin; NOT an app-namespace (see header).
|
|
app.kubernetes.io/name: nextcloud-db
|
|
---
|
|
# Owner credentials for the CNPG cluster. SOPS-encrypted (age); type basic-auth so CNPG's initdb
|
|
# consumes it as the `nextcloud` role's password instead of generating a random one — this lets the
|
|
# SAME password be mirrored into ns nextcloud (nextcloud-bootstrap/db-password) for the app's env,
|
|
# since a Pod cannot read a Secret in another namespace. Provided as apps/nextcloud-db-app.sops.yaml.
|
|
#
|
|
# CNPG Cluster (CR of postgresql.cnpg.io installed by platform/cnpg).
|
|
apiVersion: postgresql.cnpg.io/v1
|
|
kind: Cluster
|
|
metadata:
|
|
name: nextcloud-db
|
|
namespace: nextcloud-db
|
|
spec:
|
|
# THREE instances — one primary + two hot standbys (2026-09-07), same as keycloak-db; see
|
|
# platform/keycloak/postgres-cluster.yaml for the full rationale. Short version: Longhorn's 3-way
|
|
# replication saves the DATA on node loss, not the SERVICE, and with `instances: 1` all three CNPG
|
|
# primaries had drifted onto k3s-prod-2, so one node took Nextcloud down alongside SSO and
|
|
# Semaphore. A streaming standby lets CNPG promote in seconds and repoint the -rw Service.
|
|
# Gated on the Barman archive below actually working (ContinuousArchiving=True with a real
|
|
# firstRecoverabilityPoint) — standbys replicate corruption, they are not a backup.
|
|
#
|
|
# Scaling out is non-disruptive: CNPG creates -2 then -3 sequentially, each seeded by
|
|
# pg_basebackup from the running primary, which is neither restarted nor disconnected.
|
|
instances: 3
|
|
imageName: ghcr.io/cloudnative-pg/postgresql:16.15
|
|
# Stamp app.kubernetes.io/name=nextcloud-db onto every generated pod/service so the rendered
|
|
# nextcloud egress CiliumNetworkPolicy's toEndpoints selector matches the primary pod (R-NET-02).
|
|
inheritedMetadata:
|
|
labels:
|
|
app.kubernetes.io/name: nextcloud-db
|
|
# HARD anti-affinity — the three instances MUST land on three DIFFERENT nodes; and roll the
|
|
# primary by SWITCHOVER rather than CNPG's default in-place `restart`, which would delete and
|
|
# recreate the primary pod. Full rationale in platform/keycloak/postgres-cluster.yaml. Short
|
|
# version: CNPG's default `preferred` is a scheduler hint, so when CNPG recreates an instance
|
|
# after a node loss it may stack two instances on one node — which is how `instances: 3` decays
|
|
# back into the single-node failure domain this change removes. `required` makes it a constraint;
|
|
# the cost is that an instance whose node is gone stays Pending, which is correct (2 of 3 still
|
|
# serve). Satisfiable on both clusters: prod and dev each have 3 schedulable, untainted nodes.
|
|
#
|
|
# APPLYING THIS CHANGES THE POD SPEC, so CNPG rolls the instances: standbys first, then one
|
|
# switchover. Expect a single brief connection reset for Nextcloud.
|
|
affinity:
|
|
enablePodAntiAffinity: true
|
|
podAntiAffinityType: required
|
|
primaryUpdateMethod: switchover
|
|
# SYNCHRONOUS REPLICATION — quorum of ONE standby, PREFERRED durability (2026-09-07). Full
|
|
# rationale in platform/keycloak/postgres-cluster.yaml. Short version: async replication loses the
|
|
# last few transactions when a primary dies before its standbys catch up, and `method: any` +
|
|
# `number: 1` makes COMMIT wait for one standby to confirm. dataDurability MUST stay `preferred`,
|
|
# never `required` — `required` blocks writes outright when fewer than `number` healthy standbys
|
|
# are available, which on a 3-node cluster sharing nodes with etcd and the CNI turns one degraded
|
|
# standby into a total write outage. `preferred` relaxes to async instead.
|
|
#
|
|
# NON-DISRUPTIVE: compiles to `synchronous_standby_names`, a reload-only PostgreSQL parameter —
|
|
# no restart, no switchover, no client disconnect. Land it after the anti-affinity roll.
|
|
postgresql:
|
|
synchronous:
|
|
method: any
|
|
number: 1
|
|
dataDurability: preferred
|
|
storage:
|
|
storageClass: longhorn
|
|
size: 5Gi
|
|
bootstrap:
|
|
initdb:
|
|
database: nextcloud
|
|
owner: nextcloud
|
|
# Use the pre-provisioned owner secret (basic-auth) so its password is known + reusable by the
|
|
# app in the other namespace, instead of the random CNPG-generated one.
|
|
secret:
|
|
name: nextcloud-db-app
|
|
# Emit Postgres metrics so Prometheus scrapes DB health (R-OBS-01). The CNPG operator creates the
|
|
# PodMonitor at runtime (CRD present from Phase-4 monitoring).
|
|
monitoring:
|
|
enablePodMonitor: true
|
|
resources:
|
|
requests:
|
|
cpu: 100m
|
|
memory: 256Mi
|
|
limits:
|
|
memory: 512Mi
|
|
# --- Off-cluster DR backup (R-STATE-03) — CNPG-native Barman → object storage ---
|
|
# Continuous WAL archiving + periodic base backups = proper Postgres PITR (realises the SD's
|
|
# backup.policy daily-offcluster). CNPG appends serverName (defaults to the Cluster name) to
|
|
# destinationPath, so objects land under ${CNPG_BACKUP_PATH}/nextcloud-db/ — dev
|
|
# s3://cnpg/nextcloud-db/ on the on-Proxmox MinIO, prod
|
|
# s3://k3s-prod-backup/barman/nextcloud-db/ on Cloudflare R2. Both endpointURL and
|
|
# destinationPath are cluster-vars knobs (R-ENV-02), substituted by `apps-wave-0`.
|
|
# Credentials: PER-ENVIRONMENT (2026-09-07). dev reads nextcloud-db-backup-credentials (the
|
|
# scoped MinIO key), prod reads nextcloud-db-backup-credentials-prod (the Cloudflare R2
|
|
# token). BOTH Secrets are applied to BOTH clusters by apps/wave-0/kustomization.yaml (a
|
|
# generated golden — render.py places the -prod sidecar in this DB's wave); the
|
|
# cluster-vars knob ${BACKUP_CREDENTIALS_SUFFIX} (dev "" | prod "-prod") chooses which
|
|
# one this Cluster reads. Both are in-namespace, which is all a pod can read. They were
|
|
# ONE shared Secret until the split, so giving prod its R2 token would have left dev
|
|
# signing MinIO requests with an R2 key id — 403 on every archive command, i.e. the
|
|
# disk-full clock below rather than a merely missing backup.
|
|
#
|
|
# archive_mode is ON, so a FAILING archive command is a disk-full clock, not a degraded backup:
|
|
# Postgres refuses to recycle a WAL segment it has not archived, and the pgdata PVC fills until
|
|
# the primary PANICs. Prod demonstrated this from bootstrap to 2026-09-07 with a placeholder
|
|
# endpoint. Treat ContinuousArchiving=False here as an availability incident.
|
|
backup:
|
|
retentionPolicy: "30d"
|
|
barmanObjectStore:
|
|
destinationPath: ${CNPG_BACKUP_PATH}
|
|
endpointURL: ${S3_BACKUP_ENDPOINT}
|
|
s3Credentials:
|
|
accessKeyId:
|
|
name: nextcloud-db-backup-credentials${BACKUP_CREDENTIALS_SUFFIX}
|
|
key: ACCESS_KEY_ID
|
|
secretAccessKey:
|
|
name: nextcloud-db-backup-credentials${BACKUP_CREDENTIALS_SUFFIX}
|
|
key: ACCESS_SECRET_KEY
|
|
wal:
|
|
compression: gzip
|
|
maxParallel: 2
|
|
data:
|
|
compression: gzip
|
|
---
|
|
# Daily base backup of nextcloud-db (WAL archived continuously by the barmanObjectStore above). CNPG
|
|
# cron is 6-field (sec min hour dom mon dow); immediate:true seeds the first base backup. 02:00 slot.
|
|
apiVersion: postgresql.cnpg.io/v1
|
|
kind: ScheduledBackup
|
|
metadata:
|
|
name: nextcloud-db-daily
|
|
namespace: nextcloud-db
|
|
spec:
|
|
immediate: true
|
|
schedule: "0 0 2 * * *"
|
|
backupOwnerReference: self
|
|
cluster:
|
|
name: nextcloud-db
|
|
method: barmanObjectStore
|
|
---
|
|
# =============================================================================
|
|
# CNPG METRICS-EXPORTER CONNECT RECONCILER (R-OBS-01)
|
|
#
|
|
# THE BUG THIS CLOSES (true positive, verified live on prod 2026-09-12, and the
|
|
# condition platform/cnpg/alerts.yaml already recorded on BOTH clusters 2026-09-06):
|
|
# the `nextcloud` database's ACL had been narrowed out-of-band so that PUBLIC held
|
|
# nothing, and CNPG's metrics exporter role therefore could not even CONNECT:
|
|
#
|
|
# nextcloud-db-1/-2/-3, every scrape:
|
|
# FATAL: permission denied for database "nextcloud" (SQLSTATE 42501)
|
|
# DETAIL: User does not have CONNECT privilege.
|
|
# msg="Error collecting user query" targetDatabase=nextcloud
|
|
#
|
|
# CONSEQUENCE: `cnpg_last_error` = 1 on all three instances and the ENTIRE
|
|
# cnpg_pg_stat_* / pg_settings / pg_postmaster family MISSING for this database,
|
|
# while the scrape target still reported up=1 — a blind spot that looks like health.
|
|
# Measured before the fix: cnpg_pg_settings_setting had 765 series for keycloak and
|
|
# 765 for semaphore-db and ZERO for nextcloud-db. Nextcloud's database had never
|
|
# been monitored.
|
|
#
|
|
# ROOT CAUSE, AND WHY ONLY THIS DATABASE. All three Cluster specs are identical in
|
|
# their bootstrap; the difference is entirely in the live ACL:
|
|
#
|
|
# nextcloud : datacl = {nextcloud=CTc/nextcloud} <-- explicit, no PUBLIC entry
|
|
# keycloak : datacl = NULL
|
|
# semaphore : datacl = NULL
|
|
#
|
|
# A NULL datacl means "PostgreSQL defaults", which include CONNECT and TEMP for
|
|
# PUBLIC — so keycloak and semaphore let the exporter in only by inheriting PUBLIC's
|
|
# implicit CONNECT. On `nextcloud` something ran the equivalent of
|
|
# `REVOKE ALL ON DATABASE nextcloud FROM PUBLIC`, which MATERIALISES datacl and
|
|
# drops the implicit PUBLIC entry with it. That REVOKE is not in this repo and not
|
|
# in DECISIONS-LOG.md — it was applied out of band, which is exactly why a
|
|
# git-declared fix is needed rather than another hand-run psql.
|
|
#
|
|
# WHY THE GRANT IS EXPLICIT RATHER THAN "RE-GRANT CONNECT TO PUBLIC": restoring
|
|
# PUBLIC's CONNECT would undo whatever hardening that REVOKE intended and would
|
|
# leave the exporter depending on an implicit default again — the precise fragility
|
|
# that broke here and that keycloak/semaphore are still relying on. Naming the
|
|
# exporter role makes the dependency explicit and survives a future REVOKE FROM
|
|
# PUBLIC on any of the three databases.
|
|
#
|
|
# WHY A CronJob AND NOT A CNPG FIELD — CNPG 1.30.0's API GENUINELY CANNOT EXPRESS
|
|
# THIS. Checked against the live CRDs on prod:
|
|
# * `Database` (databases.postgresql.cnpg.io) has no database-ACL field at all.
|
|
# `allowConnections` is the pg_database.datallowconn FLAG (open/closed to
|
|
# everyone), not a per-role GRANT; its only grant/revoke verbs are the `usage`
|
|
# lists on `fdws` and `servers`.
|
|
# * `spec.managed.roles` and `DatabaseRole` manage ROLE ATTRIBUTES (login,
|
|
# superuser, connectionLimit, ...) plus `inRoles` MEMBERSHIP. Membership is the
|
|
# wrong tool: the only local role holding CONNECT here is the OWNER, so
|
|
# `inRoles: [nextcloud]` would hand the exporter the owner's CREATE and TEMP on
|
|
# the database and read/write on every application table — the opposite of
|
|
# least privilege, to buy one CONNECT.
|
|
# * `bootstrap.initdb.postInitApplicationSQL` runs ONCE, at bootstrap. It cannot
|
|
# repair these three already-bootstrapped instances (the actual bug), and on a
|
|
# FRESH cluster the grant is unnecessary anyway (datacl starts NULL). Adding it
|
|
# would be a second mechanism that fixes nothing this one does not.
|
|
# So the SQL has to be applied by something, and this repo already has the pattern
|
|
# for state Flux cannot reach: the level-triggered CronJob reconciler in
|
|
# platform/keycloak/client-secret-reconciler.yaml, whose reasoning transfers
|
|
# verbatim — the drifted state lives inside PostgreSQL, not in a Kubernetes object,
|
|
# so no admission or mutation policy can see it. A one-shot Job is deliberately NOT
|
|
# used: a completed Job is never re-run, so a DB rebuild or a PITR restore would
|
|
# silently reopen the blind spot. A loop converges instead.
|
|
#
|
|
# PRIVILEGE GRANTED — EXACTLY ONE, AND NOTHING ELSE:
|
|
# GRANT CONNECT ON DATABASE nextcloud TO cnpg_metrics_exporter;
|
|
# Resulting ACL entry is `cnpg_metrics_exporter=c/nextcloud` — the `c` is CONNECT;
|
|
# there is no `C` (CREATE) and no `T` (TEMPORARY). No ownership, no superuser, no
|
|
# write, no table or schema grants. Nothing further is needed because CNPG already
|
|
# creates this role as a member of the built-in `pg_monitor` role (verified on all
|
|
# three prod clusters), and pg_monitor already carries the read access every
|
|
# collector query wants — they read pg_catalog views (pg_stat_database,
|
|
# pg_stat_archiver, pg_settings, pg_postmaster) and no application tables. Proof
|
|
# that CONNECT alone is sufficient: after this grant, `Error collecting user query`
|
|
# went to ZERO on all three pods, i.e. every collector query now succeeds.
|
|
#
|
|
# IT RUNS AS THE DATABASE OWNER, NOT AS A SUPERUSER. `enableSuperuserAccess` is off
|
|
# on this Cluster (there is no nextcloud-db-superuser Secret), and it stays off: the
|
|
# owner of a database holds the grant option on that database's privileges, so
|
|
# `nextcloud` can issue this GRANT itself. Verified live before committing, by
|
|
# running the statement under `SET ROLE nextcloud`. Credentials come from the
|
|
# in-namespace SOPS Secret nextcloud-db-app that already exists for the app.
|
|
#
|
|
# NETWORKING: nextcloud-db is intentionally NOT an app-namespace (see this file's
|
|
# header), so Kyverno generates no default-deny baseline here and this same-namespace
|
|
# pod -> nextcloud-db-rw:5432 connection needs no rendered allow.
|
|
#
|
|
# THE POD LABEL IS DELIBERATELY *NOT* app.kubernetes.io/name=nextcloud-db. That
|
|
# label is what `inheritedMetadata` stamps on the CNPG instance pods and what the
|
|
# rendered nextcloud egress CiliumNetworkPolicy selects; reusing it here would quietly
|
|
# make this reconciler a permitted destination for the Nextcloud app. It gets its own
|
|
# name so that selector keeps meaning "the database".
|
|
apiVersion: batch/v1
|
|
kind: CronJob
|
|
metadata:
|
|
name: nextcloud-db-exporter-grant
|
|
namespace: nextcloud-db
|
|
labels:
|
|
app.kubernetes.io/name: nextcloud-db-exporter-grant
|
|
app.kubernetes.io/part-of: nextcloud-db
|
|
spec:
|
|
# Every 15 minutes, matching the keycloak reconciler's cadence. A converged
|
|
# database makes this one idempotent GRANT and exits; the cost is negligible, and
|
|
# after a rebuild or PITR restore monitoring is blind for at most one interval
|
|
# (well inside the alert's `for: 6h`).
|
|
schedule: "*/15 * * * *"
|
|
# A run still waiting on the primary must not be lapped by the next tick.
|
|
concurrencyPolicy: Forbid
|
|
startingDeadlineSeconds: 300
|
|
successfulJobsHistoryLimit: 1
|
|
failedJobsHistoryLimit: 3
|
|
jobTemplate:
|
|
spec:
|
|
backoffLimit: 2
|
|
activeDeadlineSeconds: 300
|
|
ttlSecondsAfterFinished: 3600
|
|
template:
|
|
metadata:
|
|
labels:
|
|
app.kubernetes.io/name: nextcloud-db-exporter-grant
|
|
app.kubernetes.io/part-of: nextcloud-db
|
|
spec:
|
|
restartPolicy: Never
|
|
# Talks only to PostgreSQL; its one input is a mounted Secret, so it needs
|
|
# no Kubernetes API access at all (R-SEC-02, least privilege).
|
|
automountServiceAccountToken: false
|
|
securityContext:
|
|
runAsNonRoot: true
|
|
# uid/gid 26 is `postgres` in the CNPG PostgreSQL image.
|
|
runAsUser: 26
|
|
runAsGroup: 26
|
|
seccompProfile:
|
|
type: RuntimeDefault
|
|
containers:
|
|
- name: grant
|
|
# The SAME image the instances above run, pinned to the same tag. psql
|
|
# is already in it and it is already present on every node, so this
|
|
# adds no new image and no new supply-chain surface. Keep the tag in
|
|
# step with spec.imageName above when Renovate bumps it.
|
|
image: ghcr.io/cloudnative-pg/postgresql:16.15
|
|
imagePullPolicy: IfNotPresent
|
|
command:
|
|
- psql
|
|
# -w never prompts; -X skips ~/.psqlrc; ON_ERROR_STOP makes the Job
|
|
# FAIL LOUDLY rather than reporting success on a refused grant.
|
|
- "-w"
|
|
- "-X"
|
|
- "-v"
|
|
- "ON_ERROR_STOP=1"
|
|
- "-f"
|
|
- /etc/grant/grant.sql
|
|
env:
|
|
# libpq connection, assembled from the owner Secret. Target the -rw
|
|
# Service: GRANT is a write and must reach the primary.
|
|
- name: PGHOST
|
|
value: nextcloud-db-rw.nextcloud-db.svc.cluster.local
|
|
- name: PGPORT
|
|
value: "5432"
|
|
# Connecting to the application database itself is deliberate: the
|
|
# owner has CONNECT on it, and a successful connection is the same
|
|
# check the exporter was failing.
|
|
- name: PGDATABASE
|
|
value: nextcloud
|
|
- name: PGUSER
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: nextcloud-db-app
|
|
key: username
|
|
# Read by libpq from the environment and never logged; psql echoes
|
|
# no connection parameters.
|
|
- name: PGPASSWORD
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: nextcloud-db-app
|
|
key: password
|
|
- name: PGCONNECT_TIMEOUT
|
|
value: "10"
|
|
volumeMounts:
|
|
- name: sql
|
|
mountPath: /etc/grant
|
|
readOnly: true
|
|
securityContext:
|
|
allowPrivilegeEscalation: false
|
|
readOnlyRootFilesystem: true
|
|
capabilities:
|
|
drop:
|
|
- ALL
|
|
resources:
|
|
requests:
|
|
cpu: 10m
|
|
memory: 32Mi
|
|
limits:
|
|
memory: 128Mi
|
|
volumes:
|
|
- name: sql
|
|
configMap:
|
|
name: nextcloud-db-exporter-grant
|
|
defaultMode: 0444
|
|
---
|
|
# The desired state, as SQL. Idempotent: re-granting an existing privilege is a
|
|
# no-op, so a converged run changes nothing. The DO block is the ASSERTION — it
|
|
# turns "the grant did not take" into a failed Job instead of a silent success.
|
|
apiVersion: v1
|
|
kind: ConfigMap
|
|
metadata:
|
|
name: nextcloud-db-exporter-grant
|
|
namespace: nextcloud-db
|
|
labels:
|
|
app.kubernetes.io/name: nextcloud-db-exporter-grant
|
|
app.kubernetes.io/part-of: nextcloud-db
|
|
data:
|
|
grant.sql: |
|
|
-- The ONE privilege the CNPG metrics exporter is missing on this database.
|
|
-- CONNECT only: no CREATE, no TEMPORARY, no ownership, no table/schema grants.
|
|
-- Read access for the collector's pg_catalog queries already comes from the
|
|
-- built-in pg_monitor role, which CNPG grants this role when it creates it.
|
|
GRANT CONNECT ON DATABASE nextcloud TO cnpg_metrics_exporter;
|
|
|
|
-- Fail the Job if the privilege is somehow still absent, so a broken
|
|
-- reconciler surfaces as a failed Job rather than as a quiet blind spot.
|
|
-- NO PL/pgSQL, AND NOT ONE DOLLAR SIGN IN THIS FILE — DELIBERATE, including in these
|
|
-- comments. This assertion began life as a dollar-quoted DO block, which cannot survive
|
|
-- being shipped this way: the ConfigMap is rendered through Flux's postBuild
|
|
-- substitution, and envsubst collapses a doubled dollar into a single one. The block
|
|
-- reached Postgres with its opening quote halved and every Flux-reconciled run died
|
|
-- with a syntax error pointing at that character.
|
|
-- It PASSED when hand-applied with kubectl, because that path runs no envsubst — so
|
|
-- the defect was invisible until Flux owned the object, and three runs of Error pods
|
|
-- were the first sign. Escaping by doubling again would work and would also be the
|
|
-- next person's trap; plain SQL leaves substitution nothing to touch.
|
|
--
|
|
-- has_database_privilege() returns boolean; ::int is 1 when granted and 0 when not, so
|
|
-- the division raises division_by_zero and ON_ERROR_STOP=1 fails the Job. The divisor
|
|
-- is not a constant, so it cannot be folded away at plan time.
|
|
\echo 'asserting cnpg_metrics_exporter has CONNECT on database nextcloud'
|
|
SELECT 1 / has_database_privilege('cnpg_metrics_exporter', 'nextcloud', 'CONNECT')::int
|
|
AS exporter_can_connect;
|