Identifying client IPs and limiting heavy queries for Patroni PostgreSQL behind HAProxy

Problem:

Production PostgreSQL clusters (PostgreSQL community edition 15.17) are running under Patroni (v3.3.2) with HAProxy (2.6.21) in front. All client connections go through HAProxy using virtual IPs and virtual ports; on the PostgreSQL side the reported client address is the HAProxy VIP rather than the originating physical client IP. The operations team needed a reliable way to map database users to their physical IPs and to limit or control users submitting heavy/long-running queries.

Observables reported at intake: clients appear in PostgreSQL as the HAProxy virtual IP; existing logs and metrics did not provide a clear mapping from DB sessions to originating client IPs; there was no centralized plan to throttle or cap resource-hungry queries per role or session.

Process:

Step 1: Confirm inputs and collect artifacts

Received the initial symptom description and asked for artifacts required to correlate layers: HAProxy configuration and logs, PostgreSQL configuration and logs (postgresql.conf, pg_hba.conf, server logs), Patroni configuration, and a short topology description (whether HAProxy and PostgreSQL run on same or separate hosts and whether a pooler exists). This established the necessary data scope to prove whether client IPs were being lost at the proxy layer or not captured by DB logging.

Step 2: Review HAProxy logs and runtime logging format

Inspected the supplied HAProxy logs and haproxy.cfg. The log format in use did not include the raw client address fields (%ci/%cp) and backend server lines were configured as plain TCP backends without any mechanism to forward original client metadata. That explained why the PostgreSQL node saw the proxy VIP as the client address: HAProxy was terminating the TCP connection and not preserving the originating IP in any downstream-visible form.

Step 3: Validate PostgreSQL logging and session visibility

Opened postgresql.conf and the server logs from the primary. log_connections was enabled but the log_line_prefix did not contain the remote host token that would help correlation. pg_stat_activity showed client_addr equal to the HAProxy VIP. No connection pooler was present in front of PostgreSQL to interpret or re-inject proxy metadata. This confirmed that solving the visibility issue required changes at the proxy/logging layer or insertion of a proxy-aware pooling layer.

Step 4: Evaluate integration options and operational constraints

Reviewed Patroni topology and configuration management notes. Confirms HAProxy and PostgreSQL are on separate hosts and configuration files are not managed by immutable cloud images, so controlled config changes are safe. Also verified there was no existing pgbouncer or other pooler to accept PROXY protocol. This mattered because PostgreSQL itself was not receiving the original client IP from HAProxy under the current topology; therefore two practical approaches were considered: (A) forward the client IP via HAProxy logs and correlate connections by timestamp/session to map IP→DB user; (B) enable a proxy-protocol-aware path (HAProxy send-proxy → pooler that understands PROXY → PostgreSQL) so the server sees the original IP directly.

Step 5: Implemented logging and minimal server-side changes for rapid visibility

As the initial, low-risk change, HAProxy was reconfigured to emit detailed TCP logs (option tcplog and a log-format containing %ci:%cp and %si:%sp) so each client TCP session recorded the originating IP and port. PostgreSQL logging was adjusted to include the remote host token in log_line_prefix and duration logging was enabled. pg_stat_statements was enabled for query visibility. This provided immediate ability to correlate HAProxy connection entries with PostgreSQL session start/duration entries and identify heavy query origins without changing connection flow.

Step 6: Implemented per-role resource controls and introduced A/B pooler path

Concurrently implemented server-side user controls: ALTER ROLE SET statement_timeout and per-role CONNECTION LIMITs to cap runaway statements and concurrent sessions. pg_stat_statements reports were used to identify the top long-running queries to tune indexes and statement_timeout values. For a deterministic long-term solution, a connection-pooling path using a pooler (configured to accept HAProxy’s PROXY protocol) was deployed incrementally; HAProxy server lines were prepared for send-proxy and the pooler was configured to preserve the original client IP so PostgreSQL could directly observe client_addr. This final change completed the mapping from physical client IP → PostgreSQL session and enabled the role-level throttles to apply to true client sessions.

Solution:

Two changes were applied: (1) HAProxy logging was reconfigured to include client IP/port and timestamps, and PostgreSQL logging was adjusted (log_line_prefix and duration logging) and pg_stat_statements enabled, enabling immediate correlation of client IPs to DB users from logs; (2) role-level resource controls were enforced in PostgreSQL (statement_timeout and CONNECTION LIMIT) and a connection pooler was introduced as an optional path while HAProxy server lines were prepared for PROXY protocol. Together these changes allow accurate attribution of sessions to originating physical IPs and deterministic control of heavy queries.

Architecturally, forwarding client metadata in HAProxy logs provides immediate, non-invasive visibility, and introducing a PROXY-aware pooler makes the client address visible to PostgreSQL for operational tooling; role-level settings in PostgreSQL enforce resource constraints at the database layer.

Conclusion:

After rolling out detailed HAProxy logs, PostgreSQL logging adjustments, pg_stat_statements, role-level timeouts and connection limits, and an optional PROXY-aware pooling path, the environment gained reliable mapping from physical client IPs to DB sessions and deterministic controls to limit heavy queries. Operationally this reduced time-to-identify for problematic clients, allowed targeted remedial actions (timeouts or connection caps), and lowered the incidence of long-running queries impacting primary throughput.