Connection limits - a proof of concept
From time to time someone on the mailing list asks if there's a way to limit number of connections for a given IP address, database or a user. No, currently there's nothing like that built into the core,. although it'd be pretty useful feature, especially on clusters shared by multiple clients (applications, customers, ...). Sometimes such quotas can be reasonably implemented using a connection pooler like pgbouncer or things like netfilter connlimit, but both solutions have disadvantages.
But hey! We do have a little thingies called "extensions", "hooks" and "shared libraries." Last week I hacked up a simple "proof of concept" extension that uses "client auth hook" and allows you to limit number of connections by various criteria. It's not perfect (more on that later), but it works quite fine. It's available at github and once I fix those issues I'm aware of I'll publish it on pgxn.
PS: Thanks to Magnus for pointing out the existing "client auth hook" might be useful to implement this, and TL for pointing out how unbelievably ugly kluge the original version of the extension was.
The install procedure is quite simple and is described in the README, so let's skip that.
There are two sources for the connection limits - GUC variables and rules. The GUC variables are defined in the usual postgresql.conf, while the rules are stored in a new file pg_limits.conf (in the data directory). One thing to keep in mind - the rules are not applied to superusers, so it won't lock you out.
Note: As some of you pointed out, both CREATE USER and CREATE DATABASE do have a CONNECTION LIMIT option, that can be used to limit number of connections for a user or a database. That is surely enough in many cases, but if you need to limit connections by IP address or more compleex rules, something else is needed.
GUC variables
The GUC variables define "default per-object limits" that are valid for all users, databases or IP addresses.
So for example with this setting
connection_limits.per_database = 20 connection_limits.per_user = 5 connection_limits.per_ip = 10
there won't be more than 20 connections to any database, more than 5 connections for any user or 10 connections for any IP address.
pg_limits.conf
But what if you want more complex limits, like "this user can have 5 connections to database A and 10 connections to database B"? Well, that's what pg_limits.conf is for. The format resembles pg_hba.conf, i.e. there are 4 or 5 fields
database user ip [mask] limit
and there's a special value "all" that matches all values. So for example this rule
all myuser all 10
is almost like "per_user = 10" except that the limit is applied just to "myuser." The IP mask may be specified in two ways, just like in pg_hba.conf - for example these two rules are equal
all all 127.0.0.1/32 5 all all 127.0.0.1 255.255.255.255 5
i.e. there may be at most 5 connections from localhost.
Note: You could specify a hostname instead of the IP (e.g. "localhost") - the rule will be parsed, but not checked. This is one of the things I need to sort out.
GUC variables vs. pg_limits.conf
It's possible to combine those two things - define a default value and then override it for some of the users. The rule always take precedence, so for example by combining this GUC
connection_limits.per_user = 10
and these two rules in pg_limits.conf
all userA all 20 all userB all 5
the default per-user limit is 10. The only two exceptions are "userA" who is allowed to open 20 connections and "userB" who is allowed to open 5 connections. The only condition for this override to happen is that the rule must be "per-user" i.e. the only value specified must be the username. And similarly for databases and IP addresses ...
Current state of connection limits
If you want to see what is the current state (which limits are already reached etc.), use the connection_limits view
SELECT * FROM connection_limits;
Conclusion
I've started to write this extension less than a week ago, and at the beginning it was rather a proof of concept. Anyway most of the issues are already fixed and I expect to publish it on pgxn soon. One of the remaining issues is how to handle hostnames in rules. Currently the hostname is parsed but not checked properly (the rule always fail). Not sure how to solve this - any ideas?
Thanks for sharing. it's pretty useful feature mostly for cloud environment.
on the other topic, do you know how can I limit resources per connection?
It's much less flexible that what you've done here, but you can also set a connection limit on login roles by using CREATE/ALTER ROLE ... WITH CONNECTION LIMIT ...--see http://www.postgresql.org/docs/9.1/interactive/sql-createrol e.html and http://www.postgresql.org/docs/9.1/interactive/sql-alterrole .html.
Actually you can limit the number of connections a particular user is allowed to create:
ALTER USER my_user WITH CONNECTION LIMIT 5;
pgbouncer
http://pgbouncer.projects.postgresql.org/doc/config.html
http://pgbouncer.projects.postgresql.org/doc/config.html#_se ction_databases
proxy-db -- per host/user/dbname -- pool_size and connect_query
--
http://pgbouncer.projects.postgresql.org/doc/
Denish, there are things like work_mem and temp_buffers. It's not a hard limit but I don't think you can do much more.
JH, Thom Brown: Thanks, I was not aware of this option (shame on me). That certainly can be used as a solution in many cases. Anyway the original question was whether we can limit the number of connections per query, and the extension allows to use more complex rules (per database, per database and user etc.).
Yup, that's one of the options I mentioned. This approach has some disadvantages though - for example all the pgbouncer users have access to all databases with the same pool size. Which is not acceptable when those databases belong to different customers etc. You could solve this by using multiple pgbouncer instances (one for each customer), but that may be a bit too complex. Plus it does not really limit the connections per IP, you'd have to do that using netfilter or something.
there are default_pool_size and pool_size
http://pgbouncer.projects.postgresql.org/doc/config.html .
--
central pool conf:
proxydb_application1 = dbname=mydb pool_size=10
proxydb_application2 = dbname=mydb pool_size=5
admin_hole = dbname=mydb pool_size=1
--
app1 => localhost pool (max_client_conn=200) => central pool
app2 => localhost pool (max_client_conn=100) => central pool
Yes, there are default_pool_size and pool_size, but that's not what I was pointing out.
1) Once you're logged in to pgbouncer, you may access all databases defined in that pgbouncer instance. The users may not be able to list the databases, but that's security by obscurity.
2) Yes, the pool sizes are per database (defined pgbouncer), not per user. You could define two databases, one for each user - but the user may access multiple databases (see previous point). It will work if you trust the users to use the proper database, not in a shared environment.