STRUCTURAL SQL SENTINEL

structural-sql-sentinel.py

Adversarial-test discipline for SQL-shape sentinels. A fail-open regex must match every commuted, cast, IS NULL, and coalesce variant — and prove it can fail.

Stark

WHAT THIS PATTERN TEACHES

Why a single-form structural sentinel is a single point of failure. How to harden it with comprehensive alternation, positive controls, negative controls (to stop false-positive fatigue), and a fixture-bindability proof that a sentinel which algebraically cannot fail is a no-op.

WHEN TO USE THIS

Any SQL-shape policing — fail-open detection in RLS policies, dangerous catalog reads, deprecated function calls, plaintext storage in encrypted columns. Pair a CI grep with a runtime assertion against pg_policies.qual.

AT A GLANCE

@pytest.mark.parametrize("form", POSITIVE_FORMS)
def test_sentinel_catches_fail_open_form(form: str) -> None:
    """Every known fail-open variant must trip the sentinel."""
    assert policy_is_fail_open(form), \
        f"SENTINEL GAP: form did not trip — '{form}'"

FRAMEWORK IMPLEMENTATIONS

python

import re
import pytest

# Comprehensive regex matching all known fail-open forms. Each
# alternation is a CVE-class pattern that has bitten production at least once.
FAIL_OPEN_RE = re.compile(
    r"""
    (
        current_setting\([^)]*\)\s*=\s*''            |  # direct equality
        ''\s*=\s*current_setting\([^)]*\)            |  # commuted (PG keeps operand order)
        current_setting\([^)]*\)\s*::\s*\w+\s*=\s*'' |  # cast on the function call
        current_setting\([^)]*\)\s*IS\s+NULL         |  # IS NULL (unset GUC = fail-open)
        coalesce\(\s*current_setting\([^)]*\)\s*,\s*''\s*\)\s*=\s*''  # coalesce wrap
    )
    """,
    re.IGNORECASE | re.VERBOSE,
)

def policy_is_fail_open(policy_qual: str) -> bool:
    return bool(FAIL_OPEN_RE.search(policy_qual))

← All Patterns