How to Prevent PostgreSQL Query Stampedes: Protecting OLTP from Reporting and AI Agent Queries

Postgres lacks any query priority scheduler, so reporting, BI, and AI agent queries get the same CPU and I/O as OLTP traffic. Prevent query stampedes by giving non transactional work a dedicated role with hard limits, disabling parallel workers per gather for it, tiering statement_timeout by workload, capping temp_file_limit, routing all ad hoc traffic to a read replica, and adding a pooler plus a gateway that rejects expensive queries before execution.

A production incident that shows up again and again has the same shape. Someone, whether a human analyst, a BI tool, or increasingly an AI agent writing SQL, runs a query with no WHERE clause, an accidental cross join, or a wildly underestimated scan. Postgres has no concept of “this query looks expensive, so deprioritize it,” so it happily hands that query the same CPU, memory bandwidth, and I/O as your transactional traffic. If you’re on Aurora Serverless, or anything else with autoscaling, the database simply does what it’s told and scales, and your bill and your latency spike together.

Postgres has no built in query priority scheduler. It will not throttle a query because it “looks big.” Since Postgres cannot rank queries by priority the way a scheduler might, the right mental model is not to prioritize but to contain: isolate → bound → reject. Rather than assigning OLTP a priority of 10, reporting a 3, and an AI agent a 1, the goal is to isolate where a query can run, bound how much it can consume once it’s running, and reject it outright before it starts if it looks dangerous enough. Everything below is about building that containment model yourself, in front of and around Postgres, so that reporting, ad hoc, and agentic SQL can never hold your OLTP workload hostage.

1. Separate the workload with a dedicated “slow lane” role

The single change that helps the most is to stop letting reporting, admin, BI, and AI generated SQL run under the same role, or worse, the same connection pool, as your application. Create a role specifically for access that isn’t part of your transactional application traffic, and attach hard limits to it directly:

CREATE ROLE adhoc_user LOGIN PASSWORD '...';

ALTER ROLE adhoc_user SET statement_timeout = '30s';
ALTER ROLE adhoc_user SET max_parallel_workers_per_gather = 0;
ALTER ROLE adhoc_user SET temp_file_limit = '2GB';
ALTER ROLE adhoc_user SET work_mem = '16MB';
ALTER ROLE adhoc_user CONNECTION LIMIT 4;

Because these are role level defaults, your OLTP application role keeps its own (tighter, faster) settings. This single change turns “one bad query can degrade the whole cluster” into “one bad query dies inside its own lane.”

2. Kill parallel fanout for anything that isn’t core OLTP

max_parallel_workers_per_gather is the setting most people forget, and it’s arguably the most important one for this failure mode. A large query that the planner decides to parallelize can recruit several background workers simultaneously, turning a single bad query into a small CPU flash mob. Postgres’s own documentation is direct about the scale of this: a parallel query using four workers can use up to five times as much CPU time, memory, and I/O bandwidth as the same query with no workers at all, since settings like work_mem apply separately to each worker rather than being shared across them. Setting max_parallel_workers_per_gather to 0 for ad hoc roles prevents a query from multiplying its own resource footprint this way, even though it doesn’t make the query itself faster or “lower priority”:

-- application/OLTP role
ALTER ROLE app_user SET max_parallel_workers_per_gather = 2;

-- reporting / BI / agentic role
ALTER ROLE adhoc_user SET max_parallel_workers_per_gather = 0;

3. Put a fuse on runtime with tiered statement_timeout

statement_timeout is your first line of defense, but it needs to be tiered rather than global. Setting it in postgresql.conf affects every session including migrations and maintenance jobs, so it’s best set per role or per database instead. A reasonable tiering for something like an OLTP banking workload:

ALTER ROLE app_user SET statement_timeout = '3s';
ALTER ROLE adhoc_user SET statement_timeout = '30s';

statement_timeout is server side and starts counting the moment the command reaches Postgres, so it still bounds execution even if the client that issued the query disappears in the meantime. The separate problem is that Postgres might not notice a client has vanished while a long running query is still computing, because client_connection_check_interval defaults to 0 (disabled). Where your Postgres version supports it, set client_connection_check_interval so the server can detect a closed connection sooner rather than continuing to burn CPU on a query nobody is waiting for anymore.

While you’re at it, set the two timeouts that usually get forgotten alongside it:

