Enforcing Soft and Hard Quotas in PostgreSQL

When a multi-tenant PostgreSQL fleet bills by consumption, a runaway tenant needs a control loop that reads its rolling spend and clamps the role at the database itself — this page builds that loop in psycopg v3 with real ALTER ROLE SQL that is both idempotent and fully reversible.

Back to: Database Quota Boundary Design

A quota boundary only matters at the moment it becomes an action, and in PostgreSQL that action is a change to the tenant’s role. The design here is a two-tier loop that translates a normalized cost signal — the same kind of settled-plus-proxy figure described in database quota boundary design — into concrete role state. A soft breach emits an alert and sets the tenant role default_transaction_read_only, which stops writes and new provisioning while leaving reads intact. A hard breach layers on a strict statement_timeout, an idle_in_transaction_session_timeout, and a CONNECTION LIMIT so a single tenant can neither pin a connection slot nor run an unbounded query. Every transition is written so it can run on every tick without drift and can be unwound cleanly the instant spend recovers.

The two properties that make this safe to run unattended are idempotency — applying the soft tier twice leaves the role in exactly one state — and reversibility — every setting applied has a matching RESET so recovery is a first-class path, not a manual cleanup. The connection-level half of the hard tier (terminating the backends a tenant already holds) is the specific subject of the sibling page on throttling tenant connections on budget breach; this page owns the role-level policy loop that decides when that throttle fires.

Prerequisites

  • A dedicated enforcement role. Do not run this loop as a superuser. Create a quota_admin role with CREATEROLE so it can alter the tenant roles it manages but cannot touch data or system catalogs it does not own. On PostgreSQL 16 the CREATEROLE grantee can ALTER ROLE and set CONNECTION LIMIT on roles it administers.

    -- Run once as a superuser during provisioning.
CREATE ROLE quota_admin WITH LOGIN CREATEROLE PASSWORD :'pw';
-- Grant admin over each tenant role so quota_admin may alter it (PG16 syntax).
GRANT tenant_acme TO quota_admin WITH ADMIN OPTION;

  • A rolling-spend source. The loop reads a materialized figure per tenant — a table refreshed by your metric pipeline holding month-to-date and projected cost. This page assumes a tenant_cost_rolling(tenant, rolling_cost_usd, projected_cost_usd, refreshed_at) table; how that table is populated from provider exports is upstream work.

  • Python 3.10+ and the client libraries. psycopg v3 for the SQL, pydantic v2 for the policy model, and requests for the alert webhook.

    pip install "psycopg[binary]>=3.1" "pydantic>=2.6" "requests>=2.31"

  • Connection string in the environment. Never hard-code credentials: export QUOTA_DSN="postgresql://quota_admin@db.internal:5432/postgres" and supply the password via ~/.pgpass or PGPASSWORD.

Step-by-Step Implementation

The loop has four moving parts: a policy model, a soft-tier applier, a hard-tier applier, and a clear/reset path. Each SQL-emitting function first reads the role’s current state from pg_roles and only issues DDL when the desired state differs, which is what makes a tick idempotent.

Step 1 — Model the policy and read rolling spend

The policy is a plain data object so thresholds live in configuration, not code. The reader returns the rolling and projected figures the evaluator compares against.

import os
from decimal import Decimal
from enum import Enum

import psycopg
from psycopg import sql
from psycopg.rows import dict_row
from pydantic import BaseModel


class Tier(str, Enum):
    NORMAL = "normal"
    SOFT = "soft"
    HARD = "hard"


class QuotaPolicy(BaseModel):
    budget_usd: Decimal          # the tenant's monthly cap
    soft_ratio: Decimal = Decimal("0.80")   # read-only hold at 80% of budget
    hard_ratio: Decimal = Decimal("1.00")   # full clamp at 100%
    statement_timeout: str = "5s"
    idle_in_txn_timeout: str = "10s"
    hard_conn_limit: int = 5


def read_rolling_spend(conn: psycopg.Connection, tenant: str) -> tuple[Decimal, Decimal]:
    """Return (month_to_date, projected_month_end) cost for one tenant."""
    row = conn.execute(
        """
        SELECT rolling_cost_usd, projected_cost_usd
        FROM tenant_cost_rolling
        WHERE tenant = %s
        """,
        (tenant,),
    ).fetchone()
    if row is None:
        return Decimal("0"), Decimal("0")
    return Decimal(row[0]), Decimal(row[1])


