# Read-only SQL

A Paseo plugin that serves one or more databases to agents as an MCP server on the daemon's loopback, meant to sit behind
[MCP Proxy](https://github.com/lerenn/paseo-mcp-proxy). The agent never holds the database password or even the service's
token: MCP Proxy does, and the connection string stays in a file on the daemon.

Why daemon-side: a pod has no route to the database, and giving a pod the credentials would put them in an agent's reach.

## What it enforces

- **Two keys, both on the daemon**: `dsnFile` (the connection string) and `tokenFile` (the bearer MCP Proxy presents).
  Neither is shown to agents, written to the audit log, or returned in an error (a DSN in an error message is replaced).
- **SELECT only, in three layers**: a check for one `SELECT`/`WITH`/`VALUES`/`TABLE` statement; a `READ ONLY` transaction
  (always rolled back); and the database role, which you create with `SELECT` rights only (see below). The query is run as
  `SELECT * FROM (<query>) LIMIT $1`, so Postgres' extended protocol refuses a second statement.
- **Limits per database**: `statementTimeoutMs` (15 s), `maxRows` (500), `maxBytes` (400 KB), `queriesPerMinute` (30), cells cut at 4000 characters.
- **Audit**: every call, denied ones too, goes to `<paseo home>/plugin-data/readonly-sql/audit.jsonl` (0600, rotated at 10 MB): database, tool,
  the SQL, outcome, rows, time. The audit log cannot say which agent asked (MCP Proxy is the caller); MCP Proxy's own audit has the agent.

Tools: `query {sql}` and `describe_tables {table?}`. The endpoint is `http://127.0.0.1:47852/db/<name>/mcp`
(`PASEO_READONLY_SQL_PORT` changes the port).

## Setting it up (one database, `annie-prod`)

1. **In the database**, as an administrator (names are examples):
   ```sql
   CREATE ROLE paseo_ro LOGIN PASSWORD '<generated>' NOSUPERUSER NOCREATEDB NOCREATEROLE;
   ALTER ROLE paseo_ro SET default_transaction_read_only = on;
   ALTER ROLE paseo_ro SET statement_timeout = '15s';
   GRANT CONNECT ON DATABASE annie TO paseo_ro;
   GRANT USAGE ON SCHEMA public TO paseo_ro;
   -- the PR Blast Reviewer's tables only (runs and reports, rules, rule signals and ledger, run rule refs, feedback and votes, settings, alerts)
   DO $$
   DECLARE t text;
   BEGIN
     FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'pr\_blast\_reviewer\_%' LOOP
       EXECUTE format('GRANT SELECT ON public.%I TO paseo_ro', t);
     END LOOP;
   END $$;
   GRANT SELECT (id, name) ON public.projects TO paseo_ro;   -- two columns of projects, nothing else
   -- no default privileges: a new table stays invisible until someone grants it
   ```
   Prefer a read replica. Check what the role sees: `SELECT table_name FROM information_schema.tables WHERE table_schema='public'` as `paseo_ro` lists only the granted tables (that is also what `describe_tables` shows an agent).
2. **On the daemon host**, as the daemon's user:
   ```sh
   install -d -m 700 ~/.paseo/ops-secrets
   umask 077
   printf '%s' 'postgres://paseo_ro:<password>@<host>:5432/annie?sslmode=require' > ~/.paseo/ops-secrets/annie-prod.dsn
   head -c 32 /dev/urandom | base64 | tr -d '\n=' > ~/.paseo/ops-secrets/annie-prod.token
   ```
   The database host must be reachable from the daemon host.
3. **Plugin settings** (`readonly-sql`): `databases.annie-prod = { dsnFile: "/home/<user>/.paseo/ops-secrets/annie-prod.dsn", tokenFile: "/home/<user>/.paseo/ops-secrets/annie-prod.token", description: "Annie production, read replica" }`.
4. **MCP Proxy entry** (in the git source of definitions): `name: annie-prod-sql`, `url: http://127.0.0.1:47852/db/annie-prod/mcp`, `auth: api-key`.
5. **Give the proxy the token** without it passing through an agent: with daemon-ops, `mcp_key_set(server="annie-prod-sql", sourceRef="file:/home/<user>/.paseo/ops-secrets/annie-prod.token")`.
6. **Attach it only to the agents that need it**: MCP Proxy's `labelServers` setting, e.g. `{"paseo.prbr=1": "annie-prod-sql, annie-prod, launchdarkly"}`, grants it to new agents carrying that label (or set `PASEO_MCP_SERVERS=annie-prod-sql` in a role's environment).

Rotating the password: rewrite the DSN file; the next query reconnects. Rotating the token: rewrite the token file, then `mcp_key_set` again.