Forward "real" `client_hostname` and `client_addr` information from pgBouncer to Postgres

[CC-Hsu]

Hi, Team,

Currently when using pgBouncer between various application servers and PGSQL server, the information shown in client_hostname and client_addr columns of pg_stat_activity shows those of pgBoucner service.

When a PGSQL server is setup with pgbouncer, all client hostnames and client IP addresses are "masked" by pgBouncer.

Most of tracing/monitoring tools are corporate with PGSQL closely but not with pgBouncer; this kind of "source address information fencing" makes these tools less useful.

Currently, pgBouncer provides a parameter application_name_add_host to attach client information in application_name field, but it is not friendly for querying pg_stat_activity.

Besides, application_name_add_host information is not directly recorded in PGSQL logs with %r/%h escape but by %a, which is less ideal for log processing.

Is it possible to improve pgBouncer to achieve the following feature? 1) Forward real client's hostname and IP address information to PGSQL, and turn on/off by a parameter. 2) A parameter to append "(from pgBouncer)" in application_name to notify that the session is forward from pgBouncer.

Best Regards.

[petere]

The second item would be possible. However, you can already get a similar effect from application_name_add_host, so I'm not sure that it's worth having another setting to tweak application_name in a different way.

The first item is not in PgBouncer's hands, because PostgreSQL logs the origin of the connection as it sees it. There are discussions to implement the PROXY protocol to handle this, but this requires changes on the PostgreSQL side first.

[CC-Hsu]

Hi, petere,

Thanks a lot for your analysis on implementation prerequisite.

For the main purpose of this feature request (recognizing the real client in PGSQL when utilizing pgBouncer), it may be also feasible if pgBouncer can provide per-database application_name_tag setting in pgBouncer [database] section.

A visual example might like below.

[databases]
foodb1 = host=localhost port=5432 application_name_tag="conn from AP server1"
foodb2 = host=localhost port=5432 application_name_tag="conn from AP server2"

If such feature can be implemented, users can also utilize it to 'classify' client servers sources based on such setting. 😊

Besides, this setup can co-work with pgBouncer HBA file for more precise control.

Best Regards.

[MichaelDBA]

Any movement on this? I want this feature so I can tell from pg_stat_activity if connections are coming from a pgbouncer somewhere.

[bomuva]

I got a number of "authentication failed" in pgbouncer error log. I suspect it is a single client but I cannot determine its IP. How can I get the IP of the client when uthentication isn't succeed? The log contains server IP only