HAProxy transparent proxy mode

[Falseclock]

What is wrong with my config (below)?

Tried these tutorials
https://www.haproxy.com/blog/howto-transparent-proxying-and-binding-with-haproxy-and-aloha-load-balancer/
https://serverfault.com/questions/980615/haproxy-unable-to-switch-to-transparent-mode

but still no success, can't conect to pgbouncer

[Falseclock]

Here is my configuration

lsmod | grep -i tproxy

[TESTING]root@od2pdb03:/etc/haproxy# lsmod | grep -i tproxy
xt_TPROXY              20480  0
nf_tproxy_ipv6         20480  1 xt_TPROXY
nf_tproxy_ipv4         20480  1 xt_TPROXY
nf_defrag_ipv6         24576  2 xt_socket,xt_TPROXY
nf_defrag_ipv4         16384  2 xt_socket,xt_TPROXY
x_tables               53248  5 nft_compat,xt_socket,xt_TPROXY,ip_tables,xt_mark

iptables -t mangle -vL

[TESTING]root@od2pdb03:/etc/haproxy# iptables -t mangle -vL
Chain PREROUTING (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination
 924K  397M DIVERT     tcp  --  any    any     anywhere             anywhere             socket

Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination

Chain OUTPUT (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination

Chain POSTROUTING (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination

Chain DIVERT (1 references)
 pkts bytes target     prot opt in     out     source               destination
 924K  397M MARK       all  --  any    any     anywhere             anywhere             MARK set 0x1
 924K  397M ACCEPT     all  --  any    any     anywhere             anywhere

ip rule

[TESTING]root@od2pdb03:/etc/haproxy# ip rule
0:      from all lookup local
32764:  from all fwmark 0x1 lookup 100
32766:  from all lookup main
32767:  from all lookup default

ip route show table 100

[TESTING]root@od2pdb03:/etc/haproxy# ip route show table 100
local default dev lo scope host

sysctl -p

[TESTING]root@od2pdb03:/etc/haproxy# sysctl -p
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv6.conf.lo.disable_ipv6 = 1
net.ipv4.ip_nonlocal_bind = 1
net.ipv4.ip_forward = 1
net.ipv4.ip_local_port_range = 10000 65535
net.core.netdev_max_backlog = 10000
net.ipv4.tcp_max_syn_backlog = 8192
net.core.somaxconn = 65535
net.ipv4.tcp_tw_reuse = 1
vm.overcommit_memory = 2
vm.swappiness = 1
vm.min_free_kbytes = 102400
vm.dirty_expire_centisecs = 1000
vm.dirty_background_bytes = 67108864
vm.dirty_bytes = 536870912
vm.zone_reclaim_mode = 0
kernel.numa_balancing = 0
kernel.sched_autogroup_enabled = 0
net.ipv4.ip_forward = 1
net.ipv4.ip_nonlocal_bind = 1
net.ipv4.conf.default.send_redirects = 1
net.ipv4.conf.all.accept_redirects = 1
net.ipv4.conf.default.rp_filter = 2
net.ipv4.conf.default.accept_source_route = 1

cat haproxy.cfg

[TESTING]root@od2pdb03:/etc/haproxy# cat haproxy.cfg
global
    maxconn 100000
    log /dev/log    local0
    log /dev/log    local1 notice
    chroot /var/lib/haproxy
    stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
    stats timeout 30s
    #user haproxy
    #group haproxy
    daemon

defaults
    mode               tcp
    log                global
    retries            2
    timeout queue      5s
    timeout connect    5s
    timeout client     60m
    timeout server     60m
    timeout check      15s

listen stats
    mode http
    bind 172.27.137.19:7000
    stats enable
    stats uri /

listen master
    bind 172.27.137.8:5432 transparent
    maxconn 10000
    option tcplog
    option httpchk OPTIONS /master
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions
    server od2pdb01 172.27.137.17:6432 check port 8008
    server od2pdb02 172.27.137.18:6432 check port 8008
    server od2pdb03 172.27.137.19:6432 check port 8008
    source 0.0.0.0 usesrc clientip

listen replicas
    bind 172.27.137.8:5433
    maxconn 10000
    option tcplog
    option httpchk OPTIONS /replica
    balance roundrobin
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
    server od2pdb01 172.27.137.17:6432 check port 8008
    server od2pdb02 172.27.137.18:6432 check port 8008
    server od2pdb03 172.27.137.19:6432 check port 8008

grep -i TPROXY /boot/config-5.10.0-20-amd64

[TESTING]root@od2pdb03:/etc/haproxy# grep -i TPROXY /boot/config-5.10.0-20-amd64
CONFIG_NFT_TPROXY=m
CONFIG_NETFILTER_XT_TARGET_TPROXY=m
CONFIG_NF_TPROXY_IPV4=m
CONFIG_NF_TPROXY_IPV6=m

haproxy -vv

[TESTING]root@od2pdb03:/etc/haproxy# haproxy -vv
HA-Proxy version 2.2.9-2+deb11u3 2022/03/10 - https://haproxy.org/
Status: long-term supported branch - will stop receiving fixes around Q2 2025.
Known bugs: http://www.haproxy.org/bugs/bugs-2.2.9.html
Running on: Linux 5.10.0-20-amd64 #1 SMP Debian 5.10.158-2 (2022-12-13) x86_64
Build options :
  TARGET  = linux-glibc
  CPU     = generic
  CC      = gcc
  CFLAGS  = -O2 -g -O2 -ffile-prefix-map=/build/haproxy-UmgAPI/haproxy-2.2.9=. -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -Wall -Wextra -Wdeclaration-after-statement -fwrapv -Wno-address-of-packed-member -Wno-unused-label -Wno-sign-compare -Wno-unused-parameter -Wno-clobbered -Wno-missing-field-initializers -Wno-stringop-overflow -Wno-cast-function-type -Wtype-limits -Wshift-negative-value -Wshift-overflow=2 -Wduplicated-cond -Wnull-dereference
  OPTIONS = USE_PCRE2=1 USE_PCRE2_JIT=1 USE_OPENSSL=1 USE_LUA=1 USE_ZLIB=1 USE_SYSTEMD=1
  DEBUG   =

Feature list : +EPOLL -KQUEUE +NETFILTER -PCRE -PCRE_JIT +PCRE2 +PCRE2_JIT +POLL -PRIVATE_CACHE +THREAD -PTHREAD_PSHARED +BACKTRACE -STATIC_PCRE -STATIC_PCRE2 +TPROXY +LINUX_TPROXY +LINUX_SPLICE +LIBCRYPT +CRYPT_H +GETADDRINFO +OPENSSL +LUA +FUTEX +ACCEPT4 -CLOSEFROM +ZLIB -SLZ +CPU_AFFINITY +TFO +NS +DL +RT -DEVICEATLAS -51DEGREES -WURFL +SYSTEMD -OBSOLETE_LINKER +PRCTL +THREAD_DUMP -EVPORTS

Default settings :
  bufsize = 16384, maxrewrite = 1024, maxpollevents = 200

Built with multi-threading support (MAX_THREADS=64, default=8).
Built with OpenSSL version : OpenSSL 1.1.1k  25 Mar 2021
Running on OpenSSL version : OpenSSL 1.1.1n  15 Mar 2022
OpenSSL library supports TLS extensions : yes
OpenSSL library supports SNI : yes
OpenSSL library supports : TLSv1.0 TLSv1.1 TLSv1.2 TLSv1.3
Built with Lua version : Lua 5.3.3
Built with network namespace support.
Built with zlib version : 1.2.11
Running on zlib version : 1.2.11
Compression algorithms supported : identity("identity"), deflate("deflate"), raw-deflate("deflate"), gzip("gzip")
Built with transparent proxy support using: IP_TRANSPARENT IPV6_TRANSPARENT IP_FREEBIND
Built with PCRE2 version : 10.36 2020-12-04
PCRE2 library supports JIT : yes
Encrypted password support via crypt(3): yes
Built with gcc compiler version 10.2.1 20210110
Built with the Prometheus exporter as a service

Available polling systems :
      epoll : pref=300,  test result OK
       poll : pref=200,  test result OK
     select : pref=150,  test result OK
Total: 3 (3 usable), will use epoll.

Available multiplexer protocols :
(protocols marked as <default> cannot be specified using 'proto' keyword)
            fcgi : mode=HTTP       side=BE        mux=FCGI
       <default> : mode=HTTP       side=FE|BE     mux=H1
              h2 : mode=HTTP       side=FE|BE     mux=H2
       <default> : mode=TCP        side=FE|BE     mux=PASS

Available services : prometheus-exporter
Available filters :
        [SPOE] spoe
        [COMP] compression
        [TRACE] trace
        [CACHE] cache
        [FCGI] fcgi-app

[vitabaks]

HAProxy transparent proxy mode is currently not supported by this playbook. This advanced HAProxy setup is outside the scope of this project.

I will try to find time to solve this problem, but for now you need to work on it yourself.

[honibis]

Hi,
Just a newbea question:
I want to change HAProxy configuration with ports directing to 5432 instead of 6432 to disable pgbouncer. However anytime i edit the haproxy.cfg file it reverts back in couple seconds. May be patroni is auto updating but i could not find any relavent configuration. How can i edit haproxy.cfg or disable pgbouncer wtihout reinstalling the cluster?

[honibis]

Ok, figured out i think.
Changed installpgbouncer to false in main.yml then did run
ansible-playbook config_pgcluster.yml -kK
May be i could use tags to avoid unnecesary updates, still looks like it works.

[Falseclock]

@honibis you have to edit confd configuration

[Falseclock]

@vitabaks without transparent mode hba becomes useless cause every connection goes from 127.0.0.1
haproxy -> pg_bouncer -> postgres

Any suggestions?

[vitabaks]

Yes. In HAProxy mode, there is no need to manage rules in pg_hba.conf

[Falseclock]

Yes. In HAProxy mode, there is no need to manage rules in pg_hba.conf

and how to solve the issue of security and access control? Do you have any solution?

[vitabaks]

Firewall.

[Falseclock]

Firewall.

What about
[Type B] PostgreSQL High-Availability only?

never tried such type.

[vitabaks]

When using HAProxy (Type-A) or Pgbouncer (all types, optional) You will always receive local connections (127.0.0.1) on the database server, because this services does not forward clients IP Addresses.

What about [Type B] PostgreSQL High-Availability only?

You will see the IP addresses of the clients directly in Postgres if your setup does not use PgBouncer (pgbouncer_install: false).