feat: migrate Authentik PostgreSQL to CloudNative-PG operator

[ixxeL2097]

Overview

Replace the Bitnami PostgreSQL StatefulSet bundled with the Authentik Helm chart with a standalone CloudNative-PG managed Cluster, then point Authentik at it as an external database.

Problem with the current setup

The bundled Bitnami PostgreSQL is:

• A single-instance StatefulSet (no HA, no automatic failover)
• Managed by the Authentik Helm chart (hard to tune independently)
• No built-in WAL archiving or scheduled backups
• No connection pooling

What CloudNative-PG provides

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: authentik-postgres
spec:
  instances: 2
  postgresql:
    parameters:
      max_connections: "200"
      shared_buffers: 256MB
  storage:
    size: 20Gi
    storageClass: local-path-retain
  backup:
    barmanObjectStore:
      destinationPath: s3://minio.fredcorp.com/cnpg-backups
      serverName: authentik-postgres
    retentionPolicy: 7d
  affinity:
    podAntiAffinityType: required

Then configure Authentik to use the external DB:

authentik:
  postgresql:
    host: authentik-postgres-rw.authentik.svc
    name: authentik
    user: authentik

Benefits

• Automatic failover between 2 instances (primary election)
• WAL archiving to MinIO (already deployed on genmachine)
• PodMonitor for Prometheus metrics
• Independent lifecycle from the Authentik chart
• Required anti-affinity: primary and replica never co-schedule

Reference

• Inspired by szinn/k8s-homelab CloudNative-PG setup
• Chart: https://cloudnative-pg.github.io/charts
• Manifest location: gitops/manifests/cloudnative-pg/