How PostgreSQL 16 changes connection slot allocation
Nathan Bossart authored a patch that reserves connection slots for non-superusers; Tushar Ahuja and Robert Haas reviewed it, and Robert Haas committed it. The commit message reads:
|
1 2 3 4 5 6 7 8 9 |
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 |
Configuring and testing the reserved group
To exercise the feature, edit postgresql.conf with:
|
1 2 3 4 5 |
... max_connections = 2 # (change requires restart) reserved_connections = 1 # (change requires restart) superuser_reserved_connections = 0 # (change requires restart) ... |
Setting superuser_reserved_connections to zero keeps the superuser pool from interfering with the test.
Next, create an ordinary, non-privileged role:
|
1 2 3 |
postgres=# create user pasha password '12345'; CREATE ROLE postgres=# q |
Connect as user pasha:
|
1 2 3 |
$ psql -U pasha -d postgres psql (16devel) Type 'help' for help |
Repeat the same command from a second terminal:
|
1 2 3 |
$ 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 |
The second attempt fails. Prepatch releases let you consume every one of the max_connections slots; that is no longer true.
Grant the new membership to lift the restriction:
|
1 2 |
postgres=# grant pg_use_reserved_connections to pasha; GRANT ROLE |
The second session can now be established:
|
1 2 3 4 5 |
$psql -U pasha -d postgres psql (16devel) Type 'help' for help. postgres=> |
Why a separate reserved group beats a pool limit
superuser_reserved_connections already exists, but it targets a narrower problem: it guarantees that a superuser can still log in when every slot is occupied, typically to terminate sessions and clear the blockage. Not every workload deserves superuser rights, though. Backup jobs and monitoring agents must reach the database even when the application's connection pool is fully exhausted, and a security-conscious operator will not hand those tools superuser credentials. The usual workaround is capping the application user's connections, but that ceiling has to be recalculated every time the pool size changes. Using the pg_use_reserved_connections group avoids that maintenance: it makes selected roles "more equal" than the rest without granting them superuser access.
