> **Plain-language companion:** [v0.44.0.md](v0.44.0.md)

## v0.44.0 — Security Hardening & Code Quality

**Status: Planned.** Derived from [plans/PLAN_OVERALL_ASSESSMENT_9.md](../plans/PLAN_OVERALL_ASSESSMENT_9.md) §Dimension 7 (Security), §Dimension 2 (Code Quality).

> **Release Theme**
> Reduce the security and code-quality regression surface: harden SECURITY
> DEFINER paths, centralize dynamic SQL, add RLS warnings, decompose large
> modules, consolidate API options, and improve the parser unsafe facade.

---

### Features

| ID | Title | Effort | Priority | Assessment ref |
|----|-------|--------|----------|----------------|
| A45-1 | Remove `public` from IVM SECURITY DEFINER search_path | M | P0 | SEC-01 |
| A45-2 | Centralized SQL-building module (`src/sql_builder.rs`) | L | P0 | SEC-02, CQ-02 |
| A45-3 | RLS bypass creation-time WARNING/NOTICE | S | P1 | SEC-03 |
| A45-4 | Monitoring demo credential hardening | S | P1 | SEC-04 |
| A45-5 | SECURITY DEFINER CI check | S | P1 | SEC-06 |
| A45-6 | Document superuser requirement | S | P2 | SEC-05 |
| A45-7 | Module decomposition of large files | L | P1 | CQ-01 |
| A45-8 | `CreateStreamTableOptions` struct | M | P1 | CQ-07 |
| A45-9 | Parser typed unsafe facade + fuzzing targets | L | P1 | CQ-05 |
| A45-10 | Structured warnings in scheduler BGW paths | M | P2 | CQ-06 |
| A45-11 | Replace milestone comment IDs with invariant docs | S | P2 | CQ-08 |

**A45-1 — Remove `public` from IVM search_path.**
In `src/ivm.rs`, remove `public` from the SECURITY DEFINER function's
`SET search_path`. Schema-qualify all user-table references in generated delta
SQL using the schema captured at stream-table creation time. If `public` is
required for specific edge cases (user-defined operators in public), document
the requirement explicitly and restrict to those cases only.

**A45-2 — Centralized SQL-building module.**
Create `src/sql_builder.rs` with:
- `ident(name: &str) -> String` — safe identifier quoting
- `qualified(schema: &str, name: &str) -> String` — schema.table
- `literal(value: &str) -> String` — string literal with proper escaping
- `regclass(oid: Oid) -> String` — OID to regclass cast
- `spi_param(index: usize) -> String` — `$N` placeholder
- `list_idents(names: &[&str]) -> String` — comma-separated identifiers

Migrate all dynamic SQL construction in `src/cdc.rs`, `src/wal_decoder.rs`,
`src/dvm/parser/rewrites.rs`, `src/refresh/codegen.rs`, and `src/api/helpers.rs`
to use these helpers. Add a CI lint check (grep or semgrep) that flags manual
`format!("... '{}'")` patterns in production Rust.

**A45-3 — RLS bypass warning.**
In `create_stream_table_impl` (and create-or-replace variant), check if any
source table has RLS enabled. If so, emit a `WARNING` via `pgrx::warning!()`
explaining that source-table RLS does not protect stream-table contents and
that RLS should be applied on the stream table itself.

**A45-4 — Monitoring credential hardening.**
In `monitoring/docker-compose.yml`:
- Replace hardcoded `POSTGRES_PASSWORD=postgres` with `${POSTGRES_PASSWORD}`
- Replace Grafana `admin/admin` with environment variables
- Add `monitoring/.env.example` with commented defaults
- Bind services to `127.0.0.1` in examples where possible
- Add prominent comments that credentials must be changed for shared
  environments

**A45-5 — SECURITY DEFINER CI check.**
Add a CI check (script or workflow step) that:
- Finds all `SECURITY DEFINER` in Rust source and SQL migration files
- Asserts each has a corresponding `SET search_path`
- Asserts none include user-writable schemas unless explicitly justified
  with a comment

**A45-6 — Document superuser requirement.**
Add a section to `docs/SECURITY_MODEL.md` (or `INSTALL.md`) explaining:
- Why `superuser = true` and `trusted = false` are required
- Exact privileges needed at install time vs runtime
- Why trusted install is not currently supported
- Guidance for managed/hosted environments

