paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

February 10, 2025

Row-Level Security and Connection Pooling: Surviving HR ACL Timeouts Without Blowing Your p95 Budget

Designing fail-closed access control for pooled database connections when the upstream identity source gets slow

Introduction

Row-Level Security (RLS) is one of the more elegant tools in a database engineer's kit. Instead of scattering WHERE department_id = :current_user_dept clauses across every query in your codebase, you push the access control logic down into the database itself. Postgres enforces it transparently, consistently, and - critically - in a way that's much harder for an application bug to accidentally bypass. For most data domains, this works beautifully because the access rules are relatively static: a user belongs to a team, a team belongs to a tenant, and that mapping rarely changes mid-session.

HR data breaks this assumption. Who can see a given employee's compensation, performance review, or medical leave record often depends on real-time organizational state - reporting lines that change after a reorg, a manager who was just suspended pending investigation, or a contractor whose access was revoked an hour ago. Caching that ACL (Access Control Level) for the lifetime of a connection, or even for a few minutes, creates a window where someone can read data they should no longer have access to. So some teams make a deliberate, defensible choice: re-check the ACL against the HR system of record on every sensitive read, and fail closed if that check can't complete. This article is about what happens when that upstream HR service gets slow, and how to build a system that stays secure without becoming unusable.

The Core Tension: Correctness vs. Availability

The fail-closed live ACL check is, from a pure security standpoint, the right default. A system that fails open under load - silently granting access when it can't verify permissions - trades a rare availability problem for a systemic security one, and that trade is almost never worth it for HR, payroll, or similarly regulated data. Auditors and compliance frameworks (SOC 2, HIPAA where applicable, and most internal HR data governance policies) generally expect exactly this behavior: when in doubt, deny.

The problem is that "the HR upstream" is rarely a highly available, low-latency service. HR systems are frequently vendor platforms (Workday, SAP SuccessFactors, BambooHR) accessed over HTTP APIs with their own rate limits, their own maintenance windows, and their own notion of acceptable latency - which is usually tuned for HR administrators clicking through a UI, not for a request path that fires on every row read in a high-throughput application. When that upstream starts queuing requests or returning 5xx errors under load, a naive fail-closed implementation doesn't just deny the specific request that timed out - it can cascade. Every connection in your pool that's waiting on an ACL check is a connection held open, not returning to the pool, and not serving other queries. This is where RLS correctness and connection pool health start fighting each other.

This is compounded by the fact that connection pooling tools like PgBouncer operate in transaction or statement pooling modes specifically to maximize connection reuse under concurrency. If your RLS re-check is implemented as a synchronous network call made inside a transaction that's holding a pooled connection, you've effectively coupled your database connection lifetime to the latency and reliability of an unrelated third-party HTTP API. A slow HR vendor API during a Monday morning performance review cycle can quietly starve your connection pool for unrelated, otherwise-healthy traffic.

Deep Technical Explanation: Where the Latency Actually Comes From

To design a sane fallback, it helps to decompose where time is actually spent in this read path. A request arrives, the application acquires a connection from the pool, begins a transaction, and before (or during) the row-level security evaluation, it needs an authorization decision for the specific resource being read. If that decision requires a live call to HR, the sequence looks like: acquire connection -> begin transaction -> call HR ACL endpoint -> block on network I/O -> receive response -> set RLS context (e.g., via SET LOCAL app.user_permissions = ... or a security-definer function) -> execute query -> commit -> release connection. The HR call sits directly in the critical path, and its tail latency becomes your tail latency.

Under normal conditions, this might add a modest, acceptable number of milliseconds - comparable to any other dependency call. The danger is in the distribution's tail. HR identity services are not built for five-nines availability under spiky load, and when they start to struggle, they often don't fail cleanly; they slow down. Connections queue, retries pile up, and if your application naively retries each failed ACL check two or three times before giving up, a single degraded upstream can multiply your effective request volume against it, making the problem worse - a classic retry storm. Meanwhile, your p95 and p99 latency for the entire read path, not just the HR lookup, inherits all of this variance, because the database connection and the eventual query execution are both waiting behind it.

The second-order effect is on your connection pool's utilization curve. Pools are sized based on an assumption about how long a connection is held per transaction. If that holding time balloons because of an external dependency, you don't just get slow requests - you get pool exhaustion, where new requests can't acquire a connection at all and start failing for reasons that have nothing to do with the database itself. This is a subtle but important distinction to communicate to teams debugging the incident: the database may be completely healthy while the application appears to be down, because the real bottleneck is a pool of connections stuck waiting on HR.

