App keys

An app key is the credential you put in a deployed app. It never expires, it rotates without downtime, and Postgres itself limits it to the tables you grant. Use one whenever an app talks to BataDB over HTTP (POST /v1/sql) or sends Turbine telemetry.

Never put an admin key or a plain sql key in a deployment. A plain sql key runs its SQL as the project's owner role, which can do anything to your data. Both kinds of key also expire, which is an outage on a date nobody wrote down. An app key runs its SQL as a role that can do exactly what you granted and nothing else.

The minimal example: counting page views from a Vercel function#

This is the whole setup for an app that records page views and never reads anything back.

1. Create the table#

Run this once with any admin connection (the dashboard SQL editor, bata db query, or psql with your connection string):

CREATE TABLE analytics (
  id       bigserial PRIMARY KEY,
  path     text NOT NULL,
  referrer text,
  at       timestamptz NOT NULL DEFAULT now()
);

2. Create an insert-only app key#

bata keys create --app --name pageviews --project <project-id> --grant insert:analytics

Or in the console: Settings, API Keys, Create API Key, App key, pick the project, then tick Insert on public.analytics.

The key is shown once. It is bound to one project, one branch (the primary branch unless you pass --branch) and one database (the branch's default database unless you pass --database).

What that key can do, enforced by Postgres:

3. Call it from the function#

Put the key in the function's environment as BATA_APP_KEY (a Vercel environment variable, never in code). Then:

// app/api/pageview/route.ts (Next.js on Vercel; Node or Edge runtime)
export async function POST(req: Request) {
  const { path, referrer } = await req.json().catch(() => ({}));
  if (typeof path !== 'string') return new Response('path required', { status: 400 });
  const body = JSON.stringify({
    query: 'INSERT INTO analytics (path, referrer) VALUES ($1, $2)',
    params: [path.slice(0, 512), typeof referrer === 'string' ? referrer.slice(0, 512) : null],
  });
  for (let attempt = 1; ; attempt++) {
    const res = await fetch('https://api.batadata.com/v1/sql', {
      method: 'POST',
      headers: { authorization: `Bearer ${process.env.BATA_APP_KEY}`, 'content-type': 'application/json' },
      body,
    });
    if (res.ok) return new Response(null, { status: 204 });
    // 503: the database is waking from scale-to-zero. 429: the key is busy. Wait as told, twice at most.
    if ((res.status !== 503 && res.status !== 429) || attempt === 3) return new Response('not recorded', { status: 502 });
    await new Promise((r) => setTimeout(r, Number(res.headers.get('retry-after') ?? 1) * 1000));
  }
}

No project or branch header is needed: the key's binding is the default. Sending x-project-id or x-branch-id is fine as long as they match the binding; anything else is refused with 403 KEY_PROJECT_DENIED or KEY_BRANCH_DENIED.

@batadata/serverless works too (new Pool({ apiKey: process.env.BATA_APP_KEY })), and it retries a waking database's 503 for you. You only need that driver, or plain fetch, on an edge runtime. On a Node server you can use the pooled connection string with a normal driver or ORM instead (see Connecting). That connects as the project owner, not as an app key's role.

INSERT ... RETURNING needs SELECT#

Postgres requires SELECT on the returned columns. An insert-only key gets permission denied for table analytics for INSERT ... RETURNING id. Either drop RETURNING, or grant SELECT:

bata keys grant <key-id> --grant select:analytics

That grant is enforced on the key's next query. Note that it also lets the key read the whole table.

4. Rotate with zero downtime#

bata keys rotate <key-id> --overlap 24h

This prints a new secret for the same key (same database role, same grants). The old secret keeps working until the overlap ends, then stops.

1. Put the new secret in BATA_APP_KEY and redeploy.
2. Once the new deployment is live, either wait out the overlap or end it now with bata keys revoke <old-secret-id>. bata keys list shows the old secret as app (retiring), with the day it stops in the EXPIRES column (expires_at in --json has the exact time).

The overlap is 24h by default, from 0 to 7 days. --overlap takes 0, a number of seconds, or a duration such as 30m, 24h or 7d. --overlap 0 stops the old secret immediately. In the console: Rotate, then choose how long the old secret keeps working.

Managing app keys#

The MCP server has the same operations: api_keys_create (with kind: "app"), api_keys_list, api_keys_grant, api_keys_rotate and api_keys_revoke. The full flag reference is in the CLI reference.

Grants are privilege:table or privilege:schema.table. Privileges are select, insert, update and delete. A key holds at most 100 grants. Unquoted names fold to lower case the way Postgres does. Quote a mixed-case name, and quote it again for your shell: --grant 'select:"Events"'. A table must exist before it can be granted (400 APP_KEY_TABLE_NOT_FOUND). The system schemas (information_schema and every pg_ schema) and the platform's own schemas cannot be granted.

Grants name tables and partitioned tables only. A view, materialized view or foreign table is refused with 400 APP_KEY_NOT_A_TABLE, and nothing changes. A view reads its base tables with its owner's privileges, so a grant on one would reach data the key's grant list does not show. Grant the underlying table instead. A grant on a partitioned table covers its partitions when you query through it.

An app key without grants is refused unless it is telemetry-only (scopes: ["telemetry:write"]). Telemetry-only app keys are what telemetry_setup (MCP) and "Create telemetry key" (console) make.

Revoking an app key stops every secret of it at once and removes its role from the database: right away if the compute is running, otherwise the next time it runs. Nothing can log in as the role in between. Revoking only a rotated-out secret ends that secret's overlap and leaves the key working.

What an app key can reach#

• Routes: POST /v1/sql, and POST /v1/insights/:projectId/ingest and /observe when it has telemetry:write. Every other route answers 403 KEY_SCOPE_DENIED, including /v1/whoami, the dashboard SQL routes (/v1/sql/rows, /v1/sql/execute, /v1/sql/tables), time travel, connection info and key management. An app key can never read the admin password or widen itself.
• Telemetry reports only for the key's own branch. A branchId in the ingest body, or a branch_id query on /observe, that names another branch gets 403 KEY_BRANCH_DENIED. No branch means the key's own branch: the rows are stored under it, never as project-wide.
• Database: only what its role holds: the granted privileges on each table, USAGE on those tables' schemas, and USAGE on the sequences behind the defaults of tables it may insert into or update (so serial columns work). The role is not a superuser and cannot create roles or databases.
• What every role gets from PUBLIC still applies, and BataDB does not change your database to take it away. The key reports what that adds beyond its grants: see Extra access through PUBLIC. The one exception is BataDB's own pg_stat_statements, which BataDB closes to PUBLIC itself (see BataDB's own pg_stat_statements).
• Row-level security works: policies TO the key's role apply to it. The role name is printed when the key is created and shown in the console list and in bata keys list --json (database_role).
• Each key runs a limited number of queries at once. Past that, a request answers 429 APP_KEY_BUSY with Retry-After.
• All app keys on one compute together use only part of its connections, so they cannot crowd out your own app. Past that, a request answers 429 APP_KEYS_COMPUTE_BUSY with Retry-After. See Connection share per compute.

