How to limit connections in a postgres database

This is how you can restrict connections for a specific database to zero:

postgres=# alter database db01 connection limit 0;
ALTER DATABASE

Verify

SELECT datname, datconnlimit FROM pg_database WHERE datname = 'db01';
-[ RECORD 1 ]+-------------
datname      | db01
datconnlimit | 0

Set back to unlimited:

ALTER DATABASE db01 CONNECTION LIMIT -1;
ALTER DATABASE

Verify:

SELECT datname, datconnlimit FROM pg_database WHERE datname = 'db01';
-[ RECORD 1 ]+-------------
datname      | db01
datconnlimit | -1

To limit the connections for a specific user only:

 psql
psql (15.13)
Type "help" for help.

postgres=# alter user read_db01 connection limit 0;
ALTER ROLE
postgres=# alter user read_db01 connection limit -1;
ALTER ROLE
postgres=#

The current setting can be verified with:

 SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolname = 'read_db01';
      rolname      | rolconnlimit
-------------------+--------------
 read_db01         |           -1

or, list all users that does have restrictions on the number of connections:

 SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit != -1;
      rolname      | rolconnlimit
-------------------+--------------
 pganalyze         |            5
 read_db01         |            0
(2 rows)