When I use systemd socket in combination with auth_type = hba and auth_hba_file IPv4 not OK

[ntap-jbo]

When I use socket in combination with auth_type = hba and auth_hba_file IPv4 not OK.

I will use the easiest explainable example:
The debian pgbouncer apt.postgresql.org package Version 1.21.0-1.pgdg120+1 (bookworm i.e. debian 12).

I have attacheched both the /lib/systemd/system/pgbouncer.socket and the /lib/systemd/system/pgbouncer.service files
pgbouncer.tar.gz

In pg_hba.conf I've entered (auth_hba_file).

host    postgres   test    127.0.0.1/32   md5
host    postgres   test    192.168.122.125/32   md5
host    postgres   test    ::1/128        md5
host    postgres   test    fe80::5054:ff:fe69:70a9/128        md5

With the standard pgbouncer.service file I can connect to both the IPv4 and IPv6 Addresses without a problem.

As soon as I enable "Requires=pgbouncer.socket" i.e. uncomment it.

systemctl daemon-reload
systemctl restart pgbouncer

The systemd socket is used. And I only can connect via the IPv6 IP addresses.

I get:

psql -h 127.0.0.1 -U test -p 6432 postgres
psql: error: connection to server at "127.0.0.1", port 6432 failed: FATAL:  login rejected

psql -h 192.168.122.125 -U test -p 6432 postgres
psql: error: connection to server at "192.168.122.125", port 6432 failed: FATAL:  login rejected

psql -h ::1 -U test -p 6432 postgres
Password for user test:

psql -h fe80::5054:ff:fe69:70a9%enp1s0 -U test -p 6432 postgres
Password for user test:

Without "Requires=pgbouncer.socket" I can sign in via IPv4 and IPv6.

psql -h 127.0.0.1 -U test -p 6432 postgres
Password for user test:

psql -h 192.168.122.125 -U test -p 6432 postgres
Password for user test:

psql -h ::1 -U test -p 6432 postgres
Password for user test:

psql -h fe80::5054:ff:fe69:70a9%enp1s0 -U test -p 6432 postgres
Password for user test:

The reason why I want to use a socket is because I want to have two bgbouncers listing on the same port and IP address.
It's not in this example, as the issue is easier to produce like this.

[JelteF]

The reason why I want to use a socket is because I want to have two bgbouncers listing on the same port and IP address. It's not in this example, as the issue is easier to produce like this.

The recommended way to do that is to use pgbouncer it's so_reuseport option: https://www.pgbouncer.org/config.html#so_reuseport

Regarding your specific problem though, I'm not entirely sure how systemd its socket forwarding works. An initial thought would be that you would need to add the following to your hba file, because now the connection comes over the unix socket instead of over TCP:

local    postgres   test                      md5

Also, the two ListenStream entries in your pgbouncer.socket file seem like it could cause some problems, usually only one is allowed. Did you look at the systemd logs?

[ntap-jbo]

I need to use IP addresses as I need to connect from external IP addresses to pgbouncer.

I've looked into the logging.
I see entries like this.

2023-12-28 15:24:12.987 CET [616] WARNING C-0x557721deced0: postgres/test@[::ffff:127.0.0.1]:36278 pooler error: login rejected

When I change my pg_hba.conf to

host    postgres   test    ::ffff:127.0.0.1/128   md5
host    postgres   test    ::ffff:192.168.122.125/128   md5
host    postgres   test    ::1/128        md5
host    postgres   test    fe80::5054:ff:fe69:70a9/128        md5

I can login i.e. it's working as expected.

I don't know if it's the correct way to add the IPv4 entries with a ::ffff: prefix?
I would prefer it to be added without the ::ffff: prefix.

[JelteF]

Okay, good that you got it working. Then I guess systemd forces use of ipv6 for its socket handling. It's called an IPv4-mapped IPv6 address: https://www.ibm.com/docs/en/zos/2.2.0?topic=addresses-ipv4-mapped-ipv6

Closing this since there's nothing actionable on PgBouncer its side.

[petere]

If you carefully read the descriptions of the systemd unit options ListenStream and BindIPv6Only, then this appears to be the expected behavior. Most people just don't realize it if they are not using HBA.

If you don't want to change your pg_hba.conf as you have described, you could probably also write the system socket configuration like

ListenStream=v.w.x.y:6432
ListenStream=[x]:6432
BindIPv6Only=ipv6-only

Maybe there is also an argument to be made that these mapped addresses should match IPv4 pg_hba.conf entries? (Should check with PostgreSQL core.)

[JelteF]

Okay, re-opening to see if we should change our HBA logic and have ipv4 rules match these addresses.

[ntap-jbo]

The ListenStream and BindIPv6Only=ipv6-only solved the issue

I've used:

ListenStream=0.0.0.0:6432
ListenStream=[::]:6432
BindIPv6Only=ipv6-only

I can now use the pg_hba.conf with IPv4 entries, IPv4 format and IPv6 (native IPv6 format).