Implementation: Circuit Breakers, Caching, and Decoupling the Check

The first architectural fix is to get the HR ACL check out of the critical section that holds the database connection. Acquire the authorization decision before you acquire a pooled connection, not after. This alone prevents HR latency from directly degrading pool availability, even before you add any resilience patterns.

import CircuitBreaker from "opossum";
import { Pool } from "pg";

const pool = new Pool({ max: 20 });

// Circuit breaker wraps the HR ACL lookup, not the database call.
const hrAclCheck = new CircuitBreaker(
  async (userId: string, resourceId: string) => {
    const res = await fetch(
      `${process.env.HR_SERVICE_URL}/acl?user=${userId}&resource=${resourceId}`,
      { signal: AbortSignal.timeout(150) } // hard timeout budget
    );
    if (!res.ok) throw new Error(`HR ACL check failed: ${res.status}`);
    return res.json() as Promise<{ allowed: boolean; scope: string[] }>;
  },
  {
    timeout: 150,              // matches AbortSignal above
    errorThresholdPercentage: 50,
    resetTimeout: 10_000,      // half-open retry after 10s
    rollingCountTimeout: 10_000,
    volumeThreshold: 10,       // don't trip on a handful of cold-start errors
  }
);

export async function readHrRecord(userId: string, resourceId: string) {
  let acl;
  try {
    acl = await hrAclCheck.fire(userId, resourceId);
  } catch (err) {
    // Fail closed: timeout, circuit open, or explicit denial all land here.
    throw new ForbiddenError("HR authorization unavailable; access denied");
  }

  if (!acl.allowed) throw new ForbiddenError("Access denied by policy");

  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SET LOCAL app.user_scope = $1", [acl.scope.join(",")]);
    const result = await client.query(
      "SELECT * FROM hr_records WHERE id = $1", // RLS policy enforces app.user_scope
      [resourceId]
    );
    await client.query("COMMIT");
    return result.rows;
  } finally {
    client.release();
  }
}

This is a reasonably standard application of the circuit breaker pattern, originally popularized by Michael Nygard in Release It! and widely implemented in libraries like Netflix's Hystrix (now in maintenance mode but influential) and its spiritual successor, resilience4j on the JVM side. The breaker's job isn't to make the HR call faster - it's to stop sending requests to a struggling dependency once failures cross a threshold, so your application fails fast (and fails closed) instead of piling up slow, connection-holding requests. The volumeThreshold and errorThresholdPercentage settings matter a lot here: too sensitive, and a brief blip trips the breaker and denies legitimate traffic unnecessarily; too lax, and you don't protect the pool quickly enough.

The second lever is a short-lived, scope-limited cache for ACL decisions, which reduces how often you need to call HR at all without reintroducing the staleness problem you're trying to avoid. A cache TTL of a few seconds to a couple of minutes, keyed on (user_id, resource_type) rather than per-record, can cut live-check volume substantially for read-heavy workloads while keeping staleness within a window your compliance team has explicitly signed off on. This is different from caching for an entire session; it's caching the decision, with a short enough TTL that a revoked permission is reflected before most compliance frameworks would consider it a violation.

import redis
import httpx
import time

r = redis.Redis(host="localhost", decode_responses=True)
CACHE_TTL_SECONDS = 20  # explicitly approved staleness window

def get_hr_acl(user_id: str, resource_type: str, timeout_s: float = 0.15) -> dict | None:
    cache_key = f"hr_acl:{user_id}:{resource_type}"
    cached = r.get(cache_key)
    if cached is not None:
        return {"allowed": cached == "true", "source": "cache"}

    try:
        resp = httpx.get(
            f"{HR_SERVICE_URL}/acl",
            params={"user": user_id, "type": resource_type},
            timeout=timeout_s,
        )
        resp.raise_for_status()
        decision = resp.json()["allowed"]
        r.setex(cache_key, CACHE_TTL_SECONDS, "true" if decision else "false")
        return {"allowed": decision, "source": "live"}
    except (httpx.TimeoutException, httpx.HTTPStatusError):
        return None  # caller treats None as fail-closed

Trade-offs and Pitfalls

