Turn off SSL when connecting to bouncer

PostgreSQL Percona Operator for PostgreSQL
,
1

Hi,

I’m new to the database operator scene. I have two questions.

I’m getting this error when connecting to the bouncer service.

ERROR: SQLSTATE[08006] [7] connection to server at "postgres-db-pg-db-pgbouncer.postgres-ha.svc.cluster.local" (10.43.245.232), port 5432 failed: FATAL:  SASL authentication failed
connection to server at "postgres-db-pg-db-pgbouncer.postgres-ha.svc.cluster.local" (10.43.245.232), port 5432 failed: FATAL:  SSL required
ERROR: Failed to connect to database

How can I turn off SSL to connect to the bouncer service?

And my second question, in general, how are application users supposed to be added to the database? I’ve been through the docs and this forum. I’ve seen something referring to using Crossplane, but that seems like overkill for just getting started. Any tips on how to create application users would be greatly appreciated.

Scott

2

Hi,

Offhand without looking at your configuration settings, located at /etc/pgbouncer/pgbouncer.ini, you should refer to these two runtime parameters to see if they’ve been enabled by accident;

  • server_tls_sslmode
  • client_tls_sslmode

Look up the references for these configuration parameters here.

Hope this helps.

3

Hi Robert,

Thanks for the reply. Would I be looking inside the pgBouncer container for this ini file? I’ll go looking. But, wouldn’t there be a setting in the Helm charts for the server or operator to set these settings? I know of the tlsOnly setting. It is set to false.

Scott

4

I have this in pgbouncer.ini.

client_tls_sslmode = require

How to go about changing it?

Scott

5

I’ve got the client_tls_sslmode value to change to disable and allow. With both values I now get:

FATAL:  server login has been failing, try again later (server_login_retry)

When I set both server_tls_sslmode and client_tls_sslmode to disable, I get:

ERROR: SQLSTATE[08006] [7] connection to server at "postgres-db-pg-db-pgbouncer.postgres-ha.svc.cluster.local" (10.43.245.232), port 5432 failed: FATAL:  SASL authentication failed
ERROR: Failed to connect to database

Scott

6

Offhand it sounds like you need to login as the pgbouncer administrator
Take a look at these two blogs and see if they can you out.

7

Hello,

I am having a similar issue when using operator 2.2.0 on minikube after following the instructions here: Install on Minikube - Percona Operator for PostgreSQL. When I try to access the database using a service or when connecting to the pod directly, I can get a connection.

$ PGPASSWORD='my-password' psql -h pg-cluster1-ha -U developer
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1), server 15.2 - Percona Distribution)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

(works)

$ PGPASSWORD='my-password' psql -h 10.244.0.30 -U developer
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1), server 15.2 - Percona Distribution)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

(works)

in both cases, SSL connection is made… no issues at all. However, when I try this:

$ PGPASSWORD='my-password' psql -h pg-cluster1-pgbouncer -U developer
psql: error: connection to server at "pg-cluster1-pgbouncer" (10.107.250.90), port 5432 failed: FATAL:  server login has been failing, try again later (server_login_retry)
connection to server at "pg-cluster1-pgbouncer" (10.107.250.90), port 5432 failed: FATAL:  SSL required

(it either freezes, no reply at all or the above error.)

The pgbouncer log shows:

2023-09-04 11:38:36.898 UTC [7] LOG C-0x557b55d76b60: developer/(nouser)@10.244.0.40:57928 closing because: server login has been failing, try again later (server_login_retry) (age=0s)
2023-09-04 11:38:36.898 UTC [7] WARNING C-0x557b55d76b60: developer/(nouser)@10.244.0.40:57928 pooler error: server login has been failing, try again later (server_login_retry)
2023-09-04 11:38:36.898 UTC [7] LOG C-0x557b55d76b60: (nodb)/(nouser)@10.244.0.40:57936 closing because: SSL required (age=0s)
2023-09-04 11:38:36.898 UTC [7] WARNING C-0x557b55d76b60: (nodb)/(nouser)@10.244.0.40:57936 pooler error: SSL required

but clearly psql is trying SSL.

If I try to connect using the pgbouncer pod IP directly I get:

$ PGPASSWORD='my-password' psql -h 10.244.0.32 -U developer
psql: error: connection to server at "10.244.0.32", port 5432 failed: FATAL:  server login has been failing, try again later (server_login_retry)
connection to server at "10.244.0.32", port 5432 failed: FATAL:  SSL required

