I recently came across a Mydbops article on TiDB 8.5.7 highlighting per-user connection limits. TiDB’s release notes confirm that the release adds both a max_user_connections variable and a WITH MAX_USER_CONNECTIONS option for individual users, enforced per TiDB server instance.
That is a useful reminder of a capability already available in YugabyteDB’s PostgreSQL-compatible YSQL API:
ALTER ROLE app_user CONNECTION LIMIT 20;
One line can keep an application from claiming every available connection on a node.
Apparently, even database connections need boundaries.
Why Put a Limit on a User?
Imagine several applications sharing a universe. One application leaks connections, a batch script opens sessions in a loop, or Kubernetes scales out dozens of pods with oversized connection pools.
Without a boundary, that one application can consume available connection slots and prevent other applications from connecting. Direct YSQL connections also require backend processes and memory; excessive active connections can add CPU contention.
Per-user connection limits can help contain these problems:
Use a distinct login role for each application or tenant you want to control. If every application logs in as the same user, they share that user’s allowance.
This is one part of resource governance. It limits open connections, including idle ones. It does not impose a CPU budget, memory budget, query rate, or guaranteed throughput allocation. A small number of expensive queries can still consume substantial resources.
Demo: Two Seats for the Chatty App
This demo was run on YugabyteDB 2026.1.2.0, using YSQL based on PostgreSQL 15.12. We will fill one user’s two connection slots, show that another user can still connect, and then prove the limit is enforced per node.
The neat part? We can do it from one terminal using nested ysqlsh sessions.
1. Check the Test Environment
Start in an administrative ysqlsh session as yugabyte on node 1:
ysqlsh -h 127.0.0.1 -p 5433 -U yugabyte -d yugabyte
Check the nodes:
SELECT host, cloud, region, zone
FROM yb_servers()
ORDER BY host;
Actual result:
These are the placement labels reported by this local test cluster. The loopback addresses do not demonstrate a deployment across actual AWS regions.
The commands below use direct YSQL connections on port 5433. For your environment, substitute the node addresses and direct backend port; bypass Connection Manager and other poolers for this test. Use your normal authentication and TLS options.
2. Create the Database and Application Users
From the admin session:
CREATE DATABASE connection_limit_demo;
CREATE ROLE chatty_app LOGIN NOSUPERUSER;
CREATE ROLE polite_app LOGIN NOSUPERUSER;
ALTER ROLE chatty_app CONNECTION LIMIT 2;
ALTER ROLE polite_app CONNECTION LIMIT 2;
GRANT CONNECT ON DATABASE connection_limit_demo
TO chatty_app, polite_app;
All statements completed successfully in the test. If your environment requires passwords, set them using \password chatty_app and \password polite_app in ysqlsh before connecting as those users.
Verify the settings:
SELECT rolname, rolsuper, rolconnlimit
FROM pg_roles
WHERE rolname IN ('chatty_app', 'polite_app')
ORDER BY rolname;
Actual result:
Both roles are non-superusers, with two connection slots each on every YSQL node.
Confirm the original session’s identity and endpoint:
SELECT current_user, inet_server_addr(), pg_backend_pid();
Actual result:
Your backend PID will differ.
3. Fill Both Slots from One Terminal
From the yugabyte=# prompt, launch the first application session:
\! ysqlsh -h 127.0.0.1 -U chatty_app -d connection_limit_demo
It connects successfully:
ysqlsh (15.12-YB-2026.1.2.0-b0)
Type "help" for help.
connection_limit_demo=>
From that new prompt, run the same command again:
\! ysqlsh -h 127.0.0.1 -U chatty_app -d connection_limit_demo
The second connection also succeeds.
\! command launches a shell command from inside ysqlsh. While the child session runs, its parent remains connected. Nesting the command keeps both chatty_app connections open, even though you only see the innermost prompt.4. Try a Third Connection
From the innermost application session, run it once more:
\! ysqlsh -h 127.0.0.1 -U chatty_app -d connection_limit_demo
Actual result:
ysqlsh: error: connection to server at "127.0.0.1", port 5433 failed: FATAL: too many connections for role "chatty_app"
Two connections succeed. The third is rejected. You return to the existing application prompt.
5. Show That Another User Can Still Connect
Without closing either chatty-app session, run this command on a single line:
\! ysqlsh -h 127.0.0.1 -U polite_app -d connection_limit_demo -c "SELECT current_user, 'Still getting work done!' AS status;"
Actual result:
The cap belongs to chatty_app; it has not consumed polite_app‘s allowance. The short-lived polite-app connection closes after executing the query.
This is the practical benefit: one application reaching its limit does not, by itself, prevent another user from connecting. Overall node capacity must still be available.
6. Count the Open Sessions
Still inside the nested application session, launch a short-lived admin connection to inspect node 1.
Keep the entire \! command on one line:
\! ysqlsh -h 127.0.0.1 -c "SELECT usename, state, count(*) AS connections FROM pg_stat_activity WHERE usename IN ('chatty_app', 'polite_app') GROUP BY usename, state ORDER BY usename, state;"
This reproduces the successful command from the test environment, where the default connection user was yugabyte. Specify -U yugabyte -d yugabyte explicitly if your defaults differ.
Actual result:
Both idle application sessions still count toward the limit. The polite-app connection has already exited, so it does not appear.
The admin connection can inspect them because superusers bypass the role connection limit. Also, this pg_stat_activity query reports sessions on node 1, not the entire universe.
7. Prove the Limit Is per Node
With both chatty-app connections still open on 127.0.0.1, connect the same user to 127.0.0.2:
\! ysqlsh -h 127.0.0.2 -U chatty_app -d connection_limit_demo -c "SELECT current_user, inet_server_addr(), 'A seat on node 2!' AS status;"
Actual result:
It succeeds. During that command, the same user has two connections on node 1 and one on node 2.
The configured limit of two is enforced independently on each node. You set the role attribute once; you do not have to repeat ALTER ROLE on every node.
8. Close the Sessions and Connect Again
Run \q to close the innermost chatty-app session:
connection_limit_demo=> \q
connection_limit_demo=>
You are now back in the first chatty-app session. Run \q again:
\! ysqlsh -h 127.0.0.1 -U chatty_app -d connection_limit_demo
The connection succeeds again:
ysqlsh (15.12-YB-2026.1.2.0-b0)
Type "help" for help.
connection_limit_demo=>
Exit it with \q to return to the admin session.
The test confirms: the role limit rejects excess connections on one node, other users can still connect, another node has its own allowance, and closing sessions frees capacity.
Set, Inspect, and Remove the Limit
Using the application role created in the demo:
ALTER ROLE chatty_app CONNECTION LIMIT 20;
Inspect the configured value:
SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolname = 'chatty_app';
Remove the role-specific cap:
ALTER ROLE chatty_app CONNECTION LIMIT -1;
-1 means no role-specific limit; node-level connection limits still apply. Lowering a role’s limit does not disconnect existing sessions. It restricts subsequent connection attempts.
yugabyte superuser can make it appear that the setting does nothing.What About Connection Manager?
The demo deliberately uses direct YSQL connections so each client session has its own backend connection. With YSQL Connection Manager or another pooler, client sessions and backend connections are separate quantities.
Do not assume this direct-connection demo establishes a cap on logical client sessions through a pooler. Validate role-limit behavior with your YugabyteDB version and pooling configuration before relying on it for tenant quotas.
For connection sizing and the distinction between clients and backends, see Right-Sizing Connections in YugabyteDB with YSQL Connection Manager.
Clean Up
Exit all remaining demo-user sessions with \q. From the admin session connected to the yugabyte database, run:
DROP DATABASE connection_limit_demo;
DROP ROLE chatty_app;
DROP ROLE polite_app;
Final Takeaway
Use separate application logins and ALTER ROLE ... CONNECTION LIMIT to put a boundary around how many direct connections each application can consume on a YSQL node.
Configure once. Enforce per node. Count idle connections too.
Your app can have a seat at the table. It does not need every chair.
One of the things we’re already loving about The Colony, TX… all the walking paths! The Tribute Shoreline Trail runs right behind our new house, with five miles to explore along Lake Lewisville. Between unpacking boxes and hitting the trails, getting our steps in shouldn’t be a problem! 🚶♂️🌳☀️