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.
Table of Contents
In this short blog post, you will be introduced to the reserved_connections setting and its additional roles in PostgreSQL 16.
In PostgreSQL 16, we can see 3 major parameters controlling connections in general:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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) |
The 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.
After this basic introduction, let's see how to configure it:
|
1 2 3 4 |
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 pg_terminate_backend() (if joe is also a member of the pg_signal_backend role).
In general, there are two ways to configure reserved connections globally: We can set reserved_connections in 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.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at