Skip to content

ARGUS A19

Jump to bottom
will2469 edited this page Aug 29, 2026 · 1 revision

ARGUS-A19: UNBOUNDED_QUERY_LIMIT

Meta Field Specification
Rule Code ARGUS-A19
Identifier UNBOUNDED_QUERY_LIMIT
Severity HIGH
Category Memory Bounds, Denial of Service & Query Hygiene
Analysis Layer Layer 3 - Contextual & Pure SQL-AST Analysis
CWE Mapping CWE-400: Uncontrolled Resource Consumption
OWASP ASVS OWASP ASVS v4.0.3/v5.0 §V1.4.3, §V12 (Denial of Service Prevention)
PostgreSQL Target PostgreSQL 18.x (Buffer Pool Cache Retention & Index Scan Standards)
Default Status enabled

1. Executive Summary & Architectural Invariant

Database tables characterized by high tuple cardinality or continuous chronological growth (high-cardinality / high-growth tables, such as audit_logs, events, transactions, users, orders) must never be queried using open-ended multi-row SELECT projections.

┌─────────────────────────────────────────────────────────────────────────────┐
│                            ARCHITECTURAL INVARIANT                          │
│                                                                             │
│  Every multi-row query against high-cardinality or unbounded growth         │
│  tables MUST declare an explicit row boundary clause (LIMIT or FETCH FIRST) │
│  or implement keyset/cursor pagination.                                     │
│                                                                             │
│  Exemptions:                                                                │
│  1. Pure scalar aggregates (COUNT(*), SUM, AVG, MIN, MAX) without GROUP BY  │
│  2. Unique Point Lookups on Primary/Candidate Keys (WHERE id = $1)          │
│  3. Small reference tables (ref_*, metadata, static dictionaries)           │
└─────────────────────────────────────────────────────────────────────────────┘

2. PostgreSQL 18 Engine Internals & Threat Mechanics

Why do open-ended queries on large tables cause catastrophic cascading system outages?

┌─────────────────────────────────────────────────────────────────────────────┐
│                    THE UNBOUNDED QUERY OOM KILLER CHAIN                     │
│                                                                             │
│  Table `audit_logs` contains 15,000,000 tuples                              │
│                                                                             │
│  Developer executes:                                                        │
│  SELECT id, action, payload FROM audit_logs WHERE tenant_id = $1            │
│  (No LIMIT clause!)                                                         │
│                                                                             │
│  Cascading Infrastructure Impact:                                           │
│  1. PostgreSQL executes sequential scan across 15,000,000 rows              │
│  2. Active shared_buffers (8GB) get evicted (Buffer Cache Thrashing)        │
│  3. 15,000,000 rows streamed across TCP to Go Backend Pod                   │
│  4. Backend allocates ~24GB RAM in heap memory for slice structs            │
│  5. Extreme GC Pause spikes -> Linux Kernel OOM Killer sends SIGKILL (9)    │
│  6. POD CRASHES & CONTAINER RESTARTS UNEXPECTEDLY!                          │
│                                                                             │
│  Remediated Query:                                                          │
│  SELECT id, action, payload FROM audit_logs                                 │
│  WHERE tenant_id = $1 AND id < $last_id                                     │
│  ORDER BY id DESC LIMIT 100; (Retrieves 100 rows -> 2ms & 50KB RAM)         │
└─────────────────────────────────────────────────────────────────────────────┘

2.1. Buffer Pool Thrashing (shared_buffers Eviction)

Executing an unconstrained SELECT forces PostgreSQL to scan extensive heap pages from disk or cache. This bulk ingestion evicts hot OLTP rows and B-tree index nodes out of shared_buffers, degrading query latency cluster-wide.

2.2. Client-Side Out-Of-Memory (OOM) Termination (CWE-400)

In Go backend applications using pgx.CollectRows or rows.Scan, scanning unconstrained result sets allocates continuous memory blocks proportional to the incoming row count. Allocating hundreds of thousands of heap objects triggers memory exhaustion, triggering the Linux Kernel Out-Of-Memory killer (SIGKILL 9).

2.3. Keyset Pagination vs Offset Pagination

While LIMIT 100 OFFSET 1000000 syntactically satisfies row limits, PostgreSQL still reads and discards 1,000,000 tuples. ARGUS-A19 promotes Keyset Pagination (WHERE id < $last_id ORDER BY id DESC LIMIT 100), achieving logarithmic $O(\log N)$ index lookups.

3. Architecture & Execution Flow

flowchart TD
    A["SQL Query AST Node"] --> B{"Is Target Table High-Cardinality?"}
    B -- "No (e.g. ref_status, config)" --> C["PASS (Compliant)"]
    B -- "Yes (e.g. audit_logs, events)" --> D{"Is LIMIT or FETCH FIRST present?"}
    D -- "Yes (LIMIT $1 / LIMIT 100)" --> C
    D -- "No" --> E{"Is Pure Scalar Aggregate without GROUP BY?"}
    E -- "Yes (COUNT(*) / SUM)" --> C
    E -- "No" --> F{"Is Unique Point Lookup (WHERE id = $1)?"}
    F -- "Yes (Single Row Guaranteed)" --> C
    F -- "No" --> G{"Is Suppressed via // argus:ignore?"}
    G -- "Yes (Valid Reason)" --> C
    G -- "No" --> H["FAIL: ARGUS-A19 Unbounded Query Violation"]
