Token roles

Every PerSQL API token carries a role and an optional table scope. The defaults preserve the old behavior — newly created tokens default to admin (full access) — but new tokens can be tightened down to just what an agent or service actually needs.

Roles

Role	Can run	Cannot run
admin	anything (default)	—
readwrite	INSERT / UPDATE / DELETE, SELECT, PRAGMA	CREATE / ALTER / DROP / TRUNCATE / ATTACH / DETACH / REINDEX / VACUUM; blob upserts and deletes are still allowed
readonly	SELECT, PRAGMA, blob GET and LIST	anything mutating

The classifier is a regex prefix match — sufficient to block obvious violations. The DO is still the source of truth, so even an admin token can’t write rows that the database’s CHECK constraints reject.

Table scope

When tableScope is set on a token, the SQL /v1/.../query and /v1/.../batch accept must reference only the listed tables. Any SQL that touches an unscoped table is rejected with 403.

# Token created with tableScope=["orders","order_items"]
curl https://api.persql.com/v1/db/acme/orders/query \
  -H "Authorization: Bearer psql_live_…" \
  -d '{"sql": "SELECT * FROM customers"}'
# 403 — Token scope does not include table "customers"

The scope check is best-effort: it scans for tokens after FROM, JOIN, INTO, UPDATE, and TABLE. CTEs and aliases that don’t match the reference pattern can slip through. Use tableScope as a defense-in-depth layer, not as your only line of defense.

Creating a scoped token

In the console: Tokens → Create token. Pick a role and optionally list the tables (comma-separated).

# REST equivalent
curl https://api.persql.com/api/namespaces/acme/tokens \
  -H "Cookie: …" \
  -d '{
    "name": "agent-readonly",
    "role": "readonly",
    "tableScope": ["orders", "order_items"]
  }'

Backward compatibility

• Tokens created before this feature shipped automatically migrated to role = "admin" with no scope. Behavior is unchanged.
• Token validation is KV-cached for 60 seconds. After changing a token (which today means revoke + recreate), expect up to a minute before the new token takes effect across the edge.