restrict the number of connections in postgres using an ip address

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.

You can limit number of connections by setting the max_connections = 100 parameter in the config file. You can use something like pg_pool to limit incoming connections also. http://www.pgpool.net/docs/pgpool-II-3.5.4/doc/pgpool-en.html — VynlJunkie, Sep 12 '17 at 13:20

score 1 · Accepted Answer · answered Sep 13 '17 at 07:55

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.

restrict the number of connections in postgres using an ip address

1 Answers1

Linked