ALTER ROLE adhoc_user SET lock_timeout = '2s';                     -- don't wait forever for a row lock
ALTER ROLE adhoc_user SET idle_in_transaction_session_timeout = '60s'; -- kill abandoned open transactions

idle_in_transaction_session_timeout matters more than people expect: an open, idle transaction holds a snapshot and can block autovacuum from cleaning up dead tuples, which degrades the whole cluster slowly rather than all at once.

4. Cap temp file usage so a bad sort can’t turn into a bad disk event

This is the setting most people skip, and it catches the failure mode the others don’t:

ALTER ROLE adhoc_user SET temp_file_limit = '2GB';

If a query performs a huge sort, hash join, or materialization step that spills past this limit, Postgres kills the query outright. Without it, the failure path looks like:

accidental query → 800 GB spill to temp → disk fills, IOPS spike → slowdown across the whole cluster

With it:

accidental query → crosses the temp file guardrail → query dies cleanly

That’s a dramatically healthier failure mode, since a query erroring out beats a disk filling up or a shared I/O budget getting eaten.

5. Use work_mem for memory control, not as your brake

It’s tempting to just shrink work_mem for ad hoc users and call it a throttle. Don’t rely on it that way. Postgres spills sorts and hash operations to temporary files once they exceed their memory allowance, and each operation and each parallel worker gets its own separate allowance rather than sharing one pool, so a low work_mem mostly just forces the query to spill to disk sooner and more often. That can turn a CPU heavy query into an I/O heavy one, often making the blast radius worse rather than better, because now it’s competing with everyone else’s disk I/O instead of just CPU. Set work_mem sanely, with 16 to 64MB as a common ad hoc default, and do your actual throttling with temp_file_limit, statement_timeout, and parallelism limits.

6. Route reporting and agentic traffic to a reader, and know what that boundary actually covers

If Aurora or any Postgres compatible read replica is in your stack, ad hoc, BI, and AI agent traffic should default to the reader rather than the writer. An Aurora reader has separate compute, which gives you strong isolation for CPU and memory, and it should be the default destination for analytical work.

It’s worth being precise about what that isolation does and doesn’t guarantee, though. Aurora readers have separate compute, but they share the cluster storage system with the writer, so they are not a completely independent failure domain the way two entirely separate database clusters would be. That said, Aurora’s storage layer isn’t the same as putting two Postgres instances on one disk with a fixed IOPS bucket: AWS specifically designed Aurora’s distributed storage to scale reader I/O without that read traffic degrading primary performance the way a conventional shared disk would. Ordinary streaming replicas have a related wrinkle of their own. If hot_standby_feedback is enabled, a long running query on a standby can hold back vacuum cleanup on the primary and contribute to bloat there, which Postgres documents directly as a tradeoff of that setting. For genuinely hostile or untrusted analytical workloads, such as an AI agent with a wide blast radius, a separately replicated analytics cluster still provides a meaningfully stronger boundary than a same cluster reader, not because of a shared I/O bottleneck but because it gives you a fully independent failure domain.

OLTP application → WRITER
                              statement_timeout: 3s
                              parallel workers: 2

Humans / BI / AI SQL → READER
                              statement_timeout: 30s
                              parallel workers: 0
                              temp_file_limit: 2GB
                              connection limit: 4

An expensive reporting query then pressures the reader’s compute rather than your transactional path, which eliminates most of the “one query took down checkout” incidents even though it isn’t a complete storage level guarantee.

One more thing worth knowing before you treat the reader as a boundary at all: the Aurora reader endpoint is not itself a guaranteed read only control. If a cluster has no replicas, AWS routes the reader endpoint to the primary, and a connection made through it can then perform writes. Database permissions have to enforce read only access on their own, independent of which endpoint a connection happens to come in through. For agentic workloads where the physical boundary genuinely matters, use a separately replicated reader or analytics cluster rather than relying on endpoint routing alone.

7. Add a connection pooler, and know what it does and doesn’t solve

Postgres forks a full OS process per connection, so an external pooler (PgBouncer, or a managed equivalent like Supavisor) is close to mandatory in production, separately from everything above. In transaction pooling mode, a small pool of real server connections can serve a much larger number of client connections, since most connections are idle most of the time.