The pgbouncer log shows:

2023-09-04 11:46:02.376 UTC [7] LOG C-0x557b55d76900: developer/(nouser)@10.244.0.40:49098 closing because: server login has been failing, try again later (server_login_retry) (age=0s)
2023-09-04 11:46:02.376 UTC [7] WARNING C-0x557b55d76900: developer/(nouser)@10.244.0.40:49098 pooler error: server login has been failing, try again later (server_login_retry)
2023-09-04 11:46:02.376 UTC [7] LOG C-0x557b55d76900: (nodb)/(nouser)@10.244.0.40:49106 closing because: SSL required (age=0s)
2023-09-04 11:46:02.376 UTC [7] WARNING C-0x557b55d76900: (nodb)/(nouser)@10.244.0.40:49106 pooler error: SSL required

It seems strange that in both cases it is showing (nodb)/(nouser).

Is that normal?

pg_hba.conf includes theses entries:

# Do not edit this file manually!
# It will be overwritten by Patroni!
local all "postgres" peer
hostssl replication "_crunchyrepl" all cert
hostssl "postgres" "_crunchyrepl" all cert
host all "_crunchyrepl" all reject
host all "monitor" "127.0.0.0/8" scram-sha-256
host all "monitor" "::1/128" scram-sha-256
host all "monitor" all reject
hostssl all "_crunchypgbouncer" all scram-sha-256
host all "_crunchypgbouncer" all reject
hostssl all all all md5
host all "developer" "127.0.0.0/8" peer
host all all "0.0.0.0/0" md5

but there is another pg_hba.conf here: /opt/crunchy/conf/postgres/pg_hba.conf . Perhaps this is a template?

There is no editor on the postgres instance. Does this mean that the only way to configure pg_hba.conf? Perhaps I should be configuring it from within psql using SQL?

I have tried to add the setting: tlsOnly to cr.yaml but according to the docs, this setting no longer exists in version 2.2.0 according to this page: Custom Resource options - Percona Operator for PostgreSQL

Including it produces this error:

Error from server (BadRequest): error when creating "percona-postgresql-cluster.yaml": PerconaPGCluster in version "v2" cannot be handled as a PerconaPGCluster: strict decoding error: unknown field "spec.tlsOnly"

I have also tried to look at the pgbouncer config as suggested above but strangely there is nothing in the /etc/pgbouncer/pgbouncer.ini as suggested above but there are pgbouncer ini files here:

  • /usr/share/doc/pgbouncer/pgbouncer.ini (owned by root so I CANNOT edit but looks like a template)
  • /opt/crunchy/conf/pgbouncer.ini (owned by daemon, so must be the one)
  • /etc/pgbouncer/pgbouncer.ini (EMPTY)
  • /etc/pgbouncer/…2023_09_02_23_45_54.1327105812/pgbouncer.ini (EMPTY)

The only one I can edit contains:

[databases]
* = host=PG_SERVICE port=PG_PORT auth_user=pgbouncer

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /pgconf/users.txt
auth_query = SELECT username, password from pgbouncer.get_auth($1)
pidfile = /tmp/pgbouncer.pid
logfile = /dev/stdout
admin_users = pgbouncer
stats_users = pgbouncer
default_pool_size = DEFAULT_POOL_SIZE
max_client_conn = MAX_CLIENT_CONN
max_db_connections = MAX_DB_CONNECTIONS
min_pool_size = MIN_POOL_SIZE
pool_mode = POOL_MODE
reserve_pool_size = RESERVE_POOL_SIZE
reserve_pool_timeout = RESERVE_POOL_TIMEOUT
query_timeout = QUERY_TIMEOUT
ignore_startup_parameters = IGNORE_STARTUP_PARAMETERS

So… why can I connect directly using SSL but NOT using pgbouncer? There is no specific requirement to connect via SSL… it’s optional so is pgbouncer trying to connect o the database without?

Apologies for so many related questions… but I have been trying to get things working for quite some time. Please assist.

8

I wonder if the error message might be something of a red herring.

I’ve just completely rebuilt the Postgres cluster using this configuration

apiVersion: pgv2.percona.com/v2
kind: PerconaPGCluster
metadata:
  name: pg-cluster1
#  finalizers:
#  - percona.com/delete-pvc
#  - percona.com/delete-ssl
spec:
  crVersion: 2.2.0
