Improve heuristic to check if PG is local

[gozdal]

If PostgreSQL is listening on loopback interface, but not necessarily on 127.0.0.1 then https://github.com/rapidloop/pgmetrics/blob/1d876098d4baa30d40aed48cf85c30f70ce7c85b/collector/collect.go#L625 fails to detect that PG is actually local. E.g. if you have /etc/hosts with

127.0.0.1 localhost
127.0.1.1 pg

and you configure PG with

listen_addresses = 'pg'

then PG will listen on 127.0.1.1 but the TCP connection to 127.0.1.1 will appear from 127.0.0.1, i.e. inet_client_addr() will be 127.0.0.1 and inet_server_addr() will be 127.0.1.1.

Maybe a special case for 127.0.0.0/8 would be useful here?

[mdevan]

Yes, that sounds reasonable. I suppose changing the current query from:

SELECT COALESCE(inet_client_addr() = inet_server_addr(), TRUE)

to:

SELECT COALESCE(inet_client_addr() = inet_server_addr(), TRUE) OR (inet_server_addr() << '127.0.0.0/8')

should do the trick. What do you think?

[gozdal]

I wonder if there is some weird networking scenario (DNAT?) where inet_server_addr() is 127.0.0.0/8 but the client is actually connecting from outside(i.e. inet_client_addr() is not 127.0.0.0/8). WDYT about

SELECT COALESCE(inet_client_addr() = inet_server_addr(), TRUE) OR (inet_server_addr() << '127.0.0.0/8' AND inet_client_addr() << '127.0.0.0/8')

[mdevan]

That should reduce the false positives. Might still fail for cases like when pgmetrics connects remotely to a pooler/proxy that in turn connects to a locally-running postgres over a localhost interface.

Anyway, last one above should be good enough as a heuristic I feel. We can always add a --force-local={true|false} or some such flag later if required.

[mdevan]

Added in commit 581da00.