def decide_tier(mtd: Decimal, projected: Decimal, policy: QuotaPolicy) -> Tier:
    """Reversible dimensions use MTD ratio; projection escalates the soft tier early."""
    if mtd >= policy.budget_usd * policy.hard_ratio:
        return Tier.HARD
    if mtd >= policy.budget_usd * policy.soft_ratio or projected > policy.budget_usd:
        return Tier.SOFT
    return Tier.NORMAL

>>> decide_tier(Decimal("820"), Decimal("990"), QuotaPolicy(budget_usd=Decimal("1000")))
<Tier.SOFT: 'soft'>
>>> decide_tier(Decimal("1010"), Decimal("1200"), QuotaPolicy(budget_usd=Decimal("1000")))
<Tier.HARD: 'hard'>

Step 2 — Read current role state so writes are idempotent

Before altering anything, read what the role already carries. pg_roles.rolconfig is a text array of key=value GUC defaults; rolconnlimit is the connection cap (-1 means unlimited). Comparing desired against current means an unchanged tenant produces zero DDL.

def read_role_state(conn: psycopg.Connection, role: str) -> dict:
    """Return the role's per-role GUC defaults and connection limit."""
    row = conn.execute(
        """
        SELECT rolconnlimit,
               COALESCE(rolconfig, ARRAY[]::text[]) AS rolconfig
        FROM pg_roles
        WHERE rolname = %s
        """,
        (role,),
    ).fetchone()
    if row is None:
        raise LookupError(f"role {role!r} does not exist")
    conn_limit, rolconfig = row
    settings = dict(item.split("=", 1) for item in rolconfig)
    return {"conn_limit": conn_limit, "settings": settings}

>>> read_role_state(conn, "tenant_acme")
{'conn_limit': -1, 'settings': {}}

Step 3 — Apply the soft tier: read-only hold plus alert

The soft tier sets one GUC — default_transaction_read_only — which forces every new transaction on that role into read-only mode. Reads keep working; INSERT, UPDATE, CREATE, and any provisioning DDL raise read-only transaction. Because ALTER ROLE ... SET only affects new sessions, existing writers are drained by the connection throttle in the sibling page.

import logging
import requests

log = logging.getLogger("quota")


def apply_soft(conn: psycopg.Connection, role: str, state: dict) -> bool:
    """Set default_transaction_read_only=on if not already set. Returns True if changed."""
    if state["settings"].get("default_transaction_read_only") == "on":
        return False  # already held — idempotent no-op
    conn.execute(
        sql.SQL("ALTER ROLE {} SET default_transaction_read_only = {}").format(
            sql.Identifier(role), sql.Literal("on")
        )
    )
    log.info("soft tier: %s set read-only", role)
    return True


def emit_alert(tenant: str, tier: Tier, mtd: Decimal, budget: Decimal) -> None:
    """Notify owners. Routing/dedup/escalation is the alert layer's job, not this loop's."""
    webhook = os.environ.get("QUOTA_ALERT_WEBHOOK")
    if not webhook:
        return
    payload = {
        "tenant": tenant,
        "tier": tier.value,
        "spend_usd": float(mtd),
        "budget_usd": float(budget),
        "pct": round(float(mtd / budget) * 100, 1),
    }
    requests.post(webhook, json=payload, timeout=5)

The alert here is intentionally thin — a single POST. Turning that raw signal into a deduplicated, escalating page is a separate concern, handled by alert routing and escalation rather than baked into the enforcement loop.

Step 4 — Apply the hard tier: timeouts and a connection cap

The hard tier keeps the read-only hold and adds three protective settings. statement_timeout kills any single query that runs too long; idle_in_transaction_session_timeout reclaims a connection left holding locks with an open transaction; CONNECTION LIMIT caps how many sessions the role may open at once. Each is applied only if it differs from the current value.

def apply_hard(conn: psycopg.Connection, role: str, state: dict,
               policy: QuotaPolicy) -> list[str]:
    """Apply timeouts + connection cap idempotently. Returns the list of changes made."""
    changed: list[str] = []
    desired_guc = {
        "default_transaction_read_only": "on",
        "statement_timeout": policy.statement_timeout,
        "idle_in_transaction_session_timeout": policy.idle_in_txn_timeout,
    }
    for guc, value in desired_guc.items():
        if state["settings"].get(guc) != value:
            conn.execute(
                sql.SQL("ALTER ROLE {} SET {} = {}").format(
                    sql.Identifier(role), sql.Identifier(guc), sql.Literal(value)
                )
            )
            changed.append(guc)

    if state["conn_limit"] != policy.hard_conn_limit:
        conn.execute(
            sql.SQL("ALTER ROLE {} CONNECTION LIMIT {}").format(
                sql.Identifier(role), sql.Literal(policy.hard_conn_limit)
            )
        )
        changed.append("connection_limit")

    if changed:
        log.warning("hard tier: %s applied %s", role, changed)
    return changed

