restrict the number of connections in postgres usi

Question

Is it possible to restrict the number of connections in postgres, using the ip address of machine, since i'm not able to find which ip address is tagged to which user / role.

Answer 1

implicitly you can do it with pgbouncer - at least 1.7 version supports hba file, so you can bond db+user+ip and set limit for db+user in ini file. This way limiting connections to IP or network.

explicitly you can try using HAProxy's or just IPTABLES (I think prefered way)

lastly you can write some monkey job that will check number of connection from pg_stat_activity.client_addr and select pg_terminate_backend(pid) from pg_stat_activity where client_addr = 'x.x.x.x' order by query_started desc limit 10 (to keep first 10 connections), but this way is an awful wheel for such task I'd say.