Pgbouncer server DNS lookup failed

PostgreSQL
14

hmmm…still not working! I am detailing for anyone who happens across this thread in future.

I have added this line to coredns config:

rewrite name pg-cluster1-primary.default.svc.cluster pg-cluster1-ha.default.svc.cluster
rewrite name pg-cluster1-primary pg-cluster1-ha.default.svc.cluster

(just in case I needed both)

and I know that rewrite works and can be resolved in the pgbouncer pod because in the pgbouncer pod I can see that IP as follows:

$ kubectl exec -it pg-cluster1-pgbouncer-57fb5cfbfd-rfg87 bash
$ bash-4.4$ nslookup
> pg-cluster1-primary
Server:         10.96.0.10
Address:        10.96.0.10#53

Name:   pg-cluster1-primary.default.svc.cluster.local
Address: 10.103.237.221
>

(10.103.237.221 is the same IP as pg-cluster1-ha)

From elsewhere:

$ ping  pg-cluster1-pgbouncer
PING pg-cluster1-pgbouncer.default.svc.cluster.local (10.111.245.47) 56(84) bytes of data.

When I use:

$ psql -h pg-cluster1-primary -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)
Type "help" for help.

developer=#

it works… coredns rewrite seems to be doing it’s job.

but when I try to:

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

(or it hangs)

and the pgbouncer logs show:

2023-09-05 14:40:26.215 UTC [7] LOG S-0x55b6fd6c9540: developer/_crunchypgbouncer@10.103.237.221:5432 new connection to server (from 10.244.0.75:38056)
2023-09-05 14:40:26.217 UTC [7] LOG S-0x55b6fd6c9540: developer/_crunchypgbouncer@10.103.237.221:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1
2023-09-05 14:40:54.845 UTC [7] LOG C-0x55b6fd6b8900: developer/(nouser)@10.244.0.40:59998 login failed: db=developer
2023-09-05 14:40:54.845 UTC [7] LOG C-0x55b6fd6b8900: developer/(nouser)@10.244.0.40:59998 closing because: no such user (age=0s)
2023-09-05 14:40:54.845 UTC [7] LOG S-0x55b6fd6c9540: developer/_crunchypgbouncer@10.103.237.221:5432 closing because: client disconnect before everything was sent to the server (age=28s)
2023-09-05 14:40:54.845 UTC [7] WARNING C-0x55b6fd6b8900: developer/(nouser)@10.244.0.40:59998 pooler error: no such user
2023-09-05 14:40:54.845 UTC [7] LOG C-0x55b6fd6b8900: (nodb)/(nouser)@10.244.0.40:60002 closing because: SSL required (age=0s)
2023-09-05 14:40:54.845 UTC [7] WARNING C-0x55b6fd6b8900: (nodb)/(nouser)@10.244.0.40:60002 pooler error: SSL required

(could the pgbouncer errors be more useless? Everything to is seems to be an SSL error.)

So… one step closer… looks like pgbouncer is rejecting the user I specified.

pgbouncer is configured as follows:

apiVersion: v1
data:
  pgbouncer-empty: ""
  pgbouncer.ini: |
    # Generated by postgres-operator. DO NOT EDIT.
    # Your changes will not be saved.

    [pgbouncer]
    %include /etc/pgbouncer/pgbouncer.ini

    [pgbouncer]
    auth_file = /etc/pgbouncer/~postgres-operator/users.txt
    auth_query = SELECT username, password from pgbouncer.get_auth($1)
    auth_user = _crunchypgbouncer
    client_tls_ca_file = /etc/pgbouncer/~postgres-operator/frontend-ca.crt
    client_tls_cert_file = /etc/pgbouncer/~postgres-operator/frontend-tls.crt
    client_tls_key_file = /etc/pgbouncer/~postgres-operator/frontend-tls.key
    client_tls_sslmode = require
    conffile = /etc/pgbouncer/~postgres-operator.ini
    ignore_startup_parameters = extra_float_digits
    listen_addr = *
    listen_port = 5432
    server_tls_ca_file = /etc/pgbouncer/~postgres-operator/backend-ca.crt
    server_tls_sslmode = verify-full
    unix_socket_dir =

    [databases]
    * = host=pg-cluster1-primary port=5432
kind: ConfigMap
metadata:
  creationTimestamp: "2023-09-04T23:59:18Z"
  labels:
    pgv2.percona.com/version: 2.2.0
    postgres-operator.crunchydata.com/cluster: pg-cluster1
    postgres-operator.crunchydata.com/role: pgbouncer
  name: pg-cluster1-pgbouncer
  namespace: default
  ownerReferences:
  - apiVersion: postgres-operator.crunchydata.com/v1beta1
    blockOwnerDeletion: true
    controller: true
    kind: PostgresCluster
    name: pg-cluster1
    uid: 2bc2d171-541f-4443-8283-eedc39ce57ab
  resourceVersion: "409939"
  uid: e7806c04-d5d2-4d70-ae77-cf8638d93a1f

with auth users coning from : /etc/pgbouncer/~postgres-operator/users.txt , which contains:

"_crunchypgbouncer" "R3QxELX;xsab<]CFhq7@dH1PKXV}Mp}n"

I’m not sure who the ‘_crunchypgbouncer’ user is or why it’s there but ok… it seems to be in there by default so there must be a reason.

The developer user has already been defined in cr.yaml (in spec.users)… so why is it not defined in pgbouncer. Is this actually working at all? For anyone?

Looking at the online docs: Custom Resource options - Percona Operator for PostgreSQL

Again the repo doc files contain a reference to this in the tutorial files : percona-postgresql-operator/docs/content/tutorial/connection-pooling.md at main · percona/percona-postgresql-operator (github.com) as follows:

PgBouncer configuration can be customized through spec.proxy.pgBouncer.config. After making configuration changes, PGO will roll them out to any PgBouncer instance and automatically issue a "reload".

There are several ways you can customize the configuration:
* spec.proxy.pgBouncer.config.users: Accepts key-value pairs that represent connection settings applied to specific users.
* spec.proxy.pgBouncer.config.files: Accepts a list of files that are mounted in the /etc/pgbouncer directory and loaded before any other options are considered using PgBouncer's include directive.

but, as before (with the databases config), there is NO specific indication of what those key-value pairs might be… What are the keys??

This is insanely frustrating… Been a week at this trying to get something that is normally incredibly easy to configure (without Percona)… pgbouncer, working…

… (some time later)

After more research… this all seems to be a CruncyData thing: Connection Pooling (crunchydata.com) … perhaps there are examples there!

… (some time later)

ok… Got it… The mistake was making my test user a SUPERUSER. Looks like superusers cannot use pgbouncer. If the user is not SUPERUSER then the CrunchyData code lets the user in… perhaps their code is doing the authenticating.

FYI anyone who’s reading… Percona is using the CrunchData PGO… so do check in thier docs for more recent info: Documentation | Enterprise PostgreSQL Support & Kubernetes Solutions | Crunchy Data

That just leaves the issue with DNS. @Slavisa_Milojkovic 's solution works but does require a bit of fiddling… So I am not marking as solved just yet.

1 Like