Note statement_timeout values are quoted with sql.Literal, so '5s' reaches the server as a string literal — PostgreSQL parses the unit. The GUC name is wrapped with sql.Identifier too, so a bad key can never inject SQL.

Step 5 — Clear the boundary when spend recovers

Reversibility is the half of the design that is easy to skip and expensive to omit. Recovery resets every GUC the tiers set and returns the connection limit to -1. RESET removes the per-role default entirely, so the role reverts to the server-wide default rather than to a hard-coded value.

CLEARABLE_GUC = (
    "default_transaction_read_only",
    "statement_timeout",
    "idle_in_transaction_session_timeout",
)


def clear_boundary(conn: psycopg.Connection, role: str, state: dict) -> list[str]:
    """Undo all quota settings. Only touches settings that are actually present."""
    changed: list[str] = []
    for guc in CLEARABLE_GUC:
        if guc in state["settings"]:
            conn.execute(
                sql.SQL("ALTER ROLE {} RESET {}").format(
                    sql.Identifier(role), sql.Identifier(guc)
                )
            )
            changed.append(guc)
    if state["conn_limit"] != -1:
        conn.execute(
            sql.SQL("ALTER ROLE {} CONNECTION LIMIT -1").format(sql.Identifier(role))
        )
        changed.append("connection_limit")
    if changed:
        log.info("cleared boundary for %s: %s", role, changed)
    return changed

Step 6 — The control loop

The orchestrator ties the pieces together. It runs one tenant per transaction with autocommit=True (DDL such as ALTER ROLE must not sit inside an aborted transaction block), reads state once, then converges the role to the tier the spend dictates. The state machine below is what the loop implements: NORMAL → SOFT → HARD on rising spend, and the same edges in reverse as spend falls back under threshold.

Quota control-loop state machine: Normal, Soft, and Hard tiers with reversible transitionsThree states left to right. Normal has no restrictions. Soft sets default_transaction_read_only to on. Hard adds statement_timeout, idle_in_transaction_session_timeout, and a connection limit. The top arrows move rightward as spend rises: Normal to Soft when the ratio reaches the soft threshold, Soft to Hard when the ratio reaches 1.0. The bottom arrows move leftward as spend recovers, each issuing RESET and returning the connection limit to minus one.NORMALno restrictionsSOFTread-only hold+ alertHARDtimeouts+ conn limitratio ≥ softratio ≥ 1.0RESET read-onlyRESET + CONN LIMIT -1rising spend → · ← recovering spend (dashed)
def reconcile_tenant(conn: psycopg.Connection, tenant: str, role: str,
                     policy: QuotaPolicy) -> Tier:
    """Read spend, decide the tier, and converge the role state to it."""
    mtd, projected = read_rolling_spend(conn, tenant)
    tier = decide_tier(mtd, projected, policy)
    state = read_role_state(conn, role)

    if tier is Tier.NORMAL:
        clear_boundary(conn, role, state)
    elif tier is Tier.SOFT:
        # ensure hard-only settings are gone, then hold read-only
        if any(g in state["settings"] for g in
               ("statement_timeout", "idle_in_transaction_session_timeout")) \
                or state["conn_limit"] != -1:
            clear_boundary(conn, role, state)
            state = read_role_state(conn, role)
        if apply_soft(conn, role, state):
            emit_alert(tenant, tier, mtd, policy.budget_usd)
    else:  # HARD
        applied = apply_hard(conn, role, state)
        if applied:
            emit_alert(tenant, tier, mtd, policy.budget_usd)
    return tier


if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO)
    policy = QuotaPolicy(budget_usd=Decimal("1000"))
    with psycopg.connect(os.environ["QUOTA_DSN"], autocommit=True,
                         row_factory=dict_row) as conn:
        result = reconcile_tenant(conn, "acme", "tenant_acme", policy)
        print(f"tenant acme reconciled to tier: {result.value}")

INFO:quota:soft tier: tenant_acme set read-only
tenant acme reconciled to tier: soft

Verification

Confirm the role actually carries the settings before trusting the loop in production.

psql "$QUOTA_DSN" -c "\drds tenant_acme"