Be precise about what PgBouncer is actually solving, though: it is primarily a connection concurrency control, not a query cost scheduler. PgBouncer’s own query_timeout setting can cancel a query that runs too long, but PgBouncer’s documentation is explicit that query_timeout should be used alongside a slightly smaller server side statement_timeout, so that query_timeout mainly catches network and pathological connection problems rather than acting as your primary execution fuse. Treat statement_timeout as the mechanism that actually bounds a query’s runtime, and the pooler as the mechanism that keeps connection exhaustion from becoming a second, separate incident on top of it.

A reasonable starting config for the ad hoc pool specifically:

[adhoc_pool]
pool_mode = transaction
default_pool_size = 4
reserve_pool_size = 0
server_idle_timeout = 300
query_timeout = 45

Here query_timeout (45s) is set slightly above statement_timeout (30s from section 3), so Postgres’s own timeout does the actual cancelling and PgBouncer’s timeout only fires if something has gone wrong at the network layer.

8. Reject expensive queries before they run, not after

Everything so far reacts once a query is already executing. The more mature version of this, and the right answer for AI generated SQL specifically, is a gateway that runs EXPLAIN first and rejects or reroutes anything over a cost threshold:

SQL
  |
  v
EXPLAIN (FORMAT JSON)
  |
  cost below threshold  → EXECUTE
  cost above threshold  → REJECT or require override

It’s worth not treating the planner’s cost number as more authoritative than it is. Postgres cost is expressed in arbitrary planner cost units, not a direct prediction of milliseconds, CPU seconds, or IOPS, and stale statistics or a bad cardinality estimate are exactly how a genuinely bad plan sneaks past a naive total_cost < threshold check. A sturdier gateway looks at several signals together rather than one number: estimated total cost, estimated row counts at each plan node, sequential scans over large relations, cartesian joins, the number and type of joins, and a list of disallowed functions. Because the estimate made ahead of time can still be wrong even with all of that in place, keep statement_timeout active as a backstop even for queries the gateway approved.

It’s also worth separating two things that look similar but aren’t: bounding the output of a query and bounding the work it does to produce that output. Injecting a LIMIT 1000 protects the size of the result set, but it does nothing to stop the query from scanning a billion rows, running a huge aggregation, or performing an expensive sort before it finally returns those ten rows. Agents are especially prone to producing an innocent looking LIMIT 100 query that hides a full table scan and a large sort underneath it, so row limits belong alongside the cost and timeout checks above, not in place of them.

Core Postgres has no native max_query_cost setting, but you don’t have to build the check yourself to get a basic version of it. Two extensions hook directly into the planner and reject a query before it runs if the estimated cost is too high, though they differ enough in maturity that they’re worth weighing separately rather than treating as interchangeable options.

One practical note before the details: pg_plan_filter requires control over shared_preload_libraries, which rules it out on Aurora PostgreSQL specifically, since it isn’t part of Aurora’s supported extension list. Aurora users still get the reader isolation from section 6 and the MaxCapacity ceiling from section 9, but for the pre execution cost check itself, the gateway approach described later in this section is what actually works on Aurora.

pg_plan_filter, from PostgreSQL Experts, is the established option and the one worth actually relying on where you can install it. It has 96 stars and 10 forks on GitHub, runs CI on every pull request, currently supports PostgreSQL 14 through 18, and was originally written by Andrew Dunstan, a longtime PostgreSQL core contributor, with sponsorship from Twitch. It’s loaded via shared_preload_libraries under the module name plan_filter, and it exposes a plan_filter.statement_cost_limit GUC that blocks any single statement whose planned cost exceeds it. It also has a plan_filter.transaction_cost_limit, which caps the combined cost of every statement run inside one transaction, useful for stopping a batch job made of many individually cheap statements that add up to something enormous. An optional plan_filter.filter_select_only scopes either check to SELECT statements:

-- postgresql.conf: shared_preload_libraries = 'plan_filter'
SET plan_filter.statement_cost_limit = 100000;
SET plan_filter.transaction_cost_limit = 500000;
SET plan_filter.filter_select_only = true;

