prole/deploy/opentofu/k3s/manifests/knoe/knoe-db.yaml

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: knoe-db
spec:
  instances: 3
  enablePDB: false
  imageName: ${KNOE_IMAGE_REGISTRY}/knoe-db:${KNOE_DB_IMAGE_TAG}
  postgresUID: 100
  postgresGID: 101
  maxSyncReplicas: 1
  affinity:
    # CloudNativePG uses its own affinity schema (not corev1.Affinity). See: `kubectl explain cluster.spec.affinity`
    enablePodAntiAffinity: true
    podAntiAffinityType: preferred
    topologyKey: kubernetes.io/hostname
    tolerations:
      - key: "node-role.kubernetes.io/control-plane"
        operator: "Exists"
        effect: "NoSchedule"
      - key: "node-role.kubernetes.io/master"
        operator: "Exists"
        effect: "NoSchedule"
    nodeAffinity:
      requiredDuringSchedulingIgnoredDuringExecution:
        nodeSelectorTerms:
          - matchExpressions:
              - key: node.kubernetes.io/instance-type
                operator: In
                values:
                  - k3s

  postgresql:
    parameters:
      shared_buffers: 256MB
      pg_stat_statements.max: '10000'
      pg_stat_statements.track: all
      # krb_server_keyfile omitted — Kerberos disabled on k3s (no keytab secret)
    shared_preload_libraries:
      - pg_stat_statements
      - pg_tde
    pg_hba:
      - local all postgres trust
      - local all knoe scram-sha-256
      # SCRAM auth — Kerberos/GSS rules omitted (kerberos_enabled=false on k3s)
      - host all postgres all scram-sha-256
      - host knoe knoe-db all scram-sha-256
      - host all all all scram-sha-256
      - hostssl knoe knoe-db all scram-sha-256

  bootstrap:
    initdb:
      database: knoe-db
      owner: knoe
      localeCollate: 'en_US.utf8'
      localeCType: 'en_US.utf8'
      secret:
        name: knoe-db-user
      postInitTemplateSQL:
        # Supabase convention: relocatable extensions live in `extensions`,
        # not `public`. Studio's Database Advisor flags `public.pg_stat_statements`
        # as a Security warning on the dashboard.
        - CREATE SCHEMA IF NOT EXISTS extensions;
        - CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA extensions;
      postInitSQL:
        - CREATE EXTENSION IF NOT EXISTS pg_tde;
        - CREATE EXTENSION IF NOT EXISTS pgcrypto;
        - GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO knoe;
        - CREATE EXTENSION IF NOT EXISTS postgis;
        - CREATE EXTENSION IF NOT EXISTS postgis_topology;
        - CREATE EXTENSION IF NOT EXISTS vector;
        - CREATE EXTENSION IF NOT EXISTS tds_fdw;
        - GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO knoe;
        - GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO knoe;
        - CREATE SCHEMA IF NOT EXISTS storage;
        - CREATE SCHEMA IF NOT EXISTS graphql_public;
        - CREATE ROLE anon NOLOGIN;
        - CREATE ROLE authenticator LOGIN;
        - GRANT USAGE ON SCHEMA public TO anon;
        - GRANT USAGE ON SCHEMA storage TO anon;
        - GRANT USAGE ON SCHEMA graphql_public TO anon;
        - GRANT anon TO authenticator;
        # demo schema for guest read-only access (evolves over time)
        - CREATE SCHEMA IF NOT EXISTS demo;
        - DO $do$ BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'guest') THEN CREATE ROLE guest LOGIN; END IF; END $do$;
        - GRANT USAGE ON SCHEMA demo TO guest;
        - ALTER DEFAULT PRIVILEGES IN SCHEMA demo GRANT SELECT ON TABLES TO guest;
        # knoe schema — owned by knoe role, used for application data + identity
        - DO $do$ BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'knoe') THEN CREATE ROLE knoe LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT; END IF; END $do$;
        - CREATE SCHEMA IF NOT EXISTS knoe AUTHORIZATION knoe;
        - ALTER SCHEMA knoe OWNER TO knoe;
        - REVOKE ALL ON SCHEMA knoe FROM PUBLIC;
        - ALTER ROLE knoe SET search_path TO knoe, public;
        - GRANT USAGE ON SCHEMA knoe TO knoe;
        # knoe.user — identity registry (Knoey Users)
        - CREATE TABLE IF NOT EXISTS knoe.user (id SERIAL PRIMARY KEY, username TEXT NOT NULL UNIQUE, realm TEXT NOT NULL DEFAULT 'PROLE.LOCAL', email TEXT, display_name TEXT, tenant_realm TEXT, is_realm_admin BOOLEAN DEFAULT false, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now());
        - CREATE TABLE IF NOT EXISTS knoe.user_role (user_id INT NOT NULL REFERENCES knoe.user(id) ON DELETE CASCADE, role TEXT NOT NULL, granted_at TIMESTAMPTZ DEFAULT now(), PRIMARY KEY (user_id, role));
        - GRANT SELECT, INSERT, UPDATE ON knoe.user TO knoe;
        - GRANT SELECT, INSERT, UPDATE ON knoe.user_role TO knoe;
        - GRANT USAGE, SELECT ON SEQUENCE knoe.user_id_seq TO knoe;
        # Task 4: align knoe.user with ekosystem user UUIDs
        - ALTER TABLE knoe.user ADD COLUMN IF NOT EXISTS ekosystem_uuid text UNIQUE;
      postInitApplicationSQLRefs:
        configMapRefs:
          - name: knoe-ekosystem-sql
            key: ekosystem.sql
          - name: knoe-ekosystem-sql
            key: ekosystem_objects.sql

  managed:
    roles:
      - name: admin
        ensure: present
        login: true
        superuser: true
        comment: "Kerberos admin principal — full cluster access"
      - name: guest
        ensure: present
        login: true
        superuser: false
        comment: "Kerberos guest principal — read-only access to demo schema"
      - name: developer
        ensure: present
        login: false
        superuser: false
        comment: "Developer group role — granted to knoe-system user accounts"

  certificates:
    serverAltDNSNames:
      - pg.prole.org
      - knoe-db-rw.knoe-db.svc.cluster.local

  enableSuperuserAccess: true
  plugins:
    - name: barman-cloud.cloudnative-pg.io
      enabled: true
      isWALArchiver: true
      parameters:
        barmanObjectName: knoe-db-barman-objectstore

  env:
    - name: AWS_REGION
      value: garage
    - name: AWS_DEFAULT_REGION
      value: garage

  storage:
    size: 1Gi
    pvcTemplate:
      accessModes:
        - ReadWriteOnce
      resources:
        requests:
          storage: 1Gi
      storageClassName: synology-iscsi
      selector:
        matchLabels:
          synology.storage/role: data
  walStorage:
    size: 1Gi
    pvcTemplate:
      accessModes:
        - ReadWriteOnce
      resources:
        requests:
          storage: 1Gi
      storageClassName: synology-iscsi
      selector:
        matchLabels:
          synology.storage/role: wal
  monitoring:
    enablePodMonitor: false