The per-role settings display should list exactly the GUCs the current tier applies:

       Role       |             Settings
------------------+-----------------------------------
 tenant_acme      | default_transaction_read_only=on
                  | statement_timeout=5s
                  | idle_in_transaction_session_timeout=10s

To prove the read-only hold bites, open a fresh session as the tenant and attempt a write — it must fail, while a SELECT succeeds:

import psycopg

with psycopg.connect("postgresql://tenant_acme@db.internal/appdb") as t:
    t.execute("SELECT 1").fetchone()          # ok
    try:
        t.execute("CREATE TABLE probe(x int)")  # blocked by the soft hold
    except psycopg.errors.ReadOnlySqlTransaction as exc:
        print("write blocked as expected:", exc)

write blocked as expected: cannot execute CREATE TABLE in a read-only transaction

After spend recovers, a reconcile_tenant tick should return Tier.NORMAL and \drds tenant_acme should print no settings.

Gotchas & Edge Cases

  • ALTER ROLE ... SET only affects new sessions. A tenant already mid-write when the soft tier fires keeps writing until its session ends. To make the hold bite immediately you must terminate the tenant’s existing backends — the mechanism covered in throttling tenant connections on budget breach.
  • CONNECTION LIMIT is enforced at connect time, not retroactively. Lowering the cap to 5 does nothing to a tenant holding 40 open connections; it only prevents the 41st. Pair the cap with backend termination to bring an over-committed tenant down to the ceiling.
  • DDL must run in autocommit. If any ALTER ROLE runs inside a transaction that later aborts, every change rolls back together and the role silently keeps its old state. Run the loop with autocommit=True so each statement commits independently and the state you read next tick is real.
  • statement_timeout can kill legitimate long jobs. A 5-second cap will terminate a tenant’s nightly reindex or bulk load. Scope the hard tier to interactive roles, or set a generous timeout, so enforcement does not masquerade as data corruption.
  • idle_in_transaction_session_timeout frees locks, not idle connections. It only fires when a session is idle inside an open transaction; a plain idle connection is untouched. For idle-connection reclamation, use the connection cap plus pool-side eviction.
  • Superuser and replication roles ignore some limits. CONNECTION LIMIT is not enforced for superusers, and default_transaction_read_only is trivially overridable by a role that can SET it back. Keep tenant roles unprivileged so the boundary holds.
  • Read the projected figure, not just month-to-date. Storage grows monotonically, so a mid-month reading is far under budget while the run-rate is already over. Feeding the projection into decide_tier — the same discipline used across database quota boundary design — lets the soft tier fire before the overrun instead of after.

Frequently Asked Questions

Why set default_transaction_read_only instead of revoking write grants?

Setting the GUC is atomic, reversible with a single RESET, and leaves the underlying GRANT structure untouched, so recovery cannot accidentally drop a permission the tenant legitimately had. REVOKE/GRANT cycles are error-prone: you must enumerate every object and every future default privilege, and one missed grant leaves the tenant broken after recovery. The read-only hold is a clean on/off switch layered above the permission model.

Does lowering CONNECTION LIMIT disconnect existing sessions?

No. CONNECTION LIMIT is checked only when a new session connects; it never terminates an established one. If a tenant already holds more connections than the new cap, those sessions run until they close on their own. To force convergence, terminate the excess backends explicitly, which is exactly what the connection-throttling page covers.

How do I make this idempotent across many ticks?

Read the role’s current state from pg_roles first and compare each desired setting against rolconfig and rolconnlimit, issuing ALTER ROLE only when a value actually differs. A tenant already in the correct tier then produces zero DDL, so running the loop every minute is cheap and never churns the catalog. The clear path is symmetric: it only issues RESET for settings that are present.

What connection-pool interaction should I watch for?

If tenants connect through PgBouncer in transaction-pooling mode, default_transaction_read_only still applies because the GUC is a role default resolved server-side, but statement_timeout set per role is inherited by the server-side connection the pooler opens. Confirm the pooler authenticates as the tenant role rather than a shared service role, or the per-role settings never attach. Pool-level connection ceilings are covered in the throttling page.

Should the enforcement loop send the alert itself?

Keep it thin. The loop should emit a single structured signal — tenant, tier, spend, budget — and hand routing, deduplication, and escalation to a dedicated layer. Baking PagerDuty or Slack logic into the control loop couples enforcement to notification and makes both harder to test. Route the signal through alert routing and escalation instead.

Back to: Database Quota Boundary Design