PostgreSQL AppUser Connection Limit Auditor

The Incident (What Does the Error Mean?)

Raw error output:

FATAL:  too many connections for role "appuser"

PostgreSQL enforces per-role connection limits set at role creation or via ALTER ROLE. When the count of active connections authenticated as appuser reaches that ceiling, the server rejects every subsequent connection attempt — hard stop, no queue, no retry. Your application gets a fatal exception on the connection acquisition call. Connection pools on the app side start throwing, health checks fail, and depending on your retry logic, you may spiral into a thundering herd that hammers the already-saturated role.

This is not a max_connections global exhaustion. It is a targeted per-role limit. The rest of the database may be perfectly healthy.

The Attack Vector / Blast Radius

This failure mode is deceptively catastrophic because it is surgical and silent until it isn't.

Cascading failure chain:

1. A deployment, traffic spike, or connection leak pushes appuser active connections to the limit.
2. New web/API workers fail to acquire a DB connection. Requests time out or return 500s.
3. Application-level connection pools (HikariCP, pgx, psycopg2 pool) exhaust their own queue waiting for a slot that never frees.
4. Health checks that themselves require a DB connection start failing — load balancers pull instances, reducing app capacity, which concentrates remaining traffic on fewer instances, which each open more connections. Classic thundering herd.
5. If appuser is shared across microservices (a common anti-pattern), all services go down simultaneously — not just the offending one.

Security angle: A CONNECTION LIMIT set too low is sometimes a misconfigured security control intended to throttle a low-privilege read-only role. If someone set CONNECTION LIMIT 10 on a role that now serves production write traffic, you have an accidental self-DoS baked into your schema. Attackers who know your role structure can deliberately saturate the limit with unauthenticated or low-cost connection attempts to trigger this exact outage.

Prevention in CI/CD

1. Terraform / IaC — Enforce Pooler-Aware Connection Limits

 resource "postgresql_role" "appuser" {
   name     = "appuser"
   login    = true
-  # connection_limit not set — defaults to -1 (unlimited)
+  connection_limit = 50
 }

Add a Terraform precondition or Sentinel policy to block roles with connection_limit = -1 on non-superuser accounts.

2. Checkov Policy (Terraform scanning)

Checkov does not have a built-in rule for postgresql_role connection limits. Add a custom check:

# checkov/custom_checks/pg_role_connection_limit.py
from checkov.terraform.checks.resource.base_resource_check import BaseResourceCheck
from checkov.common.models.enums import CheckResult, CheckCategories

class PgRoleConnectionLimit(BaseResourceCheck):
    def __init__(self):
        super().__init__(
            name="Ensure PostgreSQL role has explicit connection_limit",
            id="CKV_CUSTOM_PG_001",
            categories=[CheckCategories.GENERAL_SECURITY],
            supported_resources=["postgresql_role"]
        )

    def scan_resource_conf(self, conf):
        limit = conf.get("connection_limit", [-1])
        val = limit[0] if isinstance(limit, list) else limit
        # -1 = unlimited; reject roles with no explicit cap
        if val == -1 or val is None:
            return CheckResult.FAILED
        return CheckResult.PASSED

3. Alerting — Catch This Before It Pages You

-- Prometheus postgres_exporter custom query
-- Alert when appuser connections exceed 80% of its limit
SELECT
  count(*)::float / NULLIF(rolconnlimit, 0) AS connection_saturation_ratio
FROM pg_stat_activity
JOIN pg_roles ON pg_roles.rolname = pg_stat_activity.usename
WHERE pg_stat_activity.usename = 'appuser'
GROUP BY rolconnlimit;

Set a Prometheus/Grafana alert at > 0.80 (80% saturation). You want 10+ minutes of lead time before the wall hits.

4. OPA / Conftest for SQL Migration Files

# policy/pg_role_limit.rego
package postgresql

deny[msg] {
  input.statement.type == "AlterRoleStmt"
  option := input.statement.options[_]
  option.defname == "connectionlimit"
  option.arg.ival == -1
  msg := "ALTER ROLE must not set CONNECTION LIMIT to -1 (unlimited) for non-superuser roles"
}

Run conftest test migration.sql --policy policy/ in your CI pipeline to block unlimited-connection role definitions before they reach production.