Security

The observed database

The database QueryPilot watches is the most sensitive component in the system, so access to it is restricted at several layers:

• Read-only sessions. The agent connects with default_transaction_read_only=on; PostgreSQL rejects writes regardless of what the agent sends.
• Least privilege. A dedicated role with pg_read_all_stats is sufficient. Superusers are never needed. See PostgreSQL prerequisites.
• Bounded load. At most two connections, row caps on every collector query and a statement_timeout on every session.
• Plans are planned, not executed. Automatic and on-demand capture uses EXPLAIN (GENERIC_PLAN) for exactly one SELECT statement at a time, inside a READ ONLY transaction with a 100 ms lock timeout and a 2 s statement timeout, which is always rolled back. EXPLAIN ANALYZE is never run automatically.
• Outbound only. The agent initiates every connection. The API never connects to the agent; on-demand work is picked up by the agent polling for tasks.

Credentials

• Database passwords are encrypted at rest with AES-256-GCM using QUERYPILOT_ENCRYPTION_KEY (32 bytes, base64 or hex). An invalid key fails API startup instead of the first save.
• Passwords are never returned by the API. Response objects are built field by field, so a column added to the entity later cannot leak into a response.
• The setup command the UI generates contains a <password> placeholder; the stored password is never printed.
• Secrets come only from the environment (DATABASE_URL, QUERYPILOT_ENCRYPTION_KEY, provider API keys), never from the YAML configuration file. They are held in a separate structure, so the public configuration endpoint cannot serialize them.

Agent tokens

• Generated randomly and stored only as a SHA-256 hash; a leaked database dump does not yield usable tokens.
• Shown once, at creation or rotation.
• Rotation and revocation take effect immediately.
• Every agent-protocol route (/api/v1/agent/*) requires a valid, unrevoked bearer token; the check runs before the request body is validated.

API hardening

• Security headers via Helmet.
• Strict input validation: unknown properties are rejected, not silently dropped.
• Request bodies are limited (8 MiB by default, QUERYPILOT_MAX_PAYLOAD_BYTES).
• Rate limiting per client IP (300 requests per minute by default). Health probes and /metrics are exempt so monitoring keeps working during an incident.
• CORS is off (same-origin only) unless QUERYPILOT_CORS_ORIGINS lists allowed origins.
• A consistent error envelope that never includes secrets, and a request ID on every request.
• An audit trail of operator actions, such as connection tests and settings changes.

Data privacy

• Query parameters are never stored. pg_stat_statements text is already normalized by PostgreSQL, and QueryPilot normalizes it again (literals become ?).
• Query text can be turned off with QUERYPILOT_STORE_QUERY_TEXT=false. Agents are told at registration and then never send text; fingerprints, metrics and plans keep working.
• Plans are sanitized on the observed host, before they are buffered or sent. A plan carries the query’s predicates verbatim (Filter: (email = 'alice@…')), so every string in a plan is stripped of literal values unless it is a known identifier or label, such as a relation or index name.
• Nothing leaves your infrastructure. There is no phone-home telemetry; every optional integration (AI, exporters, cloud) defaults to off and none is implemented yet.

Not in place yet

Roadmap These are designed but not implemented. Plan your deployment accordingly:

• User authentication and authorization. Anyone who can reach the web app or API can use it. Deploy on a trusted network or behind an authenticating proxy.
• TLS termination in the services themselves. Put a reverse proxy in front of the API and web app.
• Automatic retention. retention.* settings exist, but old telemetry is not yet purged automatically.
• Webhook signing and SSRF protection arrive with the webhook exporter.

Reporting a vulnerability

Please report security issues privately, never in a public issue. Once the repository is public, use its GitHub security advisories; until then, contact the maintainers directly.