Multiple users through pgbouncer

[Alansyf]

If you believe you have found have found a bug, please open up Bug Report

If you have a feature request, please open up a Feature Request

You can find information about general PGO support at:

https://access.crunchydata.com/documentation/postgres-operator/latest/support/

Questions

For questions that are neither bugs nor feature requests, please be sure to

• Provide information about your environment (see below for more information).
• Provide any steps or other relevant details related to your question.
• Attach logs, where applicable. Please do not attach screenshots showing logs unless you are unable to copy and paste the log data.
• Ensure any code / output examples are properly formatted for legibility.

Besides Pod logs, logs may also be found in the /pgdata/pg<MAJOR_VERSION>/log directory on your Postgres instance.

If you are looking for general support, please view the support page for where you can ask questions.

Environment

Please provide the following details:

• Platform: Kubernetes
• Platform Version: v1.29.9
• PGO Image Tag: ubi8-5.5.1-0
• Postgres Version 16
• Storage: hostpath

I am trying a very simple yaml to simulate two users and two databases

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo
  namespace: i345633
spec:
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.49-0
      manual:
        options:
        - --type=full
        repoName: repo1
      metadata:
        labels:
          sidecar.istio.io/inject: "false"
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - ReadWriteOnce
            resources:
              requests:
                storage: 1Gi
            storageClassName: csi-s3objectstorage
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-16.2-0
  instances:
  - dataVolumeClaimSpec:
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 10Gi
    name: instance1
    replicas: 1
  port: 5432
  postgresVersion: 16
  proxy:
    pgBouncer:
      config:
        global:
          server_connect_timeout: "10"
          server_idle_timeout: "10"
          server_login_retry: "3"
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.21-3
      port: 5432
      replicas: 1
  users:
  - databases:
    - db_user1
    name: user1
    options: CREATEDB CREATEROLE
    password:
      type: AlphaNumeric
  - databases:
    - db_user2
    name: user2
    options: CREATEDB CREATEROLE
    password:
      type: AlphaNumeric

However when i do
k port-forward svc/hippo-pgbouncer 5433:5432
I can't connect to db by user 'user1' and getting

connection failed: connection to server at "127.0.0.1", port 5433 failed: FATAL: server login has been failing, try again later (server_login_retry)
connection to server at "127.0.0.1", port 5433 failed: FATAL: SSL required

I can see pgbouncer logs

2025-03-18 10:02:27.943 UTC [44] LOG C-0x558abe5cad20: (nodb)/(nouser)@127.0.0.1:56908 closing because: SSL required (age=0s)
2025-03-18 10:02:27.943 UTC [44] WARNING C-0x558abe5cad20: (nodb)/(nouser)@127.0.0.1:56908 pooler error: SSL required

If i directly connect to the pod k port-forward hippo-instance1-g8kf-0 5433:5432, no issue at all.

Why through pgbouncer different?

[Alansyf]

Ok, issue is due to the isito Authorization policy. When add allow all rules then solved.