Every mitigation here is a trade-off against the original guarantee you set out to provide, and it's worth being explicit about that rather than letting it slide into the architecture unexamined. A short-TTL cache means you're no longer doing a truly live check - you're doing a bounded-staleness check, and someone with the right authority needs to decide that a 10- or 20-second window of potential over-permission is acceptable for your threat model. For most HR data this is a reasonable trade, since instantaneous revocation is rarely a real operational requirement, but for certain categories - an active legal hold, a just-terminated employee with system access - you may need a separate, uncached, synchronous revocation path that bypasses the cache entirely (commonly implemented as a cache-busting event published when HR explicitly revokes access).

A circuit breaker that fails closed also means that when HR has a bad day, your HR-data features effectively go down for everyone, not just degrade gracefully. This is usually the correct behavior for a security control, but it has real user-facing and business impact that needs to be communicated up front, not discovered during an incident. Teams sometimes try to split the difference by fail-opening for low-sensitivity HR data (say, org chart lookups) while fail-closing for high-sensitivity data (compensation, performance reviews), which is a legitimate pattern but multiplies the number of policies you need to test and reason about.

There's also a subtler pitfall in how timeouts compose. If your HR call has a 150ms timeout, your circuit breaker has its own timeout and reset window, and your overall request has a client-facing SLA, these numbers need to be chosen together, not independently. It's common to see a circuit breaker timeout that's longer than the upstream HTTP client's own timeout, which means the breaker never actually triggers on timeout - the HTTP client throws first, and the breaker only sees it as a generic error. Test this interaction explicitly; it's an easy thing to get backwards and not notice until a load test.

Best Practices for the p95 Budget

Treat the HR ACL check as a dependency with its own explicit latency budget, not an afterthought bolted onto the read path. If your overall endpoint SLA targets a p95 of, say, 300ms, decide up front how much of that budget the ACL check is allowed to consume - a reasonable starting point is somewhere in the 10-20% range, which forces you to set an aggressive timeout (on the order of 100-200ms) rather than inheriting whatever timeout the HR vendor's SDK happens to default to. A timeout that's too generous doesn't protect your p95; it just moves the pain from "fails fast" to "fails slow," which is worse for connection pool health even though it might look identical on an error-rate dashboard.

Separately size and monitor the resource that's actually at risk, which is usually not the HR call's own latency but your connection pool's saturation. Track time-to-acquire-connection as a first-class metric, alongside pool utilization percentage, and alert on it independently from HR service health. This is what lets you distinguish "HR is down and we're correctly denying requests fast" (acceptable, by design) from "HR is slow and we're holding connections open while waiting" (a bug you need to fix, usually by moving the ACL check outside the connection-holding window, as shown above).

Make the fallback behavior observable and testable as a first-class scenario, not just an edge case you hope never triggers. Chaos-engineering-style fault injection - deliberately introducing latency or errors into the HR service in a staging environment - is the most reliable way to verify that your circuit breaker actually trips when you expect, that your p95 degrades gracefully rather than falling off a cliff, and that your on-call team has a runbook entry for "HR ACL circuit breaker is open" that doesn't require re-deriving the architecture at 2 a.m.

#!/usr/bin/env bash
# Simple load-shedding smoke test: inject latency into the HR mock
# and verify the circuit breaker trips within expected bounds.

toxiproxy-cli toxic add hr_service \
  --type latency \
  --attribute latency=2000 \
  --toxicity 1.0

sleep 2
curl -s -o /dev/null -w "%{http_code}\n" http://localhost:8080/hr/employee/123

# Expect 403 (fail-closed) within ~150-200ms once the breaker is open,
# not a hung request waiting on the full HR timeout.

Key Takeaways

Conclusion

Fail-closed, live ACL checks against an HR system of record are a defensible and often necessary choice for sensitive employee data, but they import that upstream's reliability characteristics directly into your read path unless you actively decouple them. The combination of a circuit breaker, a tightly scoped cache, and a connection-pooling strategy that never lets an external call hold a database connection hostage turns an unbounded risk into a bounded, measurable, and testable one.

None of these patterns are exotic - circuit breakers and short-TTL caches are well-worn tools - but applying them specifically to the connection-pool-versus-ACL-check tension is where the engineering judgment actually lives. The goal isn't to eliminate the latency cost of doing the right thing security-wise; it's to make that cost predictable, bounded, and isolated from the rest of your system's health, so that a bad day for your HR vendor becomes a contained, well-understood degradation rather than a cascading outage.

References