[PR #7223] docs(design): propose read-only PostgreSQL inspection (#6211) #7727

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

📋 Pull Request Information

Original PR: https://github.com/morluto/rea/pull/7223
Author: @tiggachris
Created: 10/26/2026
Status: 🔄 Open

Base: main ← Head: docs/postgresql-inspection-design


📝 Commits (3)

  • e4c707f docs(design): propose read-only PostgreSQL inspection (#6211)
  • e2ffe13 docs(design): address review findings and fix links in postgresql-inspection.md
  • 707d3d9 docs(design): make PR documentation-only and address review findings

📊 Changes

1 file changed (+398 additions, -0 deletions)

View changed files

➕ docs/design/postgresql-inspection.md (+398 -0)

📄 Description

Summary

Relates to #6211

Adds a formal Design Proposal and RFC in docs/design/postgresql-inspection.md for read-only PostgreSQL database inspection (inspect_postgresql_database / rea inspect-postgresql-database). It defines the target interaction boundary, zero-mutation invariants, credential isolation, JSON-safe type mapping, resource ceilings, and test fixture strategy for maintainer scope review prior to code integration.

Problem and expected behavior

Reverse-engineering applications backed by relational databases (such as desktop, containerized, or client-server software) frequently requires inspecting persistent state to analyze authorization models, session flags, cryptographic parameters, or schemas.

REA currently provides inspect_sqlite_database for local offline SQLite snapshots, but has no provider for PostgreSQL targets. Using ad-hoc CLI commands or raw SQL violates REA's safety posture, risks accidental mutations, leaks secrets into command lines and process tables, and lacks structured Evidence retention. This RFC establishes the architectural boundaries required by REA's fail-closed integration rule before writing provider code.

Change and scope

  • Added docs/design/postgresql-inspection.md containing the complete RFC.
  • Updated docs/.vitepress/config.ts to register the proposal in documentation navigation.
  • Strictly documentation-only: no production provider code, dependencies, or runtime changes are introduced in this PR.

Key boundaries established in the proposal:

  1. Zero Mutation Authority: Disallows arbitrary SQL input; enforces SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY and wraps reads in BEGIN TRANSACTION READ ONLY with unconditional ROLLBACK.
  2. Network & Scope Boundary: Restricts Phase 1 strictly to loopback interfaces (127.0.0.1, ::1, localhost) and Unix domain sockets to prevent SSRF and align with local analysis.
  3. Zero Credential Leakage: Passwords excluded from MCP arguments; credentials resolved strictly via ambient environment (PGPASSWORD, PGPASSFILE); connection URIs unconditionally redacted in Evidence and logs (postgresql://user:***@host:port/db).
  4. Bounded Sampling: Bounded row_limit (default 20, max 100); 100-column projection limit; 64-bit integers and arbitrary-precision decimals serialized as strings to preserve JSON precision; cell byte limits for bytea/text.
  5. Resource Limits: 5 s handshake timeout, 10 s statement timeout (server + client), and guaranteed socket destruction on AbortSignal.

Contract and boundary impact

  • Semantic owner and earliest changed stage: docs/design/postgresql-inspection.md (documentation).
  • Contribution scope fit and linked maintainer decision: Relates to #6211; formal scope decision requested for read-only network database inspection.
  • CLI and MCP/tool-catalog contract: Proposes inspect_postgresql_database and rea inspect-postgresql-database for future implementation.
  • Provider, bridge, target-format, or platform compatibility: None (documentation only).
  • Evidence, artifact, provenance, or reconstruction contract: Specifies EvidenceObservation schema and credential redaction semantics.
  • Target selection, authority, upstream behavior, and cleanup details (if applicable): Zero-mutation read-only transaction contract defined.
  • Generated metadata (docs/public/product-catalog.json), package, or installation impact: None.

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: None (documentation-only change).
  • Base reproduction or other evidence: Cross-referenced with docs/sqlite-databases.md, docs/tool-design.md, and docs/design/limits-and-recovery.md.
  • User-visible CLI/MCP output (if applicable): None.
  • Remaining proof gaps: Provider implementation and automated Docker container fixture (postgres:16-alpine) deferred to Phase 2 implementation following maintainer scope approval.

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

  • Verified Git status and diff cleanly encompasses only docs/design/postgresql-inspection.md and docs/.vitepress/config.ts.
  • Validated relative Markdown link targets against docs/architecture.mermaid, docs/sqlite-databases.md, and docs/tool-design.md.

Compatibility, safety, and release

  • Breaking changes or migration steps: None.
  • Real Hopper/Ghidra, browser, or OS coverage: Not applicable.
  • Package or release metadata impact: None.
  • Security, privacy, process, or containment review: Proposal incorporates strict credential redaction and SSRF prevention.

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/7223 **Author:** [@tiggachris](https://github.com/tiggachris) **Created:** 10/26/2026 **Status:** 🔄 Open **Base:** `main` ← **Head:** `docs/postgresql-inspection-design` --- ### 📝 Commits (3) - [`e4c707f`](https://github.com/morluto/rea/commit/e4c707f2485a47ccd226d0e7f93f29b853bdbfaf) docs(design): propose read-only PostgreSQL inspection (#6211) - [`e2ffe13`](https://github.com/morluto/rea/commit/e2ffe13e3c0f737b791593cb7d702fee301917f3) docs(design): address review findings and fix links in postgresql-inspection.md - [`707d3d9`](https://github.com/morluto/rea/commit/707d3d9572e879635762f8bb696fb2c9a48e66d0) docs(design): make PR documentation-only and address review findings ### 📊 Changes **1 file changed** (+398 additions, -0 deletions) <details> <summary>View changed files</summary> ➕ `docs/design/postgresql-inspection.md` (+398 -0) </details> ### 📄 Description <!-- PR title: docs(design): propose read-only PostgreSQL inspection (#6211) --> ## Summary Relates to #6211 Adds a formal Design Proposal and RFC in `docs/design/postgresql-inspection.md` for read-only PostgreSQL database inspection (`inspect_postgresql_database` / `rea inspect-postgresql-database`). It defines the target interaction boundary, zero-mutation invariants, credential isolation, JSON-safe type mapping, resource ceilings, and test fixture strategy for maintainer scope review prior to code integration. ## Problem and expected behavior Reverse-engineering applications backed by relational databases (such as desktop, containerized, or client-server software) frequently requires inspecting persistent state to analyze authorization models, session flags, cryptographic parameters, or schemas. REA currently provides `inspect_sqlite_database` for local offline SQLite snapshots, but has no provider for PostgreSQL targets. Using ad-hoc CLI commands or raw SQL violates REA's safety posture, risks accidental mutations, leaks secrets into command lines and process tables, and lacks structured Evidence retention. This RFC establishes the architectural boundaries required by REA's fail-closed integration rule before writing provider code. ## Change and scope - Added `docs/design/postgresql-inspection.md` containing the complete RFC. - Updated `docs/.vitepress/config.ts` to register the proposal in documentation navigation. - Strictly documentation-only: no production provider code, dependencies, or runtime changes are introduced in this PR. Key boundaries established in the proposal: 1. **Zero Mutation Authority**: Disallows arbitrary SQL input; enforces `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY` and wraps reads in `BEGIN TRANSACTION READ ONLY` with unconditional `ROLLBACK`. 2. **Network & Scope Boundary**: Restricts Phase 1 strictly to loopback interfaces (`127.0.0.1`, `::1`, `localhost`) and Unix domain sockets to prevent SSRF and align with local analysis. 3. **Zero Credential Leakage**: Passwords excluded from MCP arguments; credentials resolved strictly via ambient environment (`PGPASSWORD`, `PGPASSFILE`); connection URIs unconditionally redacted in Evidence and logs (`postgresql://user:***@host:port/db`). 4. **Bounded Sampling**: Bounded `row_limit` (default 20, max 100); 100-column projection limit; 64-bit integers and arbitrary-precision decimals serialized as strings to preserve JSON precision; cell byte limits for `bytea`/text. 5. **Resource Limits**: 5 s handshake timeout, 10 s statement timeout (server + client), and guaranteed socket destruction on `AbortSignal`. ## Contract and boundary impact - Semantic owner and earliest changed stage: `docs/design/postgresql-inspection.md` (documentation). - Contribution scope fit and linked maintainer decision: Relates to #6211; formal scope decision requested for read-only network database inspection. - CLI and MCP/tool-catalog contract: Proposes `inspect_postgresql_database` and `rea inspect-postgresql-database` for future implementation. - Provider, bridge, target-format, or platform compatibility: None (documentation only). - Evidence, artifact, provenance, or reconstruction contract: Specifies `EvidenceObservation` schema and credential redaction semantics. - Target selection, authority, upstream behavior, and cleanup details (if applicable): Zero-mutation read-only transaction contract defined. - Generated metadata (`docs/public/product-catalog.json`), package, or installation impact: None. 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. - [x] Documentation or usability only; no target interaction. - [ ] Not sure yet; investigation or maintainer guidance needed. ## Evidence and regression coverage - Tests added or updated: None (documentation-only change). - Base reproduction or other evidence: Cross-referenced with `docs/sqlite-databases.md`, `docs/tool-design.md`, and `docs/design/limits-and-recovery.md`. - User-visible CLI/MCP output (if applicable): None. - Remaining proof gaps: Provider implementation and automated Docker container fixture (`postgres:16-alpine`) deferred to Phase 2 implementation following maintainer scope approval. 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 - Verified Git status and diff cleanly encompasses only `docs/design/postgresql-inspection.md` and `docs/.vitepress/config.ts`. - Validated relative Markdown link targets against `docs/architecture.mermaid`, `docs/sqlite-databases.md`, and `docs/tool-design.md`. ## Compatibility, safety, and release - Breaking changes or migration steps: None. - Real Hopper/Ghidra, browser, or OS coverage: Not applicable. - Package or release metadata impact: None. - Security, privacy, process, or containment review: Proposal incorporates strict credential redaction and SSRF prevention. ## Author readiness Human owner: @tiggachris - [x] 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. - [x] I checked the evidence above, confirmed it supports the current change, and recorded meaningful validation gaps. - [x] 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. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
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#7727
No description provided.