#  secrets:
#    customTLSSecret:
#      name: cluster1-cert
#    customReplicationTLSSecret:
#      name: replication1-cert

#  standby:
#    enabled: true
#    host: "<primary-ip>"
#    port: "<primary-port>"
#    repoName: repo1

#  openshift: true

  users:
  - name: developer
    databases:
    - developer
    options: "SUPERUSER"
    password:
      type: ASCII
    secretName: "pg-cluster1-developer-credentials"

#  databaseInitSQL:
#    key: init.sql
#    name: cluster1-init-sql

#  pause: true
#  unmanaged: true

#  dataSource:
#    postgresCluster:
#      clusterName: cluster1
#      repoName: repo1
#      options:
#      - --type=time
#      - --target="2021-06-09 14:15:11-04"
#    pgbackrest:
#      stanza: db
#      configuration:
#      - secret:
#          name: pgo-s3-creds
#      global:
#        repo1-path: /pgbackrest/postgres-operator/hippo/repo1
#      repo:
#        name: repo1
#        s3:
#          bucket: "my-bucket"
#          endpoint: "s3.ca-central-1.amazonaws.com"
#          region: "ca-central-1"

  image: percona/percona-postgresql-operator:2.2.0-ppg15-postgres
  imagePullPolicy: Always
  postgresVersion: 15
#  port: 5432

#  expose:
#    annotations:
#      my-annotation: value1
#    labels:
#      my-label: value2
#    type: LoadBalancer

  instances:
  - name: instance1
    replicas: 1
#    resources:
#      limits:
#        cpu: 2.0
#        memory: 4Gi
#
#    sidecars:
#    - name: testcontainer
#      image: mycontainer1:latest
#    - name: testcontainer2
#      image: mycontainer1:latest
#
#    topologySpreadConstraints:
#      - maxSkew: 1
#        topologyKey: my-node-label
#        whenUnsatisfiable: DoNotSchedule
#        labelSelector:
#          matchLabels:
#            postgres-operator.crunchydata.com/instance-set: instance1
#
#    tolerations:
#    - effect: NoSchedule
#      key: role
#      operator: Equal
#      value: connection-poolers
#
#    priorityClassName: high-priority
#
#    walVolumeClaimSpec:
#       accessModes:
#       - "ReadWriteOnce"
#       resources:
#         requests:
#           storage: 1Gi
#
    dataVolumeClaimSpec:
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 1Gi

  proxy:
    pgBouncer:
      replicas: 1
      image: percona/percona-postgresql-operator:2.2.0-ppg15-pgbouncer
#      exposeSuperusers: true
#      resources:
#        limits:
#          cpu: 200m
#          memory: 128Mi
#
#      expose:
#        annotations:
#          my-annotation: value1
#        labels:
#          my-label: value2
#        type: LoadBalancer
#
#      affinity:
#        podAntiAffinity:
#          preferredDuringSchedulingIgnoredDuringExecution:
#          - weight: 1
#            podAffinityTerm:
#              labelSelector:
#                matchLabels:
#                  postgres-operator.crunchydata.com/cluster: keycloakdb
#                  postgres-operator.crunchydata.com/role: pgbouncer
#              topologyKey: kubernetes.io/hostname
#
#      tolerations:
#      - effect: NoSchedule
#        key: role
#        operator: Equal
#        value: connection-poolers
#
#      topologySpreadConstraints:
#        - maxSkew: 1
#          topologyKey: my-node-label
#          whenUnsatisfiable: ScheduleAnyway
#          labelSelector:
#            matchLabels:
#              postgres-operator.crunchydata.com/role: pgbouncer
#
#      sidecars:
#      - name: bouncertestcontainer1
#        image: mycontainer1:latest
#
#      customTLSSecret:
#        name: keycloakdb-pgbouncer.tls
#
#      config:
#        global:
#          pool_mode: transaction

  backups:
    pgbackrest:
#      metadata:
#        labels:
      image: percona/percona-postgresql-operator:2.2.0-ppg15-pgbackrest