**A45-7 — Module decomposition.**
Continue splitting:
- `src/api/mod.rs` → extract create validation, alter logic, storage DDL
- `src/scheduler/mod.rs` → extract launcher, worker entry points, job execution
- `src/dvm/parser/sublinks.rs` → separate SubLink extraction from deparsing
- `src/cdc.rs` → separate trigger generation from buffer management

**A45-8 — `CreateStreamTableOptions` struct.**
Introduce a single struct used by all `create_stream_table` variants (SQL,
JSON bulk, create-or-replace). Centralize defaults, validation, serialization,
and docs generation. All API paths construct this struct first, then pass it
to a single implementation function.

**A45-9 — Parser typed unsafe facade.**
Consolidate unsafe parse-tree access behind a smaller typed facade layer that:
- Maps PostgreSQL node types to safe Rust enums
- Validates node type before access
- Provides safe iteration over lists
- Reports unsupported nodes as typed errors

Add deparser round-trip fuzzing targets that generate random valid parse trees,
deparse them, re-parse, and verify structural equivalence.

**A45-10 — Structured warnings in scheduler BGW.**
Replace `unwrap_or` silence patterns in `src/scheduler/mod.rs` and
`src/scheduler/pool.rs` with structured `pgrx::warning!()` calls that include
database name, query context, and error class. Add counters exposed through
diagnostics.

**A45-11 — Replace milestone comment IDs.**
Audit all code comments containing milestone IDs (`SCAL-5`, `C2-1`, `DI-8`,
etc.) and replace with current invariant descriptions. Keep milestone IDs only
when they link to a live design doc.

### Test Coverage

| ID | Title | Effort | Priority | Assessment ref |
|----|-------|--------|----------|----------------|
| T-A45-1 | IVM search_path hardening regression test | M | P0 | SEC-01 |
| T-A45-2 | SQL-builder module unit tests | M | P0 | SEC-02, CQ-02 |
| T-A45-3 | RLS bypass warning E2E test | S | P1 | SEC-03 |
| T-A45-4 | SECURITY DEFINER CI check validation | S | P1 | SEC-06 |
| T-A45-5 | CreateStreamTableOptions equivalence tests | M | P1 | CQ-07 |
| T-A45-6 | Parser deparser round-trip fuzz targets | L | P1 | CQ-05 |

**T-A45-1.**
E2E test: create a function in `public` schema that shadows a pg_catalog
function, create a stream table, verify that the IVM trigger does NOT resolve
through `public`. (Previously this would resolve through public.)

**T-A45-2.**
Unit tests for `sql_builder` module: identifier quoting with special characters,
schema-qualified names, literal escaping with quotes/backslashes/unicode,
regclass round-trip, and parameter indexing.

**T-A45-3.**
E2E test: enable RLS on a source table, create a stream table, verify that a
WARNING is emitted during creation.

**T-A45-5.**
Verify that all `create_stream_table` variants (SQL, JSON bulk, create-or-
replace) produce identical catalog state when given the same logical options.

**T-A45-6.**
Fuzz targets that generate random SQL expressions, deparse through the typed
facade, re-parse, and verify the round-trip produces equivalent structures.

### Conflicts & Risks

- **A45-1** may break user queries that rely on unqualified resolution through
  `public` in stream table definitions. Add a migration note and consider a
  compatibility GUC if breakage is widespread.
- **A45-2** is a large refactoring across many modules. Land incrementally
  per-module rather than as a single large commit.
- **A45-7** module splits can create merge conflicts with other work. Coordinate
  with any in-progress feature branches.

### Exit Criteria

- [ ] A45-1: IVM trigger functions do not include `public` in search_path
- [ ] A45-2: All dynamic SQL in production code uses `sql_builder` helpers
- [ ] A45-3: RLS bypass warning emitted on stream table creation
- [ ] A45-5: SECURITY DEFINER CI check passes
- [ ] A45-7: No production module exceeds 2,000 lines (target)
- [ ] A45-8: All create_stream_table variants use CreateStreamTableOptions
- [ ] A45-9: Parser unsafe access goes through typed facade
- [ ] Extension upgrade path tested (`0.44.0 → 0.45.0`)
- [ ] `just lint` passes with zero warnings
- [ ] `just test-all` passes