The maximum number of connections that PostgreSQL will allow, is controlled by the max_connections parameter. By default this allows a maximum of one hundred connections:

postgres=# show max_connections;
 max_connections 
-----------------
 100
(1 row)

In reality not one hundred connections are allowed for normal users, because some of those connections are reserved for superusers. This is controlled by the superuser_reserved_connections parameter which, by default, is limited to three:

postgres=# show superuser_reserved_connections;
 superuser_reserved_connections 
--------------------------------
 3
(1 row)

This means, that 97 connections are available to “normal” users. What happens if we set amount of available connections to one? With the default configuration of three (for superuser_reserved_connections) this seems to be somehow weird. Anyway, lets do it and see what happens:

postgres@debian11pg:/home/postgres/ [150] psql -c "alter system set max_connections=1" 
ALTER SYSTEM
postgres@debian11pg:/home/postgres/ [150] pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....postgres: superuser_reserved_connections (3) must be less than max_connections (1)
 stopped waiting
pg_ctl: could not start server
Examine the log output.

So, even if PostgreSQL allows us to set this, the instance will not start afterwards. Removing this configuration will allow us to start the instance again:

postgres@debian11pg:/home/postgres/ [150] sed -i '/^max_connections/d' $PGDATA/postgresql.auto.conf
postgres@debian11pg:/home/postgres/ [150] psql -l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)

Are configuration which does make more sense is this:

postgres@debian11pg:/home/postgres/ [150] psql -c "alter system set max_connections=4"
postgres@debian11pg:/home/postgres/ [150] pg_ctl restart
postgres@debian11pg:/home/postgres/ [150] psql -c "show max_connections"
 max_connections 
-----------------
 4
(1 row)

This will allow one “normal” connection and three superuser connections, which easily can be verified:

postgres@debian11pg:/home/postgres/ [150] psql -c "create user u with password 'u' login"
CREATE ROLE
postgres@debian11pg:/home/postgres/ [150] psql -U u postgres &
[1] 16639
postgres@debian11pg:/home/postgres/ [150] psql -U u postgres &
[2] 16641
17:20:32 postgres@debian11pg:/home/postgres/ [150] psql: error: connection to server on socket "/tmp/.s.PGSQL.5442" failed: FATAL:  remaining connection slots are reserved for no

Super user connections do still work, of course:

postgres@debian11pg:/home/postgres/ [150] psql
psql (15.0)
Type "help" for help.

postgres=# select count(*) from pg_stat_activity

This is how much control you have around connections up to PostgreSQL15. With PostgreSQL 16, you’ll have an additional option because a new parameter and a new role have been introduced. The new role is called pg_use_reserved_connections:

postgres=# select version();
                                               version                                                
------------------------------------------------------------------------------------------------------
 PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

postgres=# \du *reserved*
                     List of roles
          Role name          |  Attributes  | Member of 
-----------------------------+--------------+-----------
 pg_use_reserved_connections | Cannot login | {}

postgres=#

The new parameter is called reserved_connections, and it is disabled by default:

postgres=# \dconfig *reserved*
    List of configuration parameters
           Parameter            | Value 
--------------------------------+-------
 reserved_connections           | 0
 superuser_reserved_connections | 3
(2 rows)

Granting that role to a user, setting the parameter and restarting the instance will give you the new feature:

postgres=# create user u with login password 'u';
CREATE ROLE
postgres=# create user x with login password 'x';
CREATE ROLE
postgres=# grant pg_use_reserved_connections to u;
GRANT ROLE
postgres=# alter system set max_connections = 5;
ALTER SYSTEM
postgres=# alter system set reserved_connections = 1;
ALTER SYSTEM
postgres=# \! pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-01-22 06:46:41.828 CET - 1 - 17283 -  - @ - 0LOG:  redirecting log output to logging collector process
2023-01-22 06:46:41.828 CET - 2 - 17283 -  - @ - 0HINT:  Future log output will appear in directory "pg_log".
 done
server started
postgres=# select 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# \dconfig *connections*
    List of configuration parameters
           Parameter            | Value 
--------------------------------+-------
 log_connections                | off
 log_disconnections             | off
 max_connections                | 5
 reserved_connections           | 1
 superuser_reserved_connections | 3
(5 rows)

With this configuration user “x” will only be able to create one connection. Any additional connection request will fail:

postgres@debian11pg:/home/postgres/ [pgdev] psql -U x postgres &
[2] 17310
postgres@debian11pg:/home/postgres/ [pgdev] psql (16devel)
Type "help" for help.

[2]+  Stopped                 psql -U x postgres
postgres@debian11pg:/home/postgres/ [pgdev] psql -U x postgres &
[3] 17312
postgres@debian11pg:/home/postgres/ [pgdev] psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  remaining connection slots are reserved for roles with privileges of pg_use_reserved_connections

[3]   Exit 2                  psql -U x postgres

User “u” will be able to create one connection, which comes from the reserved connections. Any additional connection request will fail as well, as the remaining slots are reserved for super users:

postgres@debian11pg:/home/postgres/ [pgdev] psql -U u postgres &
[3] 17318
postgres@debian11pg:/home/postgres/ [pgdev] psql (16devel)
Type "help" for help.



[3]+  Stopped                 psql -U u postgres
postgres@debian11pg:/home/postgres/ [pgdev] psql -U u postgres &
[4] 17320
postgres@debian11pg:/home/postgres/ [pgdev] psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  remaining connection slots are reserved for superusers

[4]   Exit 2                  psql -U u postgres

Nice, so in addition to reserving connection slots for super users, you can also do that for normal users if you configure the system accordingly. Starting with PostgreSQL 16, of course.

Post Views: 2,612
Thumbnail [60x60]
by
Daniel Westermann