PGO V5 Postgres Pgbouncer issue

[shusaan]

Hi, Here is Postgres cluster config, I have disabled TLS on this cluster

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: db
  namespace: dev-test
spec:
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.41-2
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - ReadWriteOnce
            resources:
              requests:
                storage: 5Gi
            storageClassName: nfs-storage
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-13.9-2
  instances:
  - affinity:
      podAntiAffinity:
        preferredDuringSchedulingIgnoredDuringExecution:
        - podAffinityTerm:
            labelSelector:
              matchLabels:
                postgres-operator.crunchydata.com/cluster: db
                postgres-operator.crunchydata.com/instance-set: db
            topologyKey: kubernetes.io/hostname
          weight: 1
    dataVolumeClaimSpec:
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 5Gi
    name: db
    replicas: 1
  patroni:
    dynamicConfiguration:
      postgresql:
        parameters:
          max_connections: 500
          shared_buffers: 500MB
          synchronous_commit: "on"
        pg_hba:
        - hostnossl all all all md5
    leaderLeaseDurationSeconds: 30
    port: 8008
    syncPeriodSeconds: 10
  port: 5432
  postgresVersion: 13
  proxy:
    pgBouncer:
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - podAffinityTerm:
              labelSelector:
                matchLabels:
                  postgres-operator.crunchydata.com/cluster: db
                  postgres-operator.crunchydata.com/role: pgbouncer
              topologyKey: kubernetes.io/hostname
            weight: 1
      config:
        databases:
          '*': host=db-primary port=5432
        global:
          client_tls_sslmode: disable
          default_pool_size: "100"
          max_client_conn: "500"
          pool_mode: transaction
          query_wait_timeout: "240"
          server_tls_sslmode: disable
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.17-5
      port: 5432
      replicas: 1
  users:
  - name: postgres
    options: SUPERUSER

When I Connect through db-primary service it is working

But when I use the pgbouncer service db-pgbouncer Here is the error I am facing, Please Help

Environment

Please provide the following details:

• Platform: (Kubernetes, RKE2.)
• Postgres Version (e.g. 13)

[benjaminjb]

Hello @shusaan, sorry to hear that you've run into this problem.

I was able to replicate the problem, and I have some thoughts about the issue for you to try. But I'm first curious about what your use case is for disabling TLS?

That disabled TLS is the root of the problem here: PGO wants TLS in a few places including the pg_hba.conf line for the _crunchypgbouncer user. When I run into this problem, I see the log

WARNING server login failed: FATAL pg_hba.conf rejects connection for host "<some IP>", user "_crunchypgbouncer", database "postgres", no encryption

In order to get around that line PGO writes to the pg_hba.conf, we would have to override some of the defaults written into that file. We have a ticket in our backlog to address how PGO determines pg_hba and how to allow users to customize lines that PGO sets, and I've linked this issue to that ticket, since that would be one way to solve it.

I do wonder if you could go forward now by, essentially, overriding the auth_user and auth_file that pgBouncer uses and setting a different pg_hba.conf line for that user than for the default _crunchypgbouncer user.

But this seems like a fair amount of work to get around a problem this cluster has because TLS is disabled, which brings me back to my first question: what's the use case for disabling TLS?

[tjmoore4]

Since we haven't heard back on this issue for some time, I am closing this issue. If you need further assistance, feel free to re-open this issue or ask a question in our Discord server.