There’s a real limitation worth knowing before you lean on it for agent traffic specifically. The cost estimate pg_plan_filter compares against is computed from planner cost settings such as seq_page_cost and cpu_tuple_cost, and those are all USERSET, meaning any ordinary role can lower them, even to zero, and deflate its own query’s estimated cost below the limit. pg_plan_filter‘s own documentation is explicit that this makes it a guard against accidental or careless load, an overzealous ORM, a bad report, rather than a hard security boundary against a role that’s genuinely hostile or compromised. This weakness cannot be closed by simply revoking SET privileges from the ad hoc role, because USERSET parameters are inherently changeable by any ordinary session; that privilege mechanism exists for parameters that would otherwise require elevated permissions, and planner cost settings were never in that category to begin with. If the client is untrusted, meaning an AI agent whose SQL you don’t fully control, the planner cost policy has to be enforced outside the database connection entirely, in the gateway itself, which should reject any attempt to alter planner cost parameters before it lets a query through. For that use case, the gateway isn’t simply a richer version of pg_plan_filter; it’s the actual security boundary, and pg_plan_filter is protection against accidental load rather than hostile SQL.

pg_cost_guard is the other option that comes up, and it’s worth being direct about where it actually stands: the repository has 2 stars, 0 forks, 7 commits total, appears to be a single maintainer project, and its own README describes it as provided as is for educational and development purposes, with no tagged releases. That’s about as clear a signal as you get that it isn’t ready for production reliance. It has a reasonable design on paper, a cost_guard.threshold GUC plus a warning once a query crosses 80 percent of it, and it explicitly leaves DDL, VACUUM, and ANALYZE untouched, but the maturity gap against pg_plan_filter is large enough that it’s worth treating as one to revisit later rather than adopt now.

Both are worth knowing about because they’re transparent to the client and require no application changes, which is a real advantage over a hand built gateway. The tradeoff is the same one this section opened with: both gate on a single cost number, not the multi signal check described above, and pg_plan_filter‘s own documentation is upfront that Postgres’s cost estimate can be disconnected from actual runtime, so the limit should be set generously and you should expect occasional false positives. Treat pg_plan_filter as a good, cheap first layer that catches the obvious disasters from careless or accidental load, where you can install it. For agent facing traffic specifically, or on Aurora where the extension isn’t available at all, you’ll generally still want the richer multi signal check enforced in the gateway itself as the actual security boundary, which means an application level proxy or gateway rather than the extension alone.

Setting the threshold itself is the part most people end up guessing at, and guessing tends to go wrong in one of two directions: too low, so legitimate reporting queries start getting rejected and someone eventually just disables the extension out of frustration, or too high, so it never actually catches anything. A more reliable number comes from measuring your own workload before you enforce anything against it, rather than picking a round figure like 100,000 and hoping.

To calibrate the threshold, collect planner estimates from representative traffic rather than reaching straight for auto_explain.log_analyze on a busy production database. Plain EXPLAIN already includes the planner’s total_cost without actually running the query, which is all the distribution below needs, and turning on auto_explain.log_analyze instruments every statement that runs, not just the ones that end up logged. Postgres’s own documentation warns that per node timing instrumentation can carry a real performance cost, and that matters more here than in most contexts, since the entire point of this section is protecting OLTP from the overhead of other workloads. Turning the measurement itself into a small stampede on the database you’re trying to protect defeats the purpose.

A safer way to build the cost distribution logs total_cost without ANALYZE:

ALTER SYSTEM SET auto_explain.log_min_duration = '100ms';
ALTER SYSTEM SET auto_explain.log_analyze = off;
ALTER SYSTEM SET auto_explain.log_format = 'json';
SELECT pg_reload_conf();

That gives you the cost distribution with negligible overhead, since it never has to actually execute the extra instrumentation ANALYZE requires. If you also want the cost to runtime conversion described below, that does require log_analyze = on, so treat it as a separate, narrower step rather than part of the default recommendation: enable it briefly against a small sample, ideally on a read replica rather than the primary, set auto_explain.log_timing = off to at least remove the per node timing overhead, and turn it back off once you have enough data rather than leaving it on for a week against live OLTP traffic.

From the logged plans, pull the total_cost values for the traffic you already know is legitimate and look at the distribution rather than a single number. The 95th or 99th percentile gives you a natural floor, and the maximum among that known good traffic tells you how much headroom your worst legitimate query already needs. A reasonable starting threshold sits somewhere between three and ten times that legitimate maximum: comfortably above real reporting queries, but still well below the kind of accidental cross join or missing WHERE clause that tends to produce a cost several orders of magnitude larger, not marginally larger.

