Security

This page describes how to deploy QueryPilot components safely, and equally what the current release does not protect against. The Router is the SQL proxy (binary querypilot), Analytics is the analytics server (binary querypilot-analytics), the Accelerator is the caching plugin, and Guard is the policy plugin.

QueryPilot is designed to run on trusted networks. Management surfaces default to loopback, non-loopback exposure requires explicit opt-ins, and wire encryption is not available in this release. Treat the network between clients, the Router, and backends as part of your security boundary.

Client authentication

Pool credentials double as client credentials. There is no dedicated client-auth section and no per-user credential passthrough in GA v1. Clients must authenticate with the username/password of the first pool that defines them, and the Router then uses those same credentials to log into the backend. One secret, two jobs: anyone who can authenticate to the Router acts as that backend user, and rotating the backend password simultaneously rotates what every client must present.

Mechanisms per protocol:

• PostgreSQL listeners authenticate clients with SCRAM-SHA-256, so the password itself does not cross the wire during authentication. Without TLS, the queries and results after authentication do.
• MySQL listeners authenticate clients with mysql_native_password.

Client auth failures are observable via sqp_auth_failures_total (labeled by protocol and reason).

The open-proxy guard

If no pool defines credentials, the Router accepts any client credentials. In that state, a listener on a non-loopback address would be an open proxy: any client that can reach the port gets a connection to your backend with the pool's identity. The Router therefore refuses to start when an unauthenticated listener binds a non-loopback address, unless you explicitly set allow_anonymous = true under [general].

allow_anonymous = true exists for loopback development and for deployments where the network layer itself is the access control (a dedicated compose network, Kubernetes NetworkPolicy, security groups). The tradeoff is exactly what the name says: authentication is off, and reachability equals access. Leave it false in production and put credentials on your pools instead.

TLS status

Until wire TLS ships, put the Router in the same trust domain as its clients and backends: the same host (loopback), a private network segment, a VPN or service mesh that encrypts at the network layer, or SSH tunnels. One caution when reaching for a fronting proxy or load balancer to terminate TLS: PostgreSQL and MySQL negotiate TLS inside their own protocols rather than as a raw TLS socket, so a generic TLS terminator does not work for database clients that expect in-protocol negotiation. Network-layer encryption (mesh, VPN, tunnel) is the reliable pattern.

On the client-facing PostgreSQL path, wire-protocol input parsing is hardened against malformed frames: message length is capped at 256 MiB and attacker-controlled length prefixes are bounds-checked rather than trusted.

Admin API and management surfaces

The Router's core admin API (/api/translations, /api/config/*, /api/pattern-rules, /api/pools, /api/routing/test, /api/stats/digests) is unauthenticated. It is safe only because it defaults to loopback: the admin API always binds the metrics IP at the metrics port + 1 (127.0.0.1:9091 by default). Treat "admin API reachable" as equivalent to "control over the proxy's routing". Two consequences:

• Widening metrics.bind to 0.0.0.0 for Prometheus also exposes the mutating admin API. There is no way to expose one without the other. Firewall both ports and set the plugin api_keys before doing this.
• Bind metrics to loopback or a private management network, never a client-reachable interface.

Plugin routes on the same port carry their own bearer keys:

• Accelerator (/api/v1/plugins/accelerator/*): mutating endpoints require the X-QueryPilot-Api-Key header, compared in constant time. When the admin API binds a non-loopback address and no plugins.accelerator.api_key is set, the mutating endpoints refuse to serve (403), and unauthenticated reads redact query text.
• Guard (/api/v1/plugins/guard/*): mutating endpoints require X-Guard-Api-Key when plugins.guard.api_key is set. Plan around two caveats: with no api_key set the mutating endpoints are open (acceptable only on loopback), and POST /test, which runs the policy engine against arbitrary SQL, does not require the key even when one is set. The key comparison is also a plain string compare rather than constant-time. Keep the guard surface loopback-only or behind an authenticating reverse proxy.

Set both api_keys whenever the metrics/admin bind is anything other than 127.0.0.1. See the admin API reference.

Analytics has no authentication at all, on either its PostgreSQL wire port or its HTTP API. That is exactly what its loopback guard exists for: it refuses non-loopback server.bind / server.api_bind unless --unsafe-bind-all (or QUERYPILOT_ANALYTICS_UNSAFE_BIND_ALL=true) is set. The flag is a gate, not a config value; it permits the addresses you configured, nothing more. One exception to review separately: the guard covers server.bind and server.api_bind only, so [metrics] bind can reach 0.0.0.0 without the flag. Keep Analytics on loopback or an isolated network segment; the shipped containers set the flag because containers must bind 0.0.0.0, and they assume you secure the published ports at the network layer.

Guard: what it protects against

Frame Guard as protection against accidents and misbehaving tools (an agent issuing a DELETE without a WHERE, a migration script touching the wrong table), not as a hard boundary against a motivated adversary.

The default is fail-open on unanalyzable statements: unanalyzable_action = "log" allows statements the analyzer cannot classify, emitting a throttled warning and incrementing sqp_guard_unanalyzable_total. A determined client below the privileged trust level can construct statements that are unanalyzable on purpose. If you need enforcement rather than observability, fail closed: set unanalyzable_action = "block", or deploy a policy matching the Unanalyzable risk factor. Whichever you choose, alert on sqp_guard_unanalyzable_total; it is the observability control for the fail-open path.

Two further limitations to plan around in this release: trust rules matching on origin never fire (the client's network address is not threaded into the session context; match on username or role instead), and the guard API caveats described above. In enforce mode, Guard fails closed on analyzer panics.

Every Guard decision is written to the configured audit_sink: { type = "stdout" } (default) or { type = "file", path = "..." }, one JSON record per line. Audit files are created with mode 0640, and records are fingerprint-only: they carry the query fingerprint, username, trust level, coarse query type, tables, action, policy name, reason, amendments, and decision latency, never raw SQL text or literal values, so the audit log cannot leak query contents. The pipeline is observable; sqp_guard_audit_dropped_total counts records lost to backpressure. See Guard and the policies tutorial.

Secrets in configuration files

QueryPilot configs contain live credentials in plaintext TOML: pool username/password in querypilot.toml, the replication source_url (with password) in querypilot-analytics.toml, and plugin api_keys. There is no built-in secret-manager integration, so protect the files themselves:

• chmod 600 every config file and have the service user own it.
• In Kubernetes, mount configs from Secret objects rather than ConfigMaps when they carry passwords. The provided manifests template credentials in from a Secret at pod start.
• Keep configs out of world-readable images and repositories; template credentials in at deploy time.
• GET /api/config/status reports the config path, not its contents, but anyone with admin-API access can trigger a reload of whatever is on disk.

The standalone Accelerator additionally requires https plus an API token for any non-loopback sqp_api_url. The QUERYPILOT_ACCELERATOR_ALLOW_INSECURE_HTTP=1 escape hatch exists for disposable test environments only; in production it would send the token and cache-management traffic unencrypted. Never set it outside tests.