#      configuration:
#        - secret:
#            name: cluster1-pgbackrest-secrets
#      jobs:
#        priorityClassName: high-priority
#        resources:
#          limits:
#            cpu: 200m
#            memory: 128Mi
#        tolerations:
#        - effect: NoSchedule
#          key: role
#          operator: Equal
#          value: connection-poolers
#
#      global:
#        repo1-retention-full: "14"
#        repo1-retention-full-type: time
#        repo1-path: /pgbackrest/postgres-operator/cluster1/repo1
#        repo1-cipher-type: aes-256-cbc
#        repo1-s3-uri-style: path
#        repo2-path: /pgbackrest/postgres-operator/cluster1-multi-repo/repo2
#        repo3-path: /pgbackrest/postgres-operator/cluster1-multi-repo/repo3
#        repo4-path: /pgbackrest/postgres-operator/cluster1-multi-repo/repo4
#      repoHost:
#        priorityClassName: high-priority
#
#        topologySpreadConstraints:
#        - maxSkew: 1
#          topologyKey: my-node-label
#          whenUnsatisfiable: ScheduleAnyway
#          labelSelector:
#            matchLabels:
#              postgres-operator.crunchydata.com/pgbackrest: ""
#        affinity:
#          podAntiAffinity:
#            preferredDuringSchedulingIgnoredDuringExecution:
#             - weight: 1
#             podAffinityTerm:
#               labelSelector:
#               matchLabels:
#                 postgres-operator.crunchydata.com/cluster: keycloakdb
#                 postgres-operator.crunchydata.com/role: pgbouncer
#               topologyKey: kubernetes.io/hostname
#
      manual:
        repoName: repo1
        options:
         - --type=full
      repos:
      - name: repo1
        schedules:
          full: "0 0 * * 6"
#          differential: "0 1 * * 1-6"
        volume:
          volumeClaimSpec:
            accessModes:
            - ReadWriteOnce
            resources:
              requests:
                storage: 1Gi
#      - name: repo2
#        s3:
#          bucket: "<YOUR_AWS_S3_BUCKET_NAME>"
#          endpoint: "<YOUR_AWS_S3_ENDPOINT>"
#          region: "<YOUR_AWS_S3_REGION>"
#      - name: repo3
#        gcs:
#          bucket: "<YOUR_GCS_BUCKET_NAME>"
#      - name: repo4
#        azure:
#          container: "<YOUR_AZURE_CONTAINER>"
#
#    restore:
#      enabled: true
#      repoName: repo1
#      options:
#       PITR restore in place
#       - --type=time
#       - --target="2021-06-09 14:15:11-04"
#       restore individual databases
#       - --db-include=hippo

  pmm:
    enabled: false
    image: percona/pmm-client:2.37.0
#    imagePullPolicy: IfNotPresent
    secret: pg-cluster1-pmm-secret
    serverHost: monitoring-service

  patroni:
    dynamicConfiguration:
      postgresql:
        parameters:
          max_parallel_workers: 2
          max_worker_processes: 2
          shared_buffers: 1GB
          work_mem: 2MB
        pg_hba:
        - host    all all 0.0.0.0/0 md5
        - hostssl all all 0.0.0.0/0 md5
        - local   all all trust
        - host    all all ::1/128   md5

(nothing too exotic… just reduced replicas down to 1, uniquely identified the cluster and opened up postgres using pg_hba.conf settings in the patroni section)

And the first time I try to access using pgbouncer on it’s pod IP as follows:

$ psql -h 10.244.0.59 -U developer

I get this:

2023-09-04 17:00:25.046 UTC [7] LOG C-0x55e7d94d3900: (nodb)/(nouser)@10.244.0.40:41480 registered new auto-database: db=developer
2023-09-04 17:00:25.050 UTC [7] WARNING DNS lookup failed: pg-cluster1-primary: result=0
2023-09-04 17:00:25.050 UTC [7] LOG S-0x55e7d94e4670: developer/_crunchypgbouncer@(bad-af):0 closing because: server DNS lookup failed (age=0s)
2023-09-04 17:00:40.110 UTC [7] WARNING DNS lookup failed: pg-cluster1-primary: result=0
2023-09-04 17:00:40.110 UTC [7] LOG S-0x55e7d94e4670: developer/_crunchypgbouncer@(bad-af):0 closing because: server DNS lookup failed (age=0s)
2023-09-04 17:00:55.104 UTC [7] LOG S-0x55e7d94e4670: developer/_crunchypgbouncer@(bad-af):0 closing because: server DNS lookup failed (age=0s)
2023-09-04 17:01:00.972 UTC [7] LOG C-0x55e7d94d3b60: developer/(nouser)@10.244.0.40:56204 closing because: server login has been failing, try again later (server_login_retry) (age=0s)
2023-09-04 17:01:00.972 UTC [7] WARNING C-0x55e7d94d3b60: developer/(nouser)@10.244.0.40:56204 pooler error: server login has been failing, try again later (server_login_retry)
2023-09-04 17:01:00.973 UTC [7] LOG C-0x55e7d94d3b60: (nodb)/(nouser)@10.244.0.40:56206 closing because: SSL required (age=0s)
2023-09-04 17:01:00.973 UTC [7] WARNING C-0x55e7d94d3b60: (nodb)/(nouser)@10.244.0.40:56206 pooler error: SSL required
2023-09-04 17:01:10.448 UTC [7] WARNING DNS lookup failed: pg-cluster1-primary: result=0
2023-09-04 17:01:10.448 UTC [7] LOG S-0x55e7d94e4670: developer/_crunchypgbouncer@(bad-af):0 closing because: server DNS lookup failed (age=0s)