If you did also capture the optional log_analyze sample, the logged plans pair estimated cost with actual runtime, which gives you a rough conversion factor for your own hardware between cost units and wall clock time. That’s useful for sanity checking the threshold against the statement_timeout you’ve already set for that role: if a cost around 500,000 tends to correspond to roughly the same runtime as the 30 second ad hoc timeout from section 3, that gives you a grounded starting point instead of an arbitrary six digit number picked out of the air. It’s a nice addition, though, not a requirement: the percentile based threshold above works fine on its own.

Before enforcing anything, run in observe only mode if you can. pg_cost_guard‘s warning at 80 percent of the threshold is built for exactly this, or with pg_plan_filter you can simply set the limit generously at first and watch your logs for anything that would have tripped it. Tighten the threshold gradually over a week or two rather than committing to a final number on day one, and revisit it whenever data volume grows meaningfully, since a threshold sized for today’s row counts will eventually start flagging perfectly ordinary queries as your tables grow. The same approach of measuring first is worth applying to the other numbers seeded earlier in this post, statement_timeout, temp_file_limit, connection limits, rather than treating the example values in sections 1 through 4 as settings to copy verbatim.

For agent generated SQL specifically, this pattern has become close to a best practice in 2026: default the agent’s role to read only, force row count limits, require the multi signal EXPLAIN check to pass before execution (whether hand built or backed by one of the extensions above), and gate anything that writes behind explicit human approval. The reasoning that keeps coming up in agentic SQL writeups is worth internalizing directly, which is that enforcement has to happen at the database boundary, because a system prompt telling the model “only SELECT” is not a guarantee. A prompt injection or a structural hallucination can produce a write anyway, and the boundary is the only thing that can’t be argued with.

Practical layers for an agent facing setup:

• Read only role plus read replica as the physical boundary (an agent literally cannot write if its credential can’t).
• Multi signal EXPLAIN checks, not just a single cost threshold, since planner cost alone can be fooled by stale statistics.
• Row count and result size caps alongside the checks above, not instead of them, since they bound output but not execution.
• SQL statement type allowlisting (SELECT, EXPLAIN, SHOW only) enforced outside the model.
• Audit logging of generated SQL so a bad pattern is traceable to a specific prompt or session.

9. If you’re on Aurora Serverless v2, cap MaxCapacity deliberately

Autoscaling is a mixed blessing during a runaway query event: it absorbs the spike, but it also means a misconfigured query gets rewarded with more capacity instead of being stopped. AWS explicitly recommends setting a lower MaxCapacity specifically to protect against excessive consumption from inefficient queries or misconfiguration:

aws rds modify-db-cluster \
  --db-cluster-identifier prod \
  --serverless-v2-scaling-configuration \
  MinCapacity=16,MaxCapacity=64

MaxCapacity becomes your financial and resource circuit breaker. Instead of silently scaling to 150 ACUs and a very large bill, the cluster hits 64 ACUs and you get contention, a visible and debuggable problem, instead of an invisible one. This doesn’t replace the query level guardrails above, since a capped cluster with an unthrottled query still degrades, just with a ceiling on how bad it gets.

10. Watch for the slow burn version of this problem

Not every stampede is a single dramatic query. Sessions that run long or sit idle inside a transaction hold back the autovacuum horizon, letting dead tuples and bloat accumulate until routine queries get slow across the board, which makes for a much harder incident to diagnose because there’s no single smoking gun query in pg_stat_activity. idle_in_transaction_session_timeout (section 3) is your defense here. It’s also worth logging slow queries continuously with log_min_duration_statement so you catch the pattern before it becomes an incident. This won’t stop a sudden mass event, but it’s what lets you notice the trend that precedes one.

11. Putting it together

The overall shape, in priority order if you’re retrofitting an existing system:

1. Split OLTP and everything else onto separate roles with separate settings (section 1), since it is the cheapest and fastest change to make.
2. Point everything else at a read replica, not the writer (section 6), since this is the biggest single risk reduction if you have Aurora or other replicas available, while remembering it isn’t a complete storage level boundary.
3. Set tiered statement_timeout plus lock_timeout plus idle_in_transaction_session_timeout per role (sections 3, 10).
4. Disable parallel fanout and cap temp_file_limit for the ad hoc role (sections 2, 4).
5. Add a connection pooler sized correctly for each workload, with its timeout set as a backstop behind statement_timeout rather than a replacement for it (section 7).
6. For AI agent access specifically, add an EXPLAIN check that runs before execution and looks at multiple signals, plus row limits, and never grant write credentials to an agent by default (section 8).
7. If autoscaled, cap MaxCapacity as a financial and resource ceiling, not as your primary control (section 9).

