REST API and personal access tokens

The REST API gives scripts and CI pipelines the same workflow the web UI has. A request filed through the API passes the same SQL guards, waits for the same human approval, and lands in the same audit trail as one filed in the browser. Nothing runs until a reviewer approves it.

The endpoint reference is the OpenAPI 3.1 description in the repository, docs/openapi.yaml. This page explains how tokens work and walks through a complete flow.

If you only need to file a request, wait for it and fetch the result from a terminal or a pipeline, the qp command-line client does the polling for you.

Personal access tokens

Every call carries a personal access token as a bearer token:

Authorization: Bearer <id>|qp_<secret>

• A token belongs to one user and never grants more than that user may do in the web UI. Its scopes can only narrow that.
• A token always expires. You choose the lifetime when you create it, up to QUERYPROXY_API_TOKEN_MAX_DAYS days (default 90).
• A token stops working when its owner is suspended or deleted. There are no service accounts: automation uses the token of a real user.
• A user the QUERYPROXY_REQUIRE_2FA policy covers, who has not enrolled in two-factor authentication yet, gets no API access: a token must not stand in for the second factor. The token works again once the user has enrolled.

Create and revoke a token

Open Profile, then API tokens. Name the token, pick its scopes and set the number of days it lasts, then create it. The value is shown once, with a Copy button; QueryProxy stores only its hash, and a reload hides the value for good. Revoking a token stops it at once. Creating and revoking are audited as token.created and token.revoked.

Creating a token asks you to confirm first:

• With a local password, enter it, or a code from your authenticator app when two-factor authentication is on. Failed attempts are rate-limited.
• An account without a local password (SSO only) must have signed in through SSO in the last 15 minutes. Otherwise the page sends you through SSO again and brings you back.

Scopes

The Profile page offers only the scopes your roles can use.

Rules the API keeps

• No approval bypass. A request waits for a human decision exactly as in the web UI.
• No overrides. An approval that would be an override (the reviewer is a system administrator who is not a DBA of the request’s team, or is the requester) needs a written reason. The API refuses it with 403 and an override_url that points to the web form. The API has no override of its own.
• A token can never approve its owner’s own request.
• Results are masked by the connection’s masking rules, as everywhere else. Column names of a result are result data: an auditor does not get them, and an auditor cannot read a result at all.
• Auditors see redacted SQL. sql_original and sql_prepared are null for a viewer who may not see literal values; sql_redacted carries the statement with the literals replaced.

A leaked token held by a reviewer can still approve other users’ requests inside its approvals:write scope, so give that scope sparingly and keep the lifetime short.

Rate limits

Each token may make QUERYPROXY_API_RATE_LIMIT calls per minute (default 60). Above that the API answers 429 with a Retry-After header. Failed authentications are limited per address to the same number. Responses carry X-RateLimit-Limit and X-RateLimit-Remaining.

Walkthrough with curl

The examples assume two variables. QP is your installation, and TOKEN holds the token value.

export QP=https://queryproxy.example.com
export TOKEN='7|qp_...'

1. Find the connection. GET /api/v1/connections lists the connections you can use, without hosts or credentials.

curl -s -H "Authorization: Bearer $TOKEN" -H 'Accept: application/json' \
  "$QP/api/v1/connections"

2. Submit the request. Needs requests:submit. connection_id and sql are required; title (up to 150 characters) and reason (up to 1000) are optional. The answer is 201, with the request in status pending.

curl -s -X POST "$QP/api/v1/requests" \
  -H "Authorization: Bearer $TOKEN" -H 'Accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{"connection_id": 3, "sql": "SELECT id, status FROM orders", "title": "Orders check", "reason": "Support ticket"}'

The SQL guard can refuse the statement with 422 and a list of violations. A connection you cannot see is reported as 404; one you can see but may not submit to (an auditor, or a read grant for a write) is refused with 403.

3. Follow the request. Needs requests:read. Poll until status is completed (or failed, rejected, cancelled). The statuses are pending, approved, rejected, queued, running, completed, failed and cancelled.

curl -s -H "Authorization: Bearer $TOKEN" -H 'Accept: application/json' \
  "$QP/api/v1/requests/42"

4. Approval. A DBA of the team (not the requester) approves in the web UI, in Slack or Teams, or through the API with the reviewer’s own token that has approvals:write (REVIEWER_TOKEN below):

curl -s -X POST -H "Authorization: Bearer $REVIEWER_TOKEN" -H 'Accept: application/json' \
  "$QP/api/v1/requests/42/approve"

curl -s -X POST "$QP/api/v1/requests/42/reject" \
  -H "Authorization: Bearer $REVIEWER_TOKEN" -H 'Accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{"reason": "Add a WHERE clause"}'

A decided request answers 409. The requester can withdraw a pending request with POST /api/v1/requests/42/cancel.

5. Read the result. Needs results:read. The default is NDJSON, one JSON array per row with values in column order (the column names are in result.columns of the request). Send Accept: text/csv for CSV with a header row.

curl -s -H "Authorization: Bearer $TOKEN" "$QP/api/v1/requests/42/result"

curl -s -H "Authorization: Bearer $TOKEN" -H 'Accept: text/csv' \
  "$QP/api/v1/requests/42/result" -o result.csv

The response headers X-QueryProxy-Row-Count and X-QueryProxy-Truncated tell you how many rows were stored and whether the result was cut at the row ceiling. A request that has not completed, has failed, or whose result has been pruned answers 409; 404 means the stored file is missing from the result disk. Every read is audited as result.downloaded.

Errors

Errors are JSON with a message. Validation errors (422) add errors, keyed by field.

In the audit log

API submissions, approvals, rejections, cancellations and result reads carry channel = api. Token creation and revocation are token.created and token.revoked. See Audit chain and SIEM export.

Settings

Next steps

• The qp client for a ready-made command-line client of this API.
• Approval workflow for who may approve what.
• Single sign-on if your users sign in through a provider.
• Production hardening for token hygiene.