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. 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).

Related MCP server: MCP DB Gateway

Setting it up (one database, annie-prod)

1. In the database, as an administrator (names are examples):

   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:

   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.