Extra access through PUBLIC#

Every Postgres role, the key's included, gets whatever your database grants to PUBLIC. BataDB never changes your database to remove that. Instead, after every create and every grant change, BataDB checks what the key's role can actually do and reports anything beyond its grants as extra_access:

• table privileges it was not granted (from a GRANT ... TO PUBLIC),
• SECURITY DEFINER functions and procedures it can execute in schemas it can use, outside pg_catalog and information_schema (these run as their owner),
• schemas where it can CREATE,
• CREATE on the database.

TEMP tables are expected and not reported.

Where it shows:

Each item carries the SQL that closes it. Run it as the owner:

Revoking from PUBLIC affects every role that relied on it. Grant the privilege back to the roles that need it by name.

The check is a snapshot. A later GRANT ... TO PUBLIC is not seen until the key's next grant change. extraAccess is null for a key made before the check existed; any grant change fills it in. At most 50 items are listed.

BataDB's own pg_stat_statements#

Every compute has the pg_stat_statements extension, which Insights reads. Its install script grants its views to PUBLIC. BataDB closes them to PUBLIC itself, in every database where the extension is installed, so a key does not report them:

• PUBLIC loses the pg_stat_statements and pg_stat_statements_info views and the functions behind them.
• Members of pg_read_all_stats keep reading the statistics, pg_monitor and your project's owner role included.
• Insights is unaffected.

A key created before this change keeps the old warning until its next grant change.

Timeouts#

A /v1/sql request has a timeout: options.timeout in the body, 1000 to 120000 ms, 30000 by default. For an app key the API enforces it itself, so a SET statement_timeout in the key's SQL does not extend it.

• A query that runs past the timeout is ended and answers 504 QUERY_TIMEOUT.
• A request that times out is never committed. Everything the statement wrote in the request's transaction is rolled back. A text that sends its own COMMIT has committed what came before it, as with any client.
• An app key's statement always runs inside the API's transaction. A statement that cannot (VACUUM, CREATE INDEX CONCURRENTLY, ALTER SYSTEM) is refused with the Postgres error.

Connection share per compute#

An app key connects to Postgres directly, so every running app-key query holds a real connection. To keep your own app's connections free, all app keys on one compute together may use only part of its connections. Past that share a request is refused at once with 429 APP_KEYS_COMPUTE_BUSY and Retry-After, before anything is dialed. Retry after the wait it gives. A bigger compute has more connections, so its share is bigger too.

Expiry warnings for CLI and integration keys#

A CLI or integration key that expires within 14 days and was used in the last 7 days gets one email to the person who created it, with how to replace it (bata login, or bata keys rotate <id>). App keys never expire and never get one.