Where to define the maximum number of connections in Postgres?

Question

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!

Ferric

1) is clear from the docs (absolute limit for the whole server). And the rest depends on your use case. – Rightful

dhke: "And the rest depends on your use case". Depends on what? Any hint? – Ferric

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. – Rightful

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? – Ferric

Note that there's also superuser_reserved_connections. datconnlimit and rolconnlimit are not enforced for superusers. – Rightful

Answer 1

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.

Lampley

"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? – Ferric

If you don't want to limit the number of connections per user, leave rolconnlimit at -1. – Lampley