The underlying principle across all of it is that you shouldn’t try to make Postgres execute a bad query slowly and safely, because it can’t. Make bad queries impossible to run at scale (isolate the role, remove parallelism, cap temp resources, timeout aggressively) or impossible to run at all (reject them before execution based on estimated cost and structure). A slow bad query is often worse than a fast one, since it holds locks, connections, and MVCC snapshots for longer while it fails.

12. Score your own setup

Rather than leaving all of this as a checklist to eyeball, it’s worth having something that looks at your actual configuration and tells you where you stand. The script below connects to your database with psql and checks whether your actual ad hoc, reporting, and agent roles, not just any role that happens to exist, have the safeguards described above.

That distinction matters more than it sounds like it should. An earlier version of this script scored “does any role in the system have this control,” which produces a misleadingly high score: a well configured service account you barely use can earn full marks even while your actual adhoc_user or agent_user role remains completely unrestricted. It also counted a role as protected if it had statement_timeout=0 set, which is exactly backward, since 0 means the timeout is disabled. The version below fixes both problems. It only scores roles matching a name pattern you configure, and if none match, it says so explicitly rather than reporting a suspiciously perfect score for controls nobody actually verified.

Save the script as postgres_protection_score.sql, edit the target_role_patterns array near the top to match your actual role names, and run it with:

psql -d yourdb -f postgres_protection_score.sql

-- postgres_protection_score.sql
--
-- Run with: psql -d yourdb -f postgres_protection_score.sql
--
-- Edit target_role_patterns below to match your actual ad hoc,
-- reporting, BI, and agent role names before running this. The script
-- only scores roles matching one of those patterns, on purpose: scoring
-- "does any role in the system have this control" produces a
-- misleadingly high score when a single well configured service
-- account has a setting but your actual risky roles do not.
--
-- This only reads catalog data and does not change anything on your
-- server. The score is a heuristic, not an audit. It cannot see things
-- that live outside Postgres, such as whether you have a connection
-- pooler in front of it, whether reporting traffic is actually routed
-- to a genuinely separate reader, or whether a gateway based on
-- EXPLAIN sits in front of your agent's SQL.

DO $$
DECLARE
    target_role_patterns text[] := ARRAY[
        '%adhoc%', '%ad_hoc%', '%report%', '%bi_%', '%analy%',
        '%agent%', '%ai_%', '%readonly%', '%read_only%', '%etl%'
    ];
    target_roles oid[];
    role_count int;
    score int := 0;
    applicable int := 0;
    max_role_checks int := 8;
    has_statement_timeout boolean;
    has_lock_timeout boolean;
    idle_in_txn_ok boolean;
    has_no_parallel_role boolean;
    has_temp_file_limit boolean;
    has_connlimit_role boolean;
    has_role_level_work_mem boolean;
    no_target_is_superuser boolean;
    logging_slow_queries boolean;
    stats_extension boolean;
    fully_unmanaged_role text;
