In PostgreSQL 16, a new feature has been added:
reserved_connections. What is the idea behind it? Back in the old days, DBA’s and software developers tended to work as a superuser (=
postgres user). However, this is not ideal for security reasons. Therefore the PostgreSQL community has worked hard to reduce the necessity of connecting as a superuser. Over the years, features have been added to reduce the need of using this highly privileged way of connecting to the system, and reserved connections are another method.
In this short blog post, you will be introduced to the
reserved_connections setting and its additional roles in PostgreSQL 16.
Adjusting connection parameters in PostgreSQL
In PostgreSQL 16, we can see 3 major parameters controlling connections in general:
postgres=# SHOW max_connections; max_connections ----------------- 100 (1 row) postgres=# SHOW superuser_reserved_connections; superuser_reserved_connections -------------------------------- 3 (1 row) postgres=# SHOW reserved_connections; reserved_connections ---------------------- 0 (1 row)
max_connections setting controls the number of overall connections allowed. This is a hard limit and changing this variable needs a restart. Out of this pool of connections,
superuser_reserved_connections will only be available to the superuser.
In our example, this means that we can create 97 “normal” connections and 3 superuser ones (or 100 superuser ones, which is not recommended at all). Why is that important? The idea is that there are always spare connections available in order to perform maintenance and other tasks.
PostgreSQL 16 will provide us with a new, additional setting:
reserved_connections. For many years, the idea has been circulated that it’s best to limit the use of superusers. Therefore, a new role called
pg_use_reserved_connections has been introduced. If a normal (non-superuser) is assigned to this role, it’s possible to access the pool of reserved connections. In other words:
We create an “almost superuser” which can do all kinds of maintenance without having the ability to seriously destroy things.
Configuring a role for reserved connections
After this basic introduction, let’s see how to configure it:
postgres=# CREATE USER joe; CREATE ROLE postgres=# GRANT pg_use_reserved_connections TO joe; GRANT ROLE
As you can see, all we need is two lines. First, we created a user, and then we assigned the magic role to the system.
To actually give more power to user
joe, we have to increase
reserved_connections. If we set the parameter to 5, then only 92 database connections will be available to normal users. Three are reserved for superuser, and five for users in the
pg_use_reserved_connections role. So when all 92 connections are in use,
joe can still connect and for example terminate sessions with
joe is also a member of the
In general, there are two ways to configure reserved connections globally: We can set
postgresql.conf, or we can utilize
ALTER SYSTEM to set the parameter globally.
For more information about connections and performance, see this blog about connection pooling.
In case you need any assistance, please feel free to contact us.