Enable Real Client IP Visibility in Percona PostgreSQL Behind HAProxy

Percona Monitoring and Management (PMM) PMM 3.x
1

Issue Summary

We have a Percona PostgreSQL cluster deployed behind HAProxy for load-balancing and high availability.
Currently, PostgreSQL logs and pg_stat_activity only show the HAProxy IP as the client address instead of the actual application/client IP.

This prevents us from:

  • Enforcing IP-based access control in pg_hba.conf

  • Auditing real user connections

  • Meeting security and compliance requirements

Expected Behaviour

Percona PostgreSQL should receive and record the original client IP when connections are proxied via HAProxy.

This should allow:

  • pg_stat_activity.client_addr to show the real IP

  • PostgreSQL logs to record real client IP

  • pg_hba.conf to work with real client networks

pls help how we can achieve this

2

Hi @ashishpilania18,

PostgreSQL has no mechanism to parse PROXY protocol headers, unlike Percona Server for MySQL which supports this natively via proxy_protocol_networks. When HAProxy operates in mode tcp, it opens a new TCP connection to the backend, so PG only ever sees HAProxy’s IP in pg_stat_activity and pg_hba.conf. A patch to add PROXY protocol support was submitted upstream but was returned with feedback about interaction with connection poolers and has not been resubmitted, so this limitation persists through PG 18.

The only way to get real client IPs in pg_stat_activity, PostgreSQL logs, and pg_hba.conf simultaneously is HAProxy’s transparent proxy (TPROXY) feature, which preserves the original source IP on the backend connection:

backend pgsql_backend
    mode tcp
    source 0.0.0.0 usesrc clientip
    server pg1 192.168.1.10:5432 check

This requires Linux kernel TPROXY support (xt_TPROXY), CAP_NET_ADMIN for HAProxy, and return traffic from PostgreSQL routed back through HAProxy (same L2 segment or explicit routing). It works well on bare metal but is fragile in cloud and container environments.

If you mainly need audit visibility rather than pg_hba.conf enforcement, a simpler alternative is placing pgBouncer between HAProxy and PostgreSQL with application_name_add_host = 1. This appends the connecting client’s IP to pg_stat_activity.application_name, which is enough for monitoring and log correlation but does not affect client_addr or pg_hba.conf rules.

For reference, Percona’s own PostgreSQL HA architecture guide uses standard TCP-mode HAProxy without TPROXY, reflecting the tradeoff most production deployments make.