BEGIN
    SELECT array_agg(oid) INTO target_roles
    FROM pg_roles
    WHERE rolcanlogin = true
      AND rolname ILIKE ANY (target_role_patterns);

    role_count := COALESCE(array_length(target_roles, 1), 0);

    RAISE NOTICE '========================================================';
    IF role_count = 0 THEN
        RAISE NOTICE 'No login roles matched target_role_patterns.';
        RAISE NOTICE 'Edit the array at the top of this script to match your';
        RAISE NOTICE 'actual ad hoc, reporting, or agent role names, then rerun.';
        RAISE NOTICE 'Role level checks are skipped rather than scored, since';
        RAISE NOTICE 'scoring an empty set of roles would show full marks for';
        RAISE NOTICE 'controls nobody actually verified.';
    ELSE
        RAISE NOTICE 'Checking % role(s): %', role_count,
            (SELECT string_agg(rolname, ', ') FROM pg_roles WHERE oid = ANY(target_roles));
    END IF;
    RAISE NOTICE '========================================================';

    IF role_count > 0 THEN
        -- 1. Every target role has a nonzero statement_timeout.
        SELECT NOT EXISTS (
            SELECT 1 FROM unnest(target_roles) t(role_oid)
            WHERE NOT EXISTS (
                SELECT 1 FROM pg_db_role_setting s, unnest(s.setconfig) cfg
                WHERE s.setrole = t.role_oid
                  AND cfg LIKE 'statement_timeout=%'
                  AND cfg NOT LIKE 'statement_timeout=0%'
            )
        ) INTO has_statement_timeout;

        -- 2. Every target role has a nonzero lock_timeout.
        SELECT NOT EXISTS (
            SELECT 1 FROM unnest(target_roles) t(role_oid)
            WHERE NOT EXISTS (
                SELECT 1 FROM pg_db_role_setting s, unnest(s.setconfig) cfg
                WHERE s.setrole = t.role_oid
                  AND cfg LIKE 'lock_timeout=%'
                  AND cfg NOT LIKE 'lock_timeout=0%'
            )
        ) INTO has_lock_timeout;

        -- 3. Every target role has idle_in_transaction_session_timeout
        --    bounded, either at the role level or from a nonzero
        --    global default.
        SELECT NOT EXISTS (
            SELECT 1 FROM unnest(target_roles) t(role_oid)
            WHERE current_setting('idle_in_transaction_session_timeout') = '0'
              AND NOT EXISTS (
                  SELECT 1 FROM pg_db_role_setting s, unnest(s.setconfig) cfg
                  WHERE s.setrole = t.role_oid
                    AND cfg LIKE 'idle_in_transaction_session_timeout=%'
                    AND cfg NOT LIKE 'idle_in_transaction_session_timeout=0%'
              )
        ) INTO idle_in_txn_ok;

        -- 4. Every target role disables parallel fanout.
        SELECT NOT EXISTS (
            SELECT 1 FROM unnest(target_roles) t(role_oid)
            WHERE NOT EXISTS (
                SELECT 1 FROM pg_db_role_setting s, unnest(s.setconfig) cfg
                WHERE s.setrole = t.role_oid
                  AND cfg = 'max_parallel_workers_per_gather=0'
            )
        ) INTO has_no_parallel_role;

        -- 5. Every target role has temp_file_limit capped (not unlimited).
        SELECT NOT EXISTS (
            SELECT 1 FROM unnest(target_roles) t(role_oid)
            WHERE NOT EXISTS (
                SELECT 1 FROM pg_db_role_setting s, unnest(s.setconfig) cfg
                WHERE s.setrole = t.role_oid
                  AND cfg LIKE 'temp_file_limit=%'
                  AND cfg NOT LIKE 'temp_file_limit=-1'
            )
        ) INTO has_temp_file_limit;

        -- 6. Every target role has an explicit connection limit.
        SELECT NOT EXISTS (
            SELECT 1 FROM unnest(target_roles) t(role_oid)
            JOIN pg_roles r ON r.oid = t.role_oid
            WHERE r.rolconnlimit = -1
        ) INTO has_connlimit_role;

        -- 7. Every target role sets work_mem explicitly.
        SELECT NOT EXISTS (
            SELECT 1 FROM unnest(target_roles) t(role_oid)
            WHERE NOT EXISTS (
                SELECT 1 FROM pg_db_role_setting s, unnest(s.setconfig) cfg
                WHERE s.setrole = t.role_oid
                  AND cfg LIKE 'work_mem=%'
            )
        ) INTO has_role_level_work_mem;

        -- 8. None of the target roles is a superuser. A superuser role
        --    bypasses every other setting on this list, so this check
        --    can invalidate all the others at once.
        SELECT NOT EXISTS (
            SELECT 1 FROM unnest(target_roles) t(role_oid)
            JOIN pg_roles r ON r.oid = t.role_oid
            WHERE r.rolsuper = true
        ) INTO no_target_is_superuser;

        score := score
            + has_statement_timeout::int
            + has_lock_timeout::int
            + idle_in_txn_ok::int
            + has_no_parallel_role::int
            + has_temp_file_limit::int
            + has_connlimit_role::int
            + has_role_level_work_mem::int
            + no_target_is_superuser::int;
        applicable := applicable + max_role_checks;

        RAISE NOTICE '[%] Every target role has a nonzero statement_timeout', CASE WHEN has_statement_timeout THEN 'x' ELSE ' ' END;
        RAISE NOTICE '[%] Every target role has a nonzero lock_timeout', CASE WHEN has_lock_timeout THEN 'x' ELSE ' ' END;
        RAISE NOTICE '[%] Every target role has idle_in_transaction_session_timeout bounded', CASE WHEN idle_in_txn_ok THEN 'x' ELSE ' ' END;
        RAISE NOTICE '[%] Every target role disables parallel fanout', CASE WHEN has_no_parallel_role THEN 'x' ELSE ' ' END;
        RAISE NOTICE '[%] Every target role caps temp_file_limit', CASE WHEN has_temp_file_limit THEN 'x' ELSE ' ' END;
        RAISE NOTICE '[%] Every target role has an explicit connection limit', CASE WHEN has_connlimit_role THEN 'x' ELSE ' ' END;
        RAISE NOTICE '[%] Every target role sets work_mem explicitly', CASE WHEN has_role_level_work_mem THEN 'x' ELSE ' ' END;
        RAISE NOTICE '[%] None of the target roles is a superuser', CASE WHEN no_target_is_superuser THEN 'x' ELSE ' ' END;
        RAISE NOTICE '========================================================';

        SELECT r.rolname INTO fully_unmanaged_role
        FROM unnest(target_roles) t(role_oid)
        JOIN pg_roles r ON r.oid = t.role_oid
        WHERE NOT EXISTS (
            SELECT 1 FROM pg_db_role_setting s WHERE s.setrole = t.role_oid
        )
        LIMIT 1;

        IF fully_unmanaged_role IS NOT NULL THEN
            RAISE NOTICE 'Heads up: role "%" matched your target patterns but has', fully_unmanaged_role;
            RAISE NOTICE 'no role level settings of any kind. It is running under';
            RAISE NOTICE 'whatever the global defaults happen to be.';
            RAISE NOTICE '========================================================';
        END IF;
    END IF;

    -- 9. Slow queries are logged, independent of role naming.
    SELECT (current_setting('log_min_duration_statement')::int <> -1)
    INTO logging_slow_queries;
    score := score + logging_slow_queries::int;
    applicable := applicable + 1;

    -- 10. pg_stat_statements is installed, independent of role naming.
    SELECT EXISTS (
        SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements'
    ) INTO stats_extension;
    score := score + stats_extension::int;
    applicable := applicable + 1;

    RAISE NOTICE '[%] Slow queries are logged (log_min_duration_statement)', CASE WHEN logging_slow_queries THEN 'x' ELSE ' ' END;
    RAISE NOTICE '[%] pg_stat_statements is installed', CASE WHEN stats_extension THEN 'x' ELSE ' ' END;
    RAISE NOTICE '========================================================';
    RAISE NOTICE 'Score: % / % applicable checks', score, applicable;
    RAISE NOTICE '========================================================';

    IF role_count = 0 THEN
        RAISE NOTICE 'Role level checks (8 of the 10 above) were skipped, so this';
        RAISE NOTICE 'score reflects logging and observability only, not whether';
        RAISE NOTICE 'your actual risky roles are contained. Fix target_role_patterns';
        RAISE NOTICE 'and rerun before trusting this number.';
    ELSIF score::float / applicable <= 0.3 THEN
        RAISE NOTICE 'Interpretation: little to no protection on the roles that matter most.';
    ELSIF score::float / applicable <= 0.6 THEN
        RAISE NOTICE 'Interpretation: partial protection. Real gaps remain on the roles checked.';
    ELSIF score::float / applicable <= 0.85 THEN
        RAISE NOTICE 'Interpretation: solid protection at the database layer. Confirm the parts this script cannot see: a pooler, a genuinely separate reader or analytics cluster, and a cost gateway that runs before execution.';
    ELSE
        RAISE NOTICE 'Interpretation: strong, layered protection on the roles checked.';
    END IF;
    RAISE NOTICE '========================================================';
END $$;

The checks correspond directly to the guardrails covered earlier in this post: whether your actual ad hoc, reporting, and agent roles, specifically, have a tuned statement_timeout and lock_timeout, whether idle_in_transaction_session_timeout is bounded for them, whether they disable parallel fanout, whether temp_file_limit is capped, whether they carry an explicit connection limit, whether work_mem is set explicitly, and whether any of them is accidentally a superuser, which would make every other check moot. Two checks apply globally regardless of role naming: whether slow queries are logged, and whether pg_stat_statements is installed so you have visibility into what’s actually running. A low score means an unbounded query could still run today against your risky roles specifically. A high score means the database level defenses on those roles are solid, at which point the remaining work is mostly about the surrounding architecture, the pooler, the replica boundary, and the gateway that runs before execution, none of which a query against the catalog can verify for you.