Summary

sql_guard - a lightweight SQL firewall at the connection pooler layer that filters queries using POSIX extended regular expressions before they reach the PostgreSQL backend.

• Blacklist mode (sql_guard "blacklist") - blocks queries matching dangerous patterns (SQL injection prevention: DROP TABLE, UNION SELECT, COPY PROGRAM, pg_sleep, etc.)
• Whitelist mode (sql_guard "whitelist") - allows only queries matching the regex through; blocks everything else (strict access control for untrusted users, contractors, reporting tools)
• Hash cache (sql_guard_cache yes) - optional direct-mapped murmur hash cache (4096 entries) that skips regex on repeated queries (~3.2x speedup)
• Case-insensitive matching via REG_ICASE
• Per-route configuration: each database/user rule can have its own mode, regex set, and cache setting independently

Use cases

• Legacy apps without prepared statements
• Third-party software where SQL cannot be controlled
• Untrusted database access (contractors, auditors) requiring restrictions beyond PostgreSQL GRANT/REVOKE
• Defense-in-depth layer alongside application-level protections
• PCI DSS / SOC 2 compliance requirements

Config validation

Invalid combinations are rejected at startup (odyssey --test):

• sql_guard "blacklist" without sql_guard_regex -> error

• sql_guard_regex without sql_guard -> error

• sql_guard_cache yes without sql_guard -> error

  Functional tests:

• test/functional/tests/sql_guard/ - 34 test queries (blocked + allowed + case-insensitive)

• test/functional/tests/config-validation/configs/sql_guard/ - 6 configs (3 valid, 3 invalid)

Performance

Performance tests:

• test/perf/ - CI-repeatable benchmark with pass/fail validation
• test/benchmark-sql-guard/ - full benchmark, make benchmark-sql-guard regenerates results

For run benchmark use command:
make benchmark-sql-guard
Results are saved to test/benchmark-sql-guard/results.txt.

CI performance tests (pass/fail validation):
make ci-perf-test

Example benchmark results:

Cache hit rate: 100% after initial pass (20 unique queries, 4096 slots). Cache never fills up, new entries overwrite old ones, worst case is one extra regex call.

Config example

database "production" {
    user "app" {
        sql_guard "blacklist"
        sql_guard_cache yes
        sql_guard_regex "\b(DROP|TRUNCATE)\s+(TABLE|DATABASE|SCHEMA)\b"
        sql_guard_regex "\bUNION\s+(ALL\s+)?SELECT\b"
        sql_guard_regex "\bCOPY\s+\w+\s+(FROM|TO)\s+PROGRAM\b"
    }
    user "auditor" {
        sql_guard "whitelist"
        sql_guard_cache yes
        sql_guard_regex "^\s*SELECT\b"
    }
}