Is there a way to get the IP address of a client virtual machine in Postgresql?

All we need is an easy explanation of the problem, so here it is.

I can get the IP address if the Postgres connection is done from the 5432 port.
Now If I connect Postgres with 6432 port means the connection comes from pgbouncer at this time I’m not getting the IP address of the machine getting localhost in the Client IP address tab from pg_stat_activity and in logs also getting the same localhost.
I have enabled log_hostname and log_line_prefix also I have added %h and %r to get the IP of the machine from which the connection happed.
My purpose is to audit/to get the address for which query or action happened from which machine

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I got IP in Postgres logs by enabling application_name_add_host in pgbouncer and app name added in log_line_prefix in postgresql.conf

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply