9

I want to limit the number of users per database in a multi tenant environment. But there are three levels of max connections and I should be grateful for any advice.

Level 1 Entire Server

By editing the config for Postgresql I can set the Max connection for the all databases on a server

postgresql.conf = max_connections = 100

Level 2 Per database

I can select and set the database connection limit per database:

SELECT datconnlimit FROM pg_database

Level 3 Per role

I can select and set the role connection limit per "user":

SELECT rolconnlimit FROM pg_roles

My questions are

  1. If the max_connections in postgresql.conf is 100, will it be max connections of all databases regardless database and role settings? e.g. 100 databases can only have 1 connection each simultaneously?

  2. Where is the best place to limit max connections. At the database level or at the role level?

  3. Anything other to be considered?

TIA for any advice or clue!

Hearen
  • 7,420
  • 4
  • 53
  • 63
sibert
  • 1,968
  • 8
  • 33
  • 57
  • 2
    1) is clear [from the docs](https://www.postgresql.org/docs/current/static/runtime-config-connection.html#GUC-MAX-CONNECTIONS) (absolute limit for the whole server). And the rest depends on your use case. – dhke Aug 22 '16 at 13:34
  • dhke: "And the rest depends on your use case". Depends on what? Any hint? – sibert Aug 22 '16 at 15:20
  • Are there actually multiple DB users? (e.g. big web application with single DB user), Do you have multiple databases? Are those databases used by different users? In (common) case with single user, single DB, `max_connections`, `datconnlimit`, and `rolconnlimit` are all the same, governed by the lowest one. – dhke Aug 22 '16 at 15:29
  • Yes, there is multiple databases per server and normally one user per database. So does this mean that I can set _rolconnlimit_ to 10 the role and _datconnlimit_ to -1 on the database. And the max connections is handled by the role? Then the postgres admin could connect to the database without problem? – sibert Aug 22 '16 at 17:38
  • Note that there's also [`superuser_reserved_connections`](https://www.postgresql.org/docs/9.2/static/runtime-config-connection.html). `datconnlimit` and `rolconnlimit` are not enforced for superusers. – dhke Aug 22 '16 at 18:17

1 Answers1

5

  1. max_connections minus superuser_reserved_connections is the maximum for the sum of all non-superuser connections to all databases in the cluster.

  2. If you want to limit the number of users per database, setting the limit on the database seems like the obvious choice, right?

  3. If you end up setting max_connections to a high value, consider using a connection pool instead.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • "If you want to limit the number of users per database, setting the limit on the database seems like the obvious choice, right?" So the role `rolconnlimit` has to be set higher or -1 than the `datconnlimit` to ensure access? – sibert Aug 23 '16 at 04:50
  • 2
    If you don't want to limit the number of connections per user, leave `rolconnlimit` at `-1`. – Laurenz Albe Aug 23 '16 at 11:03