search

cloudnative-pg / cloudnative-pg

CloudNativePG is a comprehensive platform designed to seamlessly manage PostgreSQL databases within Kubernetes environments, covering the entire operational lifecycle from initial deployment to ongoing maintenance
https://cloudnative-pg.io
Apache License 2.0
4.47k stars 302 forks source link

[Bug]: Logger PGAudit not working, postgres logger is used instead #4386

Closed rewemkris closed 4 months ago

rewemkris commented 5 months ago

Is there an existing issue already for this bug?

I have read the troubleshooting guide

I am running a supported version of CloudNativePG

Contact Details

No response

Version

1.23.0

What version of Kubernetes are you using?

1.28

What is your Kubernetes environment?

Cloud: Google GKE

How did you install the operator?

Helm

What happened?

{ "level": "info", "ts": "2024-04-26T10:55:22+02:00", "logger": "postgres", "msg": "record", "logging_pod": "autpgc16dbms01-development-1", "record": { "log_time": "2024-04-26 10:55:22.428 CEST", "user_name": "postgres", "database_name": "postgres", "process_id": "5024", "connection_from": "[local]", "session_id": "662b6bfa.13a0", "session_line_num": "5", "command_tag": "SET", "session_start_time": "2024-04-26 10:55:22 CEST", "virtual_transaction_id": "4/1471", "transaction_id": "0", "error_severity": "LOG", "sql_state_code": "00000", "message": "AUDIT: SESSION,2,1,MISC,SET,,,SET application_name TO cnpg_metrics_exporter,,0", "application_name": "cnpg_metrics_exporter", "backend_type": "client backend", "query_id": "-5293235264903128716" } }

The documentation states the fact that pgaudit extension will use the pgaudit logger

Cluster resource

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

Relevant log output

No response

Code of Conduct

rewemkris commented 5 months ago

seems that there is a problem when adding these two options in the config **# pgaudit.log_rows: "on" (probably this one causes the problem)

pgaudit.log_statement: "on"** (default on by pgaudit)

When disabling them i get the correct logoutput with pgaudit as logger

{ "level": "info", "ts": "2024-04-26T13:10:44+02:00", "logger": "pgaudit", "msg": "record", "logging_pod": "autpgc16test01-development-1", "record": { "log_time": "2024-04-26 13:10:44.785 CEST", "user_name": "postgres", "database_name": "postgres", "process_id": "123", "connection_from": "[local]", "session_id": "662b8bb4.7b", "session_line_num": "8", "command_tag": "GRANT", "session_start_time": "2024-04-26 13:10:44 CEST", "virtual_transaction_id": "3/171", "transaction_id": "755", "error_severity": "LOG", "sql_state_code": "00000", "application_name": "cnpg-instance-manager", "backend_type": "client backend", "query_id": "810986036675059406", "audit": { "audit_type": "SESSION", "statement_id": "8", "substatement_id": "1", "class": "ROLE", "command": "GRANT", "object_type": "FUNCTION", "statement": "GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO \"streaming_replica\"", "parameter": "" } } }

phisco commented 5 months ago

I think we don't handle the rows, that's why log_rows set to on is not recognised as a pgaudit record 🤔 I'll work on it tomorrow