Loading

4. Detection Logic & Rule Anatomy

Argus AST visitor analyzes database calls (pgxpool.Pool.Query, sql.DB.Query, db.QueryRow):

  1. Table Identification: Parses SQL statements via github.com/pganalyze/pg_query_go/v6 to extract target table relations from SelectStmt.FromClause.
  2. Cardinality Verification: Checks target tables against configured high_cardinality_tables / high_growth_tables defined in .argus.yaml.
  3. Limit Analysis: Inspects SelectStmt.LimitCount and SelectStmt.LimitOffset.
  4. Exemption Handling:
    • Detects single-row scalar aggregate functions (COUNT, SUM, AVG, MIN, MAX) when len(SelectStmt.GroupClause) == 0.
    • Identifies equality predicates on primary keys (WHERE id = $1, WHERE uuid = $1).
    • Resolves dynamic query builders appending LIMIT in enclosing function scope.

5. Code Examples Matrix

Non-Compliant (Unbounded Queries)

// VIOLATION: Unbounded SELECT across multi-million row table
func FetchAuditHistory(ctx context.Context, pool *pgxpool.Pool, orgID string) ([]AuditEntry, error) {
    const query = `
        SELECT id, actor_id, action, created_at
        FROM audit_logs
        WHERE org_id = $1
        ORDER BY created_at DESC
    `
    rows, err := pool.Query(ctx, query, orgID)
    // ...
}
// VIOLATION: Aggregation with GROUP BY without LIMIT produces unbounded groups
const query = "SELECT action, COUNT(*) FROM audit_logs GROUP BY action"
rows, err := pool.Query(ctx, query)

Compliant (Bounded Queries)

// COMPLIANT: Explicit LIMIT clause bounds memory consumption
func FetchAuditHistory(ctx context.Context, pool *pgxpool.Pool, orgID string) ([]AuditEntry, error) {
    const query = `
        SELECT id, actor_id, action, created_at
        FROM audit_logs
        WHERE org_id = $1
        ORDER BY created_at DESC
        LIMIT 50
    `
    rows, err := pool.Query(ctx, query, orgID)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    return pgx.CollectRows(rows, pgx.RowToStructByName[AuditEntry])
}
// COMPLIANT: Keyset pagination with LIMIT for high-performance navigation
func FetchNextPage(ctx context.Context, pool *pgxpool.Pool, orgID string, lastID int64) ([]AuditEntry, error) {
    const query = `
        SELECT id, actor_id, action, created_at
        FROM audit_logs
        WHERE org_id = $1 AND id < $2
        ORDER BY id DESC
        LIMIT 100
    `
    rows, err := pool.Query(ctx, query, orgID, lastID)
    // ...
}
// COMPLIANT: Pure scalar aggregate without GROUP BY returns exactly 1 row
func CountTotalEvents(ctx context.Context, pool *pgxpool.Pool, orgID string) (int64, error) {
    var count int64
    err := pool.QueryRow(ctx, "SELECT COUNT(*) FROM audit_logs WHERE org_id = $1", orgID).Scan(&count)
    return count, err
}
// COMPLIANT: Point lookup by Primary Key equality returns at most 1 row
func FindUserByID(ctx context.Context, pool *pgxpool.Pool, id string) (*User, error) {
    const query = "SELECT id, email, full_name FROM users WHERE id = $1"
    row, err := pgx.CollectOneRow(pool.QueryRow(ctx, query, id), pgx.RowToAddrOfStructByName[User])
    return row, err
}

6. Mitigation & Remediation Guide

  1. Add Explicit LIMIT Clause: For list/inbox APIs, declare explicit limits (e.g. LIMIT 50 or parameterized LIMIT $limit).
  2. Implement Keyset Pagination: Replace unbounded scans with index-friendly cursor pagination (WHERE id < $last_id ORDER BY id DESC LIMIT 100).
  3. Use Point Lookups: Ensure single-entity retrieval queries filter on Primary Key (WHERE id = $1).

7. Configuration & Suppression Directives

Configuration in .argus.yaml 

rules:
  ARGUS-A19:
    enabled: true
    default_max_limit: 1000
    high_growth_tables:
      - "audit_logs"
      - "transactions"
      - "events"
      - "users"
      - "orders"
      - "notifications"

Inline Ignore Directives

For analytical background workers or bulk export pipelines that legitimately require full table ingestion:

// argus:ignore ARGUS-A19 offline analytical export worker dumps full table
rows, err := pool.Query(ctx, "SELECT * FROM audit_logs WHERE created_at < $1", cutoffDate)

// argus:ignore UNBOUNDED_QUERY_LIMIT monthly cold archive migration
rows, err := pool.Query(ctx, archiveQuery)

Clone this wiki locally