apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: autpgc16dbms01-development
  namespace: aut
  labels:
    workload: database
spec:
  env:
    - name: TZ
      value: "Europe/Vienna"
  resources:
    requests:
      memory: "16Gi"
      cpu: "4"
    limits:
      memory: "16Gi"
      cpu: "4"

  storage:
    storageClass: zonalpdssd-storageclass
    size: 50Gi
  walStorage:
    storageClass: zonalpdssd-storageclass
    size: 50Gi  #shrinking not possible after expanding

  affinity:
    tolerations:
    - key: "node-pool"
      operator: "Exists"
      effect: "NoSchedule"
    enablePodAntiAffinity: true
    topologyKey: topology.kubernetes.io/zone
    podAntiAffinityType: required 

  description: "PGCluster Crm data and insights"

  #imagePullSecret is required because the images are located in a private registry
  imagePullSecrets:
    - name: regcred  #must exist in the namespace of the cluster
  imageCatalogRef:
    apiGroup: postgresql.cnpg.io
    kind: ImageCatalog
    name: postgis
    major: 16
  instances: 1

  primaryUpdateStrategy: unsupervised

  #PostgreSQL configs
  postgresql:
    enableAlterSystem: true
    parameters:
      #pthomschitz
      ## good inital - change on APP request
      work_mem: "20MB" #per Transaction for Orders and Groups - up to usage
      max_wal_size: "1GB" #1% von DBsize
      min_wal_size: "256MB" #50% von max_wal_size
      maintenance_work_mem: "1GB"
      max_connections: '100' #effective 97 (max_connections - superuser_reserved_connections)
      max_slot_wal_keep_size: "4GB"

      ## CHANGE before deploy
      shared_buffers: "4096MB"   #25% von memory
      effective_cache_size: "12048MB"  # 50 - 75% vom memory
      max_worker_processes: "4" #Anzahl CPUs
      max_parallel_workers: "4" #Anzahl CPUs
      max_parallel_workers_per_gather: "2" #Anzahl CPUs/2
      max_parallel_maintenance_workers: "2" #Anzahl CPUs/2

      ## fixed
      effective_io_concurrency: "200"  #100 bei Raid5 bzw SSD - bis 300
      random_page_cost: "1.1"
      checkpoint_timeout: "5min"
      checkpoint_completion_target: "0.9"
      superuser_reserved_connections: "3"
      password_encryption: "scram-sha-256"

      ### fixed - extensions
      # shared_preload_library: "pg_stat_statements,auto_explain" added by operator automatically
      pg_stat_statements.max: "1000"
      pg_stat_statements.track: "all"
      auto_explain.log_min_duration: "10s"

      ### fixed - logging/audit
      #log_line_prefix: "%m %u %d [%p]: "
      log_checkpoints: "off"
      log_connections: "on"
      log_disconnections: "on"
      log_lock_waits: "off"
      log_temp_files: "-1"
      
      pgaudit.log: "READ, WRITE, FUNCTION, DDL, ROLE"
      pgaudit.log_catalog: "off"
      pgaudit.log_parameter: "on"
      pgaudit.log_relation: "on"
      pgaudit.log_rows: "on"
      pgaudit.log_statement: "on"


    pg_hba:
      - hostssl all all 0.0.0.0/0 scram-sha-256


  bootstrap:
    initdb:
      localeCollate: 'en_US.utf8'
      localeCType: 'en_US.utf8'
      database: postgres
      owner: postgres
      secret:
        name: postgres-superuser-secret
      dataChecksums: true
      encoding: 'UTF8' #default
      postInitTemplateSQL:
        - create user vault_admin with password 'Start1234' CREATEROLE;
        - create user apl_admin with password 'Start1234' CREATEDB CREATEROLE;
        - revoke create,usage on schema public from public;
        - revoke all on database template1 from public;
        - create ROLE app_readonly;
        - COMMENT ON ROLE app_readonly IS 'Application RO Role';
        - COMMENT ON ROLE apl_admin IS 'Application Plattform User';
        - create ROLE app_readwrite;
        - COMMENT ON ROLE app_readwrite IS 'Application RW Role';
        - COMMENT ON ROLE vault_admin IS 'Vault Usermanagement';
        - GRANT vault_admin to apl_admin with ADMIN OPTION;
        - GRANT app_readwrite TO vault_admin WITH ADMIN OPTION; #in dev grant readwrite
        - GRANT app_readonly TO app_readwrite with Admin option;
        - grant app_readwrite to apl_admin with Admin option;
        - GRANT CONNECT ON DATABASE postgres to apl_admin,vault_admin;
        - CREATE SCHEMA general AUTHORIZATION pg_database_owner;
        - REVOKE CREATE ON SCHEMA general from app_readonly;
        - ALTER DEFAULT PRIVILEGES IN SCHEMA general,public for role postgres,apl_admin grant SELECT ON TABLES to app_readonly,pg_database_owner;
        - ALTER DEFAULT PRIVILEGES IN SCHEMA general,public for role postgres,apl_admin grant SELECT,USAGE ON SEQUENCES to app_readonly,pg_database_owner;
        - ALTER DEFAULT PRIVILEGES IN SCHEMA general,public for role postgres,apl_admin grant EXECUTE ON FUNCTIONS to app_readwrite,pg_database_owner;
        - ALTER DEFAULT PRIVILEGES IN SCHEMA general,public for role postgres,apl_admin grant USAGE ON TYPES to app_readonly,pg_database_owner;
        - GRANT USAGE ON SCHEMA general,public TO app_readonly;
        - alter user app_readwrite set pgaudit.log to 'NONE';
        - alter user app_readonly set pgaudit.log to 'NONE';
        - alter user postgres set pgaudit.log to 'NONE';

  #Superuser config
  enableSuperuserAccess: true
  superuserSecret:
    name: postgres-superuser-secret

  serviceAccountTemplate: #workloadidentity for backup to cloud storage
   metadata:
    annotations:
     iam.gke.io/gcp-service-account: autpgc16dbms01-development@ri-te-cs-dbms-k8s-development.iam.gserviceaccount.com

  monitoring:
    enablePodMonitor: true