Reserve connections for the
pg_use_reserved_connections group in PostgreSQL 16
Nathan Bossart implemented a brand-new patch that provides a way to reserve connection slots for non-superusers.
The patch was reviewed by Tushar Ahuja and Robert Haas. Committed by Robert Haas. The commit message is:
This provides a way to reserve connection slots for non-superusers. The slots reserved via the new GUC are available only to users who have the new predefined role pg_use_reserved_connections. superuser_reserved_connections remains as a final reserve in case reserved_connections has been exhausted. Patch by Nathan Bossart. Reviewed by Tushar Ahuja and by me. Discussion: http://postgr.es/m/20230119194601.GA4105788@nathanxps13
Let’s try reserve connections in our work!
Let’s edit the
postgresql.conf file and set these values:
... max_connections = 2 # (change requires restart) reserved_connections = 1 # (change requires restart) superuser_reserved_connections = 0 # (change requires restart) ...
superuser_reserved_connections to zero so that it does not interfere with our testing.
Now, let’s create a simple mortal:
postgres=# create user pasha password '12345'; CREATE ROLE postgres=# \q
Now we connect as a user “pasha”:
$ psql -U pasha -d postgres psql (16devel) Type "help" for help
From another terminal we will repeat the above command:
$ psql -U pasha -d postgres 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
WTF! In previous versions you are able to use all of the
max_connections slots. But not anymore!
Now the time for the new role
postgres=# grant pg_use_reserved_connections to pasha; GRANT ROLE
And now you can establish the second session:
$psql -U pasha -d postgres psql (16devel) Type "help" for help. postgres=>
Reserve connections? Why not regular ones?
You may wonder how this new feature can be useful. After all, we already have
superuser_reserved_connections, which reserves some connections for superusers. The idea of that parameter is that even if all connections are taken, a superuser can still connect and fix the problem, typically by terminating some connections. However, not all connections are equal: even if your application’s connection pool is exhausted, you still would like your backup and monitoring tools to be able to connect to the database. And if you are security conscious, you won’t want those tools to use a superuser to connect. The traditional way to handle this is a connection limit on the application user, but that requires you to readjust the limit whenever you change the size of the connection pool.
pg_use_reserved_connections offers a convenient alternative solution to make some connections be “more equal” than others.
Connection pooling is a related topic that may interest you. Find out about pgbouncer, which is a free tool that makes connection pooling easy, right here.
Please leave your comments below. In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.