---
id: w5/m9/t001
title: Read-only query verb in the postgres feature (timeout, row cap, RO transaction)
worker: worker5
status: done
estimate: 30m
depends_on: []
---

## Objective

Add a `Query(ctx, dbID, sql)` verb to the postgres feature that connects to the managed database over its internal connection string and executes the statement inside a hard-read-only envelope: `BEGIN READ ONLY` + `SET default_transaction_read_only = on`, a server-side statement timeout (e.g. 10s), and a result row cap (e.g. 500 rows) — returning columns + rows in a shape the MCP tool can serialize.

## Context

- The feature already reads CNPG's `<name>-app` Secret for `connection-info` (`lego/backend/internal/postgres/`) — reuse that credential path; connect via the internal service DNS (bex-api runs in-cluster next to the databases).
- Read-only must be enforced by the database session, not by SQL parsing (agents produce creative SQL); parsing-based denylists are explicitly not the mechanism.
- Values and query text must never hit logs or error messages — same rule as env-var values (`docs/ADR006-bex-api.md`).

## Steps

1. Dial with pgx using the internal connection string; set `default_transaction_read_only=on` + `statement_timeout` on the session/connection string.
2. Run the statement in an explicit read-only transaction; collect up to the row cap; report truncation.
3. Map errors: unknown db → `ErrNotFound`; RO violation / timeout → typed, value-free errors.

## Files

- `lego/backend/internal/postgres/service.go` (+ a small query helper file)

## Acceptance criteria

- [ ] `SELECT` returns rows; `INSERT`/`UPDATE`/`CREATE` fail with the read-only error.
- [ ] `pg_sleep(60)` fails at the statement timeout; >cap results are truncated and flagged.
- [ ] No query text or values in logs.

## Out of scope

- MCP registration (t002); REST/GraphQL exposure (none — matching Render); connection pooling/PgBouncer.
