# pg_promise_guard [![CI](https://github.com/Manuelreyesbravo/pg_promise_guard/actions/workflows/ci.yml/badge.svg)](https://github.com/Manuelreyesbravo/pg_promise_guard/actions/workflows/ci.yml) Finds the guarantees your schema **claims** to give and silently stopped giving. ```sql CREATE EXTENSION pg_promise_guard; SELECT kind, object, relation, severity FROM promise_guard.promise_breaks; ``` ``` kind | object | relation | severity ----------------------+-------------------------+-----------------+---------- invalid_unique_index | app.customers_tax_id_uk | app.customers | breach disabled_trigger | app.audit_insert | app.invoices | breach unenforced_rls | app.tenant_data | app.tenant_data | breach not_valid_constraint | app.amount_positive | app.invoices | gap ``` The view has two more columns, `detail` and `evidence`. Everything lives in the schema `promise_guard`, so name it, or add it to your `search_path`. ## The failure it exists for A migration runs `CREATE UNIQUE INDEX CONCURRENTLY`. It fails on duplicates that were already in the table — and it **leaves the index behind**, marked unique, marked invalid. From that moment `\d` says `UNIQUE`, your ORM says unique, your application logic assumes unique, and **nothing is enforced**. Duplicates keep going in. No error, no log line, no alert. The failure was loud exactly once, in a deploy log nobody reads twice. The broken state it leaves behind is silent forever. That asymmetry is the whole point: a loud, one-off event is precisely what needs a periodic sentinel rather than one more alert. Same shape, different clothes: | what the schema says | what is actually happening | |---|---| | `UNIQUE` index | invalid — duplicates go in | | `CHECK` / `FOREIGN KEY` constraint | `NOT VALID` — the rows already there were never checked | | audit trigger | `DISABLED` for a bulk load, never re-enabled | | row level security | `ENABLED` but not `FORCED` — the table owner bypasses every policy | ## Why not amcheck `amcheck` verifies that a **valid** index is structurally sound. These indexes are structurally perfect and simply **not in effect**. Different question, different answer — running amcheck on an invalid index tells you nothing about the uniqueness you think you have. ## breach vs gap - **breach** — the schema states a guarantee that is *not being enforced right now*. Wrong data can be entering as you read this. - **gap** — the guarantee holds from here on but does not cover what was already there. A `NOT VALID` constraint mid-migration is a legitimate, deliberate state. `promises_kept()` only goes false on a **breach**. A monitor that goes red for a deliberate intermediate state gets silenced — and takes the real breaches with it. ```sql SELECT promise_guard.promises_kept(); -- whole database SELECT promise_guard.promises_kept('app'); -- one schema SELECT * FROM promise_guard.check_promises('app'); ``` ## Cost Reads the system catalogs only. No user data, no locks, no shared library, and no required extension: pg_living_assertions is needed only for `watch()`. Safe to run on a busy production database. Measured with `bench/scan_cost.sh` on a catalog of 1,000 tables (5,390 `pg_class` rows, 3,004 constraints, 5,000 triggers, 3,155 indexes): about 10 ms per `promises_kept()` or `check_promises()` call (`bench/results.md` has the machine and the method), so running it every minute costs little. Time it on your own catalog with the same script. It reads the catalog it means to read. Since 0.2.7 every function searches `pg_catalog` first: until 0.2.6 the extension's own schema came first, and in an installation that began at 0.1.0 that schema is `public`, where a role allowed to create could add an empty `public.pg_trigger` and blind the scanner (external audit; `test/from_010.sh`). **What it reports, since 0.2.8.** Beyond invalid indexes, NOT VALID constraints, disabled triggers and RLS that is not forced: NOT ENFORCED constraints (PostgreSQL 18) and disabled foreign-key triggers are breaches, as are a policy on a table without row level security, a disabled rule and a disabled event trigger. A trigger that fires only in replica mode, a NOT VALID domain constraint, a UNIQUE index that is invalid but ready (new rows are checked), an index being built right now, and RLS not forced on a table whose owner cannot log in are gaps. Objects of an extension owned by a superuser are skipped; an extension a tenant owns is not. It runs under READ COMMITTED only, and a schema that does not exist is an error, not a clean bill of health. What it cannot see: `session_replication_role = replica` set by a superuser skips triggers and foreign keys without leaving anything in the catalog. ## What it does NOT do Declared here rather than hidden in the version number: - It reports **state**, not history. It cannot tell you *when* the index went invalid or *who* disabled the trigger. - It does not check whether a valid index is corrupt — that is `amcheck`. - It does not look at permissions, `search_path` shadowing, or default privileges, all of which can also break assumptions people call "guarantees". - Objects that belong to an extension are skipped: an extension's own design decisions are not your database's mistakes. If you want to audit those too, query the catalogs directly. ## Tested on 0.2.10 was measured on 2026-11-12, not assumed, on local builds of PostgreSQL 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6 and 19rc1: `make installcheck` and `ci/upgrade_check.sh` from every earlier release (0.1.0 to 0.2.9) on each, and `make check-suites` (`check-pgtemp`, `check-from-010`, `check-audit`) on 18 and 19. All green. `watch()` was also run with pg_living_assertions 0.5.6, the oldest version it accepts. The CI in `.github/workflows` runs installcheck and the upgrade check on 11 to 19. | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | |:--:|:--:|:--:|:--:|:--:|:--:|:--:|:--:|:--:|:--:| | – | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | PostgreSQL 10 is not tested and not claimed (`META.json` says 11). ## Install ```sh pgxn install pg_promise_guard ``` or from source, `make install` (`PG_CONFIG=/path/to/pg_config` for another PostgreSQL). Then, in the database to watch: ```sql CREATE EXTENSION pg_promise_guard; ``` For `watch()`, install pg_living_assertions 0.5.6 or later (0.5.10 recommended): `pgxn install pg_living_assertions`, then `CREATE EXTENSION pg_living_assertions;`. Without it, everything else works and `watch()` says what it needs. ## Tests ```sh make install make installcheck # needs pg_living_assertions installed (for its watch() part) make check-suites # PostgreSQL 18+: a throwaway cluster on port 5495, stopped afterwards ``` The suite does not check that the extension reads the catalog — it **breaks each promise for real** and then asserts both that the guarantee is genuinely gone (the duplicate goes in; the audit row is missing) and that the extension says so. It also asserts that a healthy schema reports **nothing**: a checker that always finds something gets ignored, and then it is not a checker. ## 0.2.0 -- the scan gets a memory Everything above answers *what is broken right now*. Run it, get a list; run it tomorrow, get another list, with no way to tell whether anything changed, when it was last looked at, or whether anyone has **ever** looked. That last one matters most: in a scanner with no state, "this schema is clean" and "nobody has scanned this schema" produce the same empty result -- and an empty result reads as a clean bill of health. ```sql SELECT promise_guard.watch('public'); -- register the scan SELECT * FROM living_assertions.status; -- verdict AND how old it is ``` This extension still keeps **no state of its own** -- reading the catalog is its job, remembering that you read it is not. That belongs to [`pg_living_assertions`](https://pgxn.org/dist/pg_living_assertions/), which 0.2.0 to 0.2.9 required and 0.2.10 uses only in `watch()`. Only `breach` decides the verdict, for the same reason `promises_kept()` ignores gaps: a check that is red on purpose gets silenced, and takes the real ones with it. ### A latent defect fixed in 0.2.0 `check_promises()` and `promises_kept()` did not set their own `search_path`, so they resolved through the **caller's** -- meaning they only worked from a session that already had the schema in scope. A cron job, a monitoring role or a `SECURITY DEFINER` context got `function check_promises(text) does not exist`, and an unqualified name can also resolve to something a user planted earlier in their path. Every test missed it because every test called them from a session that had the schema in scope; it surfaced the first time the function ran under somebody else's `search_path`. ## License Apache License 2.0 -- see [LICENSE](LICENSE). Copyright 2026 Manuel Reyes Bravo. The name is not licensed with the code: see [TRADEMARK.md](TRADEMARK.md).