Plain-language companion: v0.5.0.md

v0.5.0 — Row-Level Security & Operational Controls

Status: Released (2026-03-13).

Goal: Harden the security context for stream tables and IVM triggers, add source-level pause/resume gating for bulk-load coordination, and deliver small ergonomic improvements.

Row-Level Security (RLS) Support

In plain terms: Row-level security lets you write policies like “user Alice can only see rows where tenant_id = 'alice'”. Stream tables already honour these policies when users query them. What this work fixes is the machinery behind the scenes — the triggers and refresh functions that build the stream table need to see all rows regardless of who is running them, otherwise they’d produce an incomplete result. This phase hardens those internal components so they always have full visibility, while end-users still see only their filtered slice.

Stream tables materialize the full result set (like MATERIALIZED VIEW). RLS is applied on the stream table itself for read-side filtering. Phase 1 hardens the security context; Phase 2 adds a tutorial; Phase 3 completes DDL tracking. Phase 4 (per-role security_invoker) is deferred to post-1.0.

Item Description Effort Ref
R1 Document RLS semantics in SQL_REFERENCE.md and FAQ.md 1h PLAN_ROW_LEVEL_SECURITY.md §3.1 | ✅ Done
R2 Disable RLS on change buffer tables (ALTER TABLE ... DISABLE ROW LEVEL SECURITY) 30min PLAN_ROW_LEVEL_SECURITY.md §3.1 R2 | ✅ Done
R3 Force superuser context for manual refresh_stream_table() (prevent “who refreshed it?” hazard) 2h PLAN_ROW_LEVEL_SECURITY.md §3.1 R3 | ✅ Done
R4 Force SECURITY DEFINER on IVM trigger functions (IMMEDIATE mode delta queries must see all rows) 2h PLAN_ROW_LEVEL_SECURITY.md §3.1 R4 | ✅ Done
R5 E2E test: RLS on source table does not affect stream table content 1h PLAN_ROW_LEVEL_SECURITY.md §3.1 R5 | ✅ Done
R6 Tutorial: RLS on stream tables (enable RLS, per-tenant policies, verify filtering) 1.5h PLAN_ROW_LEVEL_SECURITY.md §3.2 R6 | ✅ Done
R7 E2E test: RLS on stream table filters reads per role 1h PLAN_ROW_LEVEL_SECURITY.md §3.2 R7 | ✅ Done
R8 E2E test: IMMEDIATE mode + RLS on stream table 30min PLAN_ROW_LEVEL_SECURITY.md §3.2 R8 | ✅ Done
R9 Track ENABLE/DISABLE RLS DDL on source tables (AT_EnableRowSecurity et al.) in hooks.rs 2h PLAN_ROW_LEVEL_SECURITY.md §3.3 R9 | ✅ Done
R10 E2E test: ENABLE RLS on source table triggers reinit 1h PLAN_ROW_LEVEL_SECURITY.md §3.3 R10 | ✅ Done

RLS subtotal: ~8–12 hours (Phase 4 security_invoker deferred to post-1.0)

Bootstrap Source Gating

In plain terms: A pause/resume switch for individual source tables. If you’re bulk-loading 10 million rows into a source table (a nightly ETL import, for example), you can “gate” it first — the scheduler will skip refreshing any stream table that reads from it. Once the load is done you “ungate” it and a single clean refresh runs. Without gating, the CDC system would frantically process millions of intermediate changes during the load, most of which get immediately overwritten anyway.

Allow operators to pause CDC consumption for specific source tables (e.g. during bulk loads or ETL windows) without dropping and recreating stream tables. The scheduler skips any stream table whose transitive source set intersects the current gated set.

Item Description Effort Ref
BOOT-1 pgtrickle.pgt_source_gates catalog table (source_relid, gated, gated_at, gated_by) 30min PLAN_BOOTSTRAP_GATING.md | ✅ Done
BOOT-2 gate_source(source TEXT) SQL function — sets gate, pg_notify scheduler 1h PLAN_BOOTSTRAP_GATING.md | ✅ Done
BOOT-3 ungate_source(source TEXT) + source_gates() introspection view 30min PLAN_BOOTSTRAP_GATING.md | ✅ Done
BOOT-4 Scheduler integration: load gated-source set per tick; skip and log SKIP in pgt_refresh_history 2–3h PLAN_BOOTSTRAP_GATING.md | ✅ Done
BOOT-5 E2E tests: single-source gate, coordinated multi-source, partial DAG, bootstrap with initialize => false 3–4h PLAN_BOOTSTRAP_GATING.md | ✅ Done

Bootstrap source gating subtotal: ~7–9 hours

Ergonomics & API Polish

In plain terms: A handful of quality-of-life improvements: track when someone manually triggered a refresh and log it in the history table; a one-row quick_health view that tells you at a glance whether the extension is healthy (total tables, any errors, any stale tables, scheduler running); a create_stream_table_if_not_exists() helper so deployment scripts don’t crash if the table was already created; and CALL syntax wrappers so the functions feel like native PostgreSQL commands rather than extension functions.

Item Description Effort Ref
ERG-D Record manual refresh_stream_table() calls in pgt_refresh_history with initiated_by='MANUAL' 2h PLAN_ERGONOMICS.md §D | ✅ Done
ERG-E pgtrickle.quick_health view — single-row status summary (total_stream_tables, error_tables, stale_tables, scheduler_running, status) 2h PLAN_ERGONOMICS.md §E | ✅ Done
COR-2 create_stream_table_if_not_exists() convenience wrapper 30min PLAN_CREATE_OR_REPLACE.md §COR-2 | ✅ Done
NAT-CALL CREATE PROCEDURE wrappers for all four main SQL functions — enables CALL pgtrickle.create_stream_table(...) syntax 1h Deferred — PostgreSQL does not allow procedures and functions with the same name and argument types

Ergonomics subtotal: ~5–5.5 hours (NAT-CALL deferred)

Performance Foundations (Wave 1)

These quick-win items from PLAN_NEW_STUFF.md ship alongside the RLS and operational work. Read the risk analyses in that document before implementing any item.

Item Description Effort Ref
A-3a MERGE bypass — Append-Only INSERT path: expose APPEND ONLY declaration on CREATE STREAM TABLE; CDC heuristic fallback (fast-path until first DELETE/UPDATE seen) 1–2 wk PLAN_NEW_STUFF.md §A-3 | ✅ Done

A-4, B-2, and C-4 deferred to v0.6.0 Performance Wave 2 (scope mismatch with the RLS/operational-controls theme; correctness risk warrants a dedicated wave).

Performance foundations subtotal: ~10–20h (A-3a only)

v0.5.0 total: ~51–97h

Exit criteria: - [x] RLS semantics documented; change buffers RLS-hardened; IVM triggers SECURITY DEFINER - [x] RLS on stream table E2E-tested (DIFFERENTIAL + IMMEDIATE) - [x] gate_source / ungate_source operational; scheduler skips gated sources correctly - [x] quick_health view and create_stream_table_if_not_exists available - [x] Manual refresh calls recorded in history with initiated_by='MANUAL' - [x] A-3a: Append-Only INSERT path eliminates MERGE for event-sourced stream tables - [x] Extension upgrade path tested (0.4.0 → 0.5.0)