/test

❗ By default, the pull request is configured to backport to all release branches.

• To stop backporting this pr, remove the label: backport-requested ◀️ or add the label 'do not backport'
• To stop backporting this pr to a certain release branch, remove the specific branch label: release-x.y

@armru, here's the link to the E2E on CNPG workflow run: https://github.com/cloudnative-pg/cloudnative-pg/actions/runs/22718198409

@mnencia probably this should be tested with a pg_hba config like the one here https://github.com/cloudnative-pg/postgres-keycloak-oauth-validator?tab=readme-ov-file#example-cloudnativepg-configuration
Also I don't see a case of dual-stack in the testing, for order probably that case should be only one line?
On the other hand, instead of using a hard coded string for the mask is better to use something like this https://pkg.go.dev/net#IPMask.String but just an idea,
That was a quick check but the dual-stack thing is something that needs to be tested for sure

There is a problem with the markdown code for docs/src/cloudnative-pg.v1.md.

I created the angus cluster with a pgbouncer pooler (marshall):

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: angus
spec:
  instances: 3

  podSelectorRefs:
    - name: marshall-pooler
      selector:
        matchLabels:
          cnpg.io/podRole: pooler
          cnpg.io/cluster: angus
          cnpg.io/poolerName: marshall

  postgresql:
    pg_hba:
      - "hostssl all all ${podselector:marshall-pooler} scram-sha-256"
      - "host all all all reject"

  storage:
    size: 1Gi
---
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: marshall
spec:
  cluster:
    name: angus

  instances: 3
  type: rw
  pgbouncer:
    poolMode: session
    parameters:
      max_client_conn: "1000"
      default_pool_size: "10"

The cluster defines the pooler podSelectorRef that points to the pooler's pods via three labels. Then it enables access to the database via the pooler and then adds a fallback rule to reject any access.

I then create these two simple jobs with pgbench, one going through the pooler (PGHOST=marshall) and one going directly to the angus-rw service.

apiVersion: batch/v1
kind: Job
metadata:
  name: marshall-pgbench
spec:
  template:
    spec:
      containers:
      - args:
        - -i
        command:
        - pgbench
        env:
        - name: PGHOST
          value: marshall
        - name: PGDATABASE
          value: app
        - name: PGPORT
          value: "5432"
        - name: PGUSER
          valueFrom:
            secretKeyRef:
              key: username
              name: angus-app
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              key: password
              name: angus-app
        image: ghcr.io/cloudnative-pg/postgresql:18.1-system-trixie
        name: pgbench
      restartPolicy: Never
---
apiVersion: batch/v1
kind: Job
metadata:
  name: angus-pgbench
spec:
  template:
    spec:
      containers:
      - args:
        - -i
        command:
        - pgbench
        env:
        - name: PGHOST
          value: angus-rw
        - name: PGDATABASE
          value: app
        - name: PGPORT
          value: "5432"
        - name: PGUSER
          valueFrom:
            secretKeyRef:
              key: username
              name: angus-app
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              key: password
              name: angus-app
        image: ghcr.io/cloudnative-pg/postgresql:18.1-system-trixie
        name: pgbench
      restartPolicy: Never

This is the result:

$ kubectl get pods
NAME                        READY   STATUS      RESTARTS   AGE
angus-1                     1/1     Running     0          17m
angus-2                     1/1     Running     0          17m
angus-3                     1/1     Running     0          14m
angus-pgbench-7q56c         0/1     Error       0          7s
csi-hostpathplugin-0        8/8     Running     0          6h21m
marshall-6796d674ff-czmkj   1/1     Running     0          17m
marshall-6796d674ff-gzmf9   1/1     Running     0          17m
marshall-6796d674ff-wmlwk   1/1     Running     0          17m
marshall-pgbench-blm7c      0/1     Completed   0          7s

$ kubectl logs jobs/angus-pgbench
Found 2 pods, using pod/angus-pgbench-7q56c
pgbench: error: connection to server at "angus-rw" (10.96.155.110), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "10.244.1.20", user "app", database "app", SSL encryption
connection to server at "angus-rw" (10.96.155.110), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "10.244.1.20", user "app", database "app", no encryption
pgbench: error: could not create connection for initialization

$ kubectl logs jobs/marshall-pgbench
dropping old tables...
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) of pgbench_accounts done (elapsed 0.01 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.10 s (drop tables 0.03 s, create tables 0.00 s, client-side generate 0.03 s, vacuum 0.02 s, primary keys 0.01 s).

The pg_hba rule we created using the pod selector is able to allow access through the pooler. The job that tries to connect directly through the angus-rw primary service is prevented from accessing the database.

Moreover, the status of the cluster contains:

  podSelectorRefs:
  - ips:
    - 10.244.1.16
    - 10.244.2.16
    - 10.244.3.24
    name: pooler

If I delete a pgbouncer pod, a new one is recreated with a different IP and the status is immediately updated:

  podSelectorRefs:
  - ips:
    - 10.244.1.16
    - 10.244.2.20
    - 10.244.3.24
    name: pooler

@mnencia probably this should be tested with a pg_hba config like the one here https://github.com/cloudnative-pg/postgres-keycloak-oauth-validator?tab=readme-ov-file#example-cloudnativepg-configuration

Although I have not tested the validator, I have introduced the row in the pg_hba.conf, and it was parsed correctly, which to me was enough.

Also I don't see a case of dual-stack in the testing, for order probably that case should be only one line? On the other hand, instead of using a hard coded string for the mask is better to use something like this https://pkg.go.dev/net#IPMask.String but just an idea, That was a quick check but the dual-stack thing is something that needs to be tested for sure

The dual-stack issue should be addressed globally, as it is not tied to this specific feature (as far as I understand). There is an ongoing discussion about this (#7116), but my response aligns with what I’ve been saying: considering everything, I believe there are more critical areas for the project to focus on at this stage, particularly regarding technical debt.

I believe the feature, as it stands, provides significant value and should be considered a major feature for version 1.29. However, it would be great to get your review if you have time.

/test

@mnencia, here's the link to the E2E on CNPG workflow run: https://github.com/cloudnative-pg/cloudnative-pg/actions/runs/22861590448

/test

@mnencia, here's the link to the E2E on CNPG workflow run: https://github.com/cloudnative-pg/cloudnative-pg/actions/runs/22873766938