still exactly the same error if I connect via the pgbouncer service.so… same issue as before… very strange.

Could the SSL error be a red herring? Perhaps it’s the DNS issue that’s causing the problem?

Perhaps this is related to the issue that I reported earlier this year: Pgbouncer server DNS lookup failed - PostgreSQL - Percona Community Forum

Any/all suggestions welcome!

9

OK… this is interesting.

I found the configMap that the Operator creates and it’s pointing to pg-cluster1-primary… but if I look at the services that have been created:

cert-manager   cert-manager            ClusterIP      10.104.98.100    <none>          9402/TCP                     168m
cert-manager   cert-manager-webhook    ClusterIP      10.106.99.42     <none>          443/TCP                      168m
default        kubernetes              ClusterIP      10.96.0.1        <none>          443/TCP                      4d21h
default        pg-cluster1-ha          ClusterIP      10.107.88.52     <none>          5432/TCP                     158m
default        pg-cluster1-ha-config   ClusterIP      None             <none>          <none>                       158m
default        pg-cluster1-pgbouncer   ClusterIP      10.103.122.26    <none>          5432/TCP                     158m
default        pg-cluster1-pods        ClusterIP      None             <none>          <none>                       158m
default        pg-cluster1-primary     ClusterIP      None             <none>          5432/TCP                     158m
default        pg-cluster1-replicas    ClusterIP      10.97.116.87     <none>          5432/TCP                     158m
default        whoami                  ClusterIP      10.99.117.174    <none>          80/TCP                       22h
kube-system    kube-dns                ClusterIP      10.96.0.10       <none>          53/UDP,53/TCP,9153/TCP       4d21h
traefik        traefik                 LoadBalancer   10.99.116.26     192.168.49.31   80:32213/TCP,443:31368/TCP   3d15h
traefik        whoami                  ClusterIP      10.103.110.152   <none>          80/TCP                       3d6h

It looks like: pg-cluster1-primary has no IP.

So… perhaps setting replicas to 1 creates a replica but not a primary.

I’m going to point pgbouncer at: pg-cluster1-ha and see what happens.

…

Well seems like I cannot edit the confgMap… The Operator keeps changing back when I save it.

Trying to figure out why pg-cluster1-primary has no IP. Perhaps that will get me somewhere. It’s almost as if standby is enabled.

10

One little suggestion: confirm whether or not your environment is currently using IPv6 too,.

11

Yes, it is… should IP6 be turned off?

12

Further to my previous posts…

For anyone who stumbles upon this thread… a clever hack was suggested by Slavisa_Milojkovic for how to deal with this if the Operator does not inject the URI: pg-cluster1-primary into pgbouncer.

You can create a coreDNS rewrite rule that forces requests for the IP for cluster1-primary to to cluster1-ha… See here: Custom DNS Entries For Kubernetes .

For more info, see here: Pgbouncer server DNS lookup failed - PostgreSQL - Percona Community Forum

Not a fix, but a good work around.

I have checked here, on the CrunchyData forum and elsewhere on the internet but there is still no real solution or a reason why cluster1-primary does not have an IP address. Perhaps IP6 has something to do with it?

There is another potential work around… the docs in the Operator repo suggest that we can add databases and users configurations to pgbouncer.ini. but unfortunately it does not indicate what the correct syntax for configuration entries in spec.proxy.pgBouncer.config.databases or spec.proxy.pgBouncer.config.users?

See these posts for more info: Pgbouncer not finding cluster1-primary - PostgreSQL - Percona Community Forum

13

I wouldn’t be surprised if host name resolution was being carried out via IPv6

14

I will double check and let you know.