There are three level max connections at postgresql, include server level max connections, per databases and per user role max connections.

1). Server max connections.

SHOW max_connections;
 max_connections
-----------------
 100
(1 row)

2). database max connections.

Default is -1 which means there is no max connections limit to the database, but it could be set during create database and alter database.

ALTER DATABASE testdb CONNECTION LIMIT 10;

Below shows how to check the max connections per database, e.g. it’s 10 for testdb

SELECT datname, datconnlimit FROM pg_database;
  datname  | datconnlimit
-----------+--------------
 postgres  |           -1
 compose   |           -1
 template1 |           -1
 template0 |           -1
 testdb    |           10
(5 rows)

3). User Role max connections.

Default is -1 which means there is no max connections limit for the user role. However, it could be set during create user or alter user.

ALTER USER testuser WITH CONNECTION LIMIT 2;

Below shows how to check the max connections per user role, e.g. it’s 2 for testuser

SELECT rolname, rolconnlimit FROM pg_roles;
       rolname       | rolconnlimit
---------------------+--------------
 pg_signal_backend   |           -1
 admin               |           -1
 user-binder         |           -1
 testuser            |            2