autoCreateUserSchema with postgres user defined in the spec.users causes scrape errors in pgMonitor

[n-asenov]

Overview

When autoCreateUserSchema is enabled and postgres user is defined in spec.users like

- name: postgres
  databases:
    - postgres
  options: "SUPERUSER"
  password:
    type: AlphaNumeric

pgMonitor 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"

[andrewlecuyer]

Hi @n-asenov, thanks for submitting this issue! I was able to reproduce the behavior you described.

And after digging into this a bit, it looks like the postgres user should simply not get a user schema. We're therefore going to work on a fix for this.

Thanks again!