Enable per-schema-user connection limits in Azure Cosmos DB for PostgreSQL

Hi ,

Thanks for reaching out to Microsoft Q&A.

There is no way today to set CONNECTION LIMIT on a per-user basis in azure CosmosDB for PostgreSQL. The practical approach is to manage this outside the database using pgBouncer, connection pooling in the application, and good monitoring. The service is managed, and you do not get superuser or CREATEROLE privileges. Because of that, commands like: ALTER ROLE schema1user CONNECTION LIMIT 10; will not work, and there is no portal or CLI command to do this either. Mcrosoft does not currently expose a way to enforce per-user connection limits at the database level in this platform.

Since you cannot enforce limits inside the db, you need to handle it at the pooling or application layer. Here are your options:

• Use the built-in pgBouncer CosmosDB for PostgreSQL comes with pgBouncer enabled in transaction pooling mode. Tune the pool settings to handle a reasonable number of connections per application. This reduces the risk of 1 tenant using up all available connections.
• Run your own pgBouncer If you need pertenant control, run your own pgBouncer instance for each tenant or for groups of tenants. Limit the pool size for each instance to make sure no single tenant can monopolize connections.
• Control at the middleware or API gateway level If you have a multitenant API, implement rate limiting or concurrency limits at the request layer. This helps you throttle tenants before they overload the db.
• Monitor usage Use pg_stat_activity to watch connection usage. Set up alerts so you know when one tenant is opening too many connections.
• Scale the cluster Increasing node vCores raises the overall connection limits (300, 500, or 1000 connections per node depending on the size), but this does not isolate tenants. It only delays the problem if one tenant spikes.

Please 'Upvote'(Thumbs-up) and 'Accept' as answer if the reply was helpful. This will be benefitting other community members who face the same issue.

Hello Martin Masek, Thanks for posting your query on Microsoft QnA!,

I thank Vinodh247 and hope the provided answer by him is helpful to you, do let me know if you have any further questions on this.

In addition to our community member's response, I am sharing the steps which may help you with setting up per connection limits.

Steps to Follow

1. Accept that per-role connection limits are not supported natively in the managed Citus service.
2. Implement external connection pooling to enforce per-tenant limits (PgBouncer is the most common choice).

• Deploy a PgBouncer instance for each tenant (or per pool of tenants). Steps given below
• Configure PgBouncer’s pool settings to cap client connections per tenant user. Steps given below.

3. Scale your Cosmos DB for PostgreSQL instance vertically (vCores) or horizontally (Citus workers) to ensure sufficient overall capacity. - Use the Azure portal’s “Scale & Settings” blade. - Or use Azure CLI to adjust vCores.
4. Monitor and alert on connection usage at the instance level to ensure capacity is not exhausted.

Sharing an example PgBouncer configuration snippet for two tenants:

[databases]

tenant1 = host=<your-server>.postgres.cosmos.azure.com port=5432 dbname=tenant1db user=schema1user password=<pwd>

tenant2 = host=<your-server>.postgres.cosmos.azure.com port=5432 dbname=tenant2db user=schema2user password=<pwd>

[pgbouncer]

listen_addr = 0.0.0.0

listen_port = 6432

auth_type = md5

auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction

Per-tenant limits

max_client_conn = 100

default_pool_size = 10 ; limits each pool to 10 server connections

reserve_pool_size = 5

reserve_pool_timeout = 5

Some of the following best practices that you can consider :

• Use separate PgBouncer instances or namespaces per tenant for isolation.
• Monitor daytime and peak connection counts to size pools appropriately.
• Automate scaling of both your Cosmos DB for PostgreSQL instance and PgBouncer fleet with Azure Autoscale and Azure Functions.

Let me know if this works for you and share your further queries on the same.

Please accept as Yes if the response is helpful so that it can help others in the community.

Thanks

Pratyush