File Download (/file)

3 min read

File Download

The /file endpoint serves binary files generated by a PostgreSQL function — no base64 inside JSON-RPC. One returned file is sent as-is; several files are packed into a ZIP archive on the fly.

POST /{prefix}/{database}/file

Authentication, role switching (SET LOCAL ROLE), rate limiting and the request size limit are identical to /jsonrpc. The caller needs EXECUTE on the function.

Request

{
  "method": "api.export_documents",
  "params": { "count": 3 },
  "options": { "filename": "export.zip", "force_zip": false, "compression_level": 6 },
  "idempotencyKey": "optional"
}

Field	Description
`method`	Required. `schema.function`, called as `fn(params::jsonb)`.
`params`	Passed to the function as `jsonb` (default `{}`).
`options.filename`	Name of the downloaded file/ZIP. Default: the file’s own name, or `export-YYYYMMDD-HHMMSS.zip`.
`options.force_zip`	Always return a ZIP, even for a single file.
`options.compression_level`	`0` = store only, `1`–`9`; default `6`.

Database contract

The function takes one jsonb argument and returns a set of rows. path and content are required; mime_type and store_only are optional.

CREATE FUNCTION api.export_documents(params jsonb)
RETURNS TABLE (path text, content bytea, mime_type text, store_only boolean)
LANGUAGE sql AS $$
  SELECT 'report.csv', convert_to('a,b' || E'\n' || '1,2', 'UTF8'), 'text/csv', false;
$$;

path must be a plain relative path that is safe to extract on every operating system. A path is rejected (the request fails with 500 and the problem is logged) if it:
- is empty, longer than 512 bytes, not valid UTF-8, or contains control characters;
- is absolute (/…, C:…) or contains a backslash;
- has an empty, . or .. segment;
- has a segment ending in a dot or a space (Windows strips them, so ".. " would become ..);
- contains : (NTFS alternate data stream) or any of < > " | ? *;
- uses a reserved Windows device name as a segment, with or without an extension (CON, PRN, AUX, NUL, COM0–COM9, LPT0–LPT9);
- collides with another path in the same response: names are compared case-insensitively (a.txt and A.TXT clash on macOS/Windows), and a file cannot also be a directory (a and a/b).
content NULL is an empty file.
mime_type is used only if it is a well-formed media type, otherwise guessed from the extension (fallback application/octet-stream).
store_only = true stores the ZIP entry uncompressed (needed e.g. for the EPUB mimetype file).

Response

Rows	`force_zip`	Result
0	any	404 (JSON error); the transaction is rolled back, so nothing the function did is persisted and an `idempotencyKey` is not consumed
1	false	the file itself, with its media type
1	true	ZIP with one entry
≥ 2	any	ZIP (`application/zip`)

curl -X POST http://localhost:8080/db/my_database/file \
  -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -d '{"method":"api.export_documents","params":{"count":3}}' -OJ

The download name (options.filename, or the file’s own name) is sanitised: path separators, quotes and reserved characters become _, and control, bidirectional-override, zero-width and line-separator characters are removed, so a name cannot be disguised as another file type.

Errors are always JSON, never binary: 400 invalid request, 401 unauthenticated, 403 no permission, 404 unknown function or no rows, 413 the response exceeds FILE_MAX_BYTES / FILE_MAX_ENTRIES, 500 function failed or returned an invalid structure.

Safe by construction

The database controls only file names, contents and media types — never HTTP headers. Responses are always Content-Disposition: attachment with X-Content-Type-Options: nosniff, Content-Security-Policy: sandbox and Cache-Control: no-store, so a function returning text/html or SVG cannot run script in the API’s origin. File names are sanitised. The whole result is buffered in memory: the real bound per request is FILE_MAX_BYTES plus the largest single row, because the database driver reads a row completely before the limit can be checked — keep the number of concurrent downloads in mind. The ZIP itself is streamed.

Discovery

A set-returning function declared with RETURNS TABLE (…) or OUT parameters that include path and content columns is reported by capabilities with "kind": "file" and endpoint /file. The OpenAPI spec documents a real POST /{prefix}/{database}/file operation (only for roles that can execute a file function); file functions are not exposed as MCP tools.

← Real-time Notifications (SSE) Idempotency Cleanup →