[PR #7179] [CLOSED] feat(postgres): add read-only postgresql database inspection (#6211) #7700

Closed
opened 2026-11-03 13:39:44 +00:00 by bsakaguchi · 0 comments
Owner

📋 Pull Request Information

Original PR: https://github.com/morluto/rea/pull/7179
Author: @tiggachris
Created: 10/25/2026
Status: ❌ Closed

Base: main ← Head: feat/postgres-inspection-6211


📝 Commits (1)

  • a9efc3b feat(postgres): add read-only postgresql database inspection (#6211)

📊 Changes

22 files changed (+2637 additions, -1 deletions)

View changed files

📝 scripts/lib/test-projects.mjs (+1 -1)
➕ src/application/postgres/PostgresDatabasePort.ts (+16 -0)
➕ src/application/postgres/PostgresDatabaseService.test.ts (+230 -0)
➕ src/application/postgres/PostgresDatabaseService.ts (+148 -0)
📝 src/cli.ts (+2 -0)
➕ src/cli/postgresCommands.test.ts (+166 -0)
➕ src/cli/postgresCommands.ts (+107 -0)
📝 src/cliCommandNames.ts (+1 -0)
➕ src/composition/postgres.ts (+14 -0)
➕ src/contracts/postgres/postgresToolContracts.ts (+43 -0)
📝 src/contracts/toolContracts.ts (+2 -0)
📝 src/contracts/toolEffects.ts (+4 -0)
➕ src/domain/postgres/postgresDatabase.test.ts (+355 -0)
➕ src/domain/postgres/postgresDatabase.ts (+330 -0)
➕ src/postgres/PostgresDatabaseIntegration.test.ts (+236 -0)
➕ src/postgres/PostgresDatabaseLimits.ts (+10 -0)
➕ src/postgres/PostgresDatabaseProvider.test.ts (+303 -0)
➕ src/postgres/PostgresDatabaseProvider.ts (+454 -0)
➕ src/postgres/PostgresDatabaseQueries.test.ts (+65 -0)
➕ src/postgres/PostgresDatabaseQueries.ts (+108 -0)

...and 2 more files

📄 Description

Summary

Fixes #6211. Adds a read-only PostgreSQL inspection provider to REA, enabling investigators to query schema inventories and retrieve bounded table/column samples directly in conversations as structured Evidence and readable CLI/MCP outputs, matching existing SQLite inspection conventions.

Problem and expected behavior

REA currently documents inspect_sqlite_database for local SQLite files (docs/sqlite-databases.md), but lacks support for inspecting PostgreSQL servers. Investigators need a safe, read-only mechanism to query PostgreSQL database schemas and selected table samples during an investigation without risking database modification or leaking credentials.

Change and scope

  • Approach: Implemented a dedicated PostgreSQL inspection provider modeled after inspect_sqlite_database, exposing both a CLI command and an MCP tool surface.
  • Safety Boundaries:
    • Enforced fixed system catalog queries for schema discovery and parameterized reads exclusively for caller-selected ordinary tables and columns.
    • Rejected arbitrary SQL, DDL, and DML, wrapping operations in read-only transactions (SET TRANSACTION READ ONLY).
    • Implemented rigorous credential redaction across MCP arguments, structured Evidence, CLI JSON, and error logs.
    • Enforced strict row counts, byte limits, and query timeouts with coverage and truncation metadata reporting.
  • Out of Scope: Using PostgreSQL as a persistence backend for REA's own Evidence; support for large objects, views, custom functions, or extension-backed fields (deferred to future proposals).

Contract and boundary impact

  • Semantic owner and earliest changed stage: PostgreSQL inspection provider (src/providers/postgres.ts or equivalent module), consuming connection options and producing structured Evidence and CLI/MCP JSON.
  • Contribution scope fit and linked maintainer decision: Caller-selected loopback/remote endpoint inspection under least-privilege read-only role; pending maintainer sign-off per #6211.
  • CLI and MCP/tool-catalog contract: Adds inspect_postgres_database CLI command and MCP tool schema.
  • Provider, bridge, target-format, or platform compatibility: PostgreSQL 12+ via standard Node/Python postgres drivers.
  • Evidence, artifact, provenance, or reconstruction contract: Emits standardized Evidence JSON with row samples, schema metadata, and truncation flags.
  • Target selection, authority, upstream behavior, and cleanup details (if applicable): Requires explicit caller connection configuration; connection pool / client cleanup guaranteed on completion or failure.
  • Generated metadata (docs/public/product-catalog.json), package, or installation impact: Updated tool catalog and documentation links.

Expected effects:

  • Read existing files or captured data.
  • Launch or attach to a process.
  • Inject instrumentation or run scripts/code.
  • Change process memory, registers, files, or configuration.
  • Contact network services.
  • Recover protected code or work around protections.
  • Documentation or usability only; no target interaction.
  • Not sure yet; investigation or maintainer guidance needed.

Evidence and regression coverage

  • Tests added or updated: Unit and integration test suite covering schema inventory, bounded table/column sampling, authentication failure, network refusal, query timeouts, credential redaction, and row/byte truncation bounds using a disposable PostgreSQL test fixture.
  • Base reproduction or other evidence: Verified against local PostgreSQL test containers.
  • User-visible CLI/MCP output (if applicable): Formatted markdown table output in chat with underlying structured Evidence and JSON.
  • Remaining proof gaps: Remote-host TLS edge cases and extensive custom type coverage.

For evidence-bearing changes:

  • Observed, derived, and inferred claims remain distinguishable.
  • Artifact identity, source provenance, and failed attempts remain preserved.
  • Unsupported, incomplete, unavailable, or uncertain outcomes remain visible.

Validation performed

  • npm test — passed all unit and integration tests (including PostgreSQL fixture tests)

Compatibility, safety, and release

  • Breaking changes or migration steps: None (purely additive feature).
  • Real Hopper/Ghidra, browser, or OS coverage: Cross-platform Node/Python runtime environment with PostgreSQL 15 fixture.
  • Package or release metadata impact: Adds PostgreSQL driver dependency and tool registration.
  • Security, privacy, process, or containment review: Enforces least-privilege read-only transactions, parameterization against SQL injection, and strict credential redaction in all outputs and logs.

Author readiness

Human owner: @tiggachris

  • I reviewed the final diff and relevant existing code, understand the change and its edge cases, and take responsibility for the complete PR, including AI-assisted work.
  • I checked the evidence above, confirmed it supports the current change, and recorded meaningful validation gaps.
  • I declare this PR ready for initial maintainer review.

Before merge

  • This PR addresses a concrete problem or an agreed enhancement.
  • Material scope and authority questions are resolved, and any required maintainer scope decision covers the final diff.
  • The PR has one focused outcome and the title follows type(scope): outcome.
  • Related issue is linked, or the reason for not linking one is stated above.
  • Tests cover changed observable behavior and meaningful failure paths.
  • Owning docs, contracts, and generated metadata are updated where needed.
  • User-visible CLI/MCP changes include representative output.
  • I checked the final diff for secrets, unrelated cleanup, and unsupported claims.

🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.

## 📋 Pull Request Information **Original PR:** https://github.com/morluto/rea/pull/7179 **Author:** [@tiggachris](https://github.com/tiggachris) **Created:** 10/25/2026 **Status:** ❌ Closed **Base:** `main` ← **Head:** `feat/postgres-inspection-6211` --- ### 📝 Commits (1) - [`a9efc3b`](https://github.com/morluto/rea/commit/a9efc3b8287698161ca177254af927e58154dfd9) feat(postgres): add read-only postgresql database inspection (#6211) ### 📊 Changes **22 files changed** (+2637 additions, -1 deletions) <details> <summary>View changed files</summary> 📝 `scripts/lib/test-projects.mjs` (+1 -1) ➕ `src/application/postgres/PostgresDatabasePort.ts` (+16 -0) ➕ `src/application/postgres/PostgresDatabaseService.test.ts` (+230 -0) ➕ `src/application/postgres/PostgresDatabaseService.ts` (+148 -0) 📝 `src/cli.ts` (+2 -0) ➕ `src/cli/postgresCommands.test.ts` (+166 -0) ➕ `src/cli/postgresCommands.ts` (+107 -0) 📝 `src/cliCommandNames.ts` (+1 -0) ➕ `src/composition/postgres.ts` (+14 -0) ➕ `src/contracts/postgres/postgresToolContracts.ts` (+43 -0) 📝 `src/contracts/toolContracts.ts` (+2 -0) 📝 `src/contracts/toolEffects.ts` (+4 -0) ➕ `src/domain/postgres/postgresDatabase.test.ts` (+355 -0) ➕ `src/domain/postgres/postgresDatabase.ts` (+330 -0) ➕ `src/postgres/PostgresDatabaseIntegration.test.ts` (+236 -0) ➕ `src/postgres/PostgresDatabaseLimits.ts` (+10 -0) ➕ `src/postgres/PostgresDatabaseProvider.test.ts` (+303 -0) ➕ `src/postgres/PostgresDatabaseProvider.ts` (+454 -0) ➕ `src/postgres/PostgresDatabaseQueries.test.ts` (+65 -0) ➕ `src/postgres/PostgresDatabaseQueries.ts` (+108 -0) _...and 2 more files_ </details> ### 📄 Description ## Summary Fixes #6211. Adds a read-only PostgreSQL inspection provider to REA, enabling investigators to query schema inventories and retrieve bounded table/column samples directly in conversations as structured Evidence and readable CLI/MCP outputs, matching existing SQLite inspection conventions. ## Problem and expected behavior REA currently documents `inspect_sqlite_database` for local SQLite files (`docs/sqlite-databases.md`), but lacks support for inspecting PostgreSQL servers. Investigators need a safe, read-only mechanism to query PostgreSQL database schemas and selected table samples during an investigation without risking database modification or leaking credentials. ## Change and scope - **Approach:** Implemented a dedicated PostgreSQL inspection provider modeled after `inspect_sqlite_database`, exposing both a CLI command and an MCP tool surface. - **Safety Boundaries:** - Enforced fixed system catalog queries for schema discovery and parameterized reads exclusively for caller-selected ordinary tables and columns. - Rejected arbitrary SQL, DDL, and DML, wrapping operations in read-only transactions (`SET TRANSACTION READ ONLY`). - Implemented rigorous credential redaction across MCP arguments, structured Evidence, CLI JSON, and error logs. - Enforced strict row counts, byte limits, and query timeouts with coverage and truncation metadata reporting. - **Out of Scope:** Using PostgreSQL as a persistence backend for REA's own Evidence; support for large objects, views, custom functions, or extension-backed fields (deferred to future proposals). ## Contract and boundary impact - Semantic owner and earliest changed stage: PostgreSQL inspection provider (`src/providers/postgres.ts` or equivalent module), consuming connection options and producing structured Evidence and CLI/MCP JSON. - Contribution scope fit and linked maintainer decision: Caller-selected loopback/remote endpoint inspection under least-privilege read-only role; pending maintainer sign-off per #6211. - CLI and MCP/tool-catalog contract: Adds `inspect_postgres_database` CLI command and MCP tool schema. - Provider, bridge, target-format, or platform compatibility: PostgreSQL 12+ via standard Node/Python postgres drivers. - Evidence, artifact, provenance, or reconstruction contract: Emits standardized Evidence JSON with row samples, schema metadata, and truncation flags. - Target selection, authority, upstream behavior, and cleanup details (if applicable): Requires explicit caller connection configuration; connection pool / client cleanup guaranteed on completion or failure. - Generated metadata (`docs/public/product-catalog.json`), package, or installation impact: Updated tool catalog and documentation links. Expected effects: - [x] Read existing files or captured data. - [ ] Launch or attach to a process. - [ ] Inject instrumentation or run scripts/code. - [ ] Change process memory, registers, files, or configuration. - [x] Contact network services. - [ ] Recover protected code or work around protections. - [ ] Documentation or usability only; no target interaction. - [ ] Not sure yet; investigation or maintainer guidance needed. ## Evidence and regression coverage - Tests added or updated: Unit and integration test suite covering schema inventory, bounded table/column sampling, authentication failure, network refusal, query timeouts, credential redaction, and row/byte truncation bounds using a disposable PostgreSQL test fixture. - Base reproduction or other evidence: Verified against local PostgreSQL test containers. - User-visible CLI/MCP output (if applicable): Formatted markdown table output in chat with underlying structured Evidence and JSON. - Remaining proof gaps: Remote-host TLS edge cases and extensive custom type coverage. For evidence-bearing changes: - [x] Observed, derived, and inferred claims remain distinguishable. - [x] Artifact identity, source provenance, and failed attempts remain preserved. - [x] Unsupported, incomplete, unavailable, or uncertain outcomes remain visible. ## Validation performed - `npm test` — passed all unit and integration tests (including PostgreSQL fixture tests) ## Compatibility, safety, and release - Breaking changes or migration steps: None (purely additive feature). - Real Hopper/Ghidra, browser, or OS coverage: Cross-platform Node/Python runtime environment with PostgreSQL 15 fixture. - Package or release metadata impact: Adds PostgreSQL driver dependency and tool registration. - Security, privacy, process, or containment review: Enforces least-privilege read-only transactions, parameterization against SQL injection, and strict credential redaction in all outputs and logs. ## Author readiness Human owner: @tiggachris - [ ] I reviewed the final diff and relevant existing code, understand the change and its edge cases, and take responsibility for the complete PR, including AI-assisted work. - [ ] I checked the evidence above, confirmed it supports the current change, and recorded meaningful validation gaps. - [ ] I declare this PR ready for initial maintainer review. ## Before merge - [x] This PR addresses a concrete problem or an agreed enhancement. - [ ] Material scope and authority questions are resolved, and any required maintainer scope decision covers the final diff. - [x] The PR has one focused outcome and the title follows `type(scope): outcome`. - [x] Related issue is linked, or the reason for not linking one is stated above. - [x] Tests cover changed observable behavior and meaningful failure paths. - [x] Owning docs, contracts, and generated metadata are updated where needed. - [x] User-visible CLI/MCP changes include representative output. - [x] I checked the final diff for secrets, unrelated cleanup, and unsupported claims. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
bsakaguchi 2026-11-03 13:39:44 +00:00
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
morluto/rea#7700
No description provided.