autoCreateUserSchema with postgres user defined in the spec.users causes scrape errors in pgMonitor #4151
Description
Overview
When autoCreateUserSchema is enabled and postgres user is defined in spec.users like
- name: postgres databases: - postgres options: "SUPERUSER" password: type: AlphaNumericpgMonitor fails to scrape metrics from public.pg_stat_statements relation because the view pg_stat_statements is created in the postgres schema instead of being created in the public schema
Environment
- Platform: Kubernetes
- Platform Version: v1.32.3
- PGO Image Tag: ubi9-17.4-2513
- Postgres Version: 17
- Storage: openebs-hostpath
Steps to Reproduce
REPRO
Create PostgresCluster with the following configuration
--- apiVersion: postgres-operator.crunchydata.com/v1beta1 kind: PostgresCluster metadata: name: postgres annotations: postgres-operator.crunchydata.com/autoCreateUserSchema: "true" spec: postgresVersion: 17 instances: - name: postgres replicas: 1 dataVolumeClaimSpec: storageClassName: openebs-hostpath accessModes: - ReadWriteOnce resources: requests: storage: 1Gi backups: pgbackrest: repos: - name: repo1 volume: volumeClaimSpec: accessModes: - ReadWriteOnce resources: requests: storage: 1Gi users: - name: postgres databases: - postgres options: "SUPERUSER" password: type: AlphaNumeric monitoring: pgmonitor: exporter: {}EXPECTED
pgMonitor to run without scraping errors
ACTUAL
pgMonitor runs with scraping errors
Logs
time=2025-04-03T17:47:27.665Z level=INFO source=namespace.go:235 msg="error finding namespace" err="Error running query on database \"localhost:5432\": ccp_pg_stat_statements_top_total pq: relation \"public.pg_stat_statements\" does not exist" time=2025-04-03T17:47:27.667Z level=INFO source=namespace.go:235 msg="error finding namespace" err="Error running query on database \"localhost:5432\": ccp_pg_stat_statements_total pq: relation \"public.pg_stat_statements\" does not exist" time=2025-04-03T17:47:27.668Z level=INFO source=namespace.go:235 msg="error finding namespace" err="Error running query on database \"localhost:5432\": ccp_pg_stat_statements_top_max pq: relation \"public.pg_stat_statements\" does not exist" time=2025-04-03T17:47:27.669Z level=INFO source=namespace.go:235 msg="error finding namespace" err="Error running query on database \"localhost:5432\": ccp_pg_stat_statements_top_wal pq: relation \"public.pg_stat_statements\" does not exist" time=2025-04-03T17:47:27.677Z level=INFO source=namespace.go:235 msg="error finding namespace" err="Error running query on database \"localhost:5432\": ccp_pg_stat_statements_top_mean pq: relation \"public.pg_stat_statements\" does not exist" time=2025-04-03T17:47:27.679Z level=ERROR source=postgres_exporter.go:681 msg="error scraping dsn" err="queryNamespaceMappings returned 5 errors"