Query Whitelist

16 tests demonstrate how Signando Postgres validates SQL queries against a predefined whitelist using dual-hash fingerprinting.

Configuration

The query whitelist is defined in the policy.yaml file:

# policy.yaml
queries:
  - name: "get_user_by_id"
    sql: "SELECT id, name, email FROM users WHERE id = $1"
    allowed_users: ["app_user", "admin"]
    max_rows: 1

  - name: "list_active_users"
    sql: "SELECT id, name FROM users WHERE active = $1"
    allowed_users: ["app_user"]
    max_rows: 100

WHITELIST-001PASS

Whitelisted Query - Allowed

Sample Request

SELECT id, name, email FROM users WHERE id = $1

Expected Response

 id | name       | email
----+------------+-------------------
  1 | John Doe   | john@example.com
(1 row)

The query matches a whitelisted template. The dual-hash fingerprint (XXHash64 + SipHash128) matches, so the query is forwarded to the database.

WHITELIST-002BLOCKED

Non-Whitelisted Query - Blocked

Sample Request

SELECT * FROM users

Expected Response

ERROR: Query not in whitelist
DETAIL: The query fingerprint does not match any allowed query template.
HINT: Add the query to your policy.yaml or use learn mode to auto-generate rules.

Queries not matching any whitelisted template are immediately rejected. The database never sees the query.

WHITELIST-003BLOCKED

Query with Additional Columns - Blocked

Sample Request

SELECT id, name, email, password_hash FROM users WHERE id = $1

Even though the base query is similar, adding 'password_hash' changes the fingerprint. The modified query is blocked.