pg_promise_guard 0.2.10

This Release
pg_promise_guard 0.2.10
Date
Status
Stable
Other Releases
Abstract
Find the guarantees your schema claims and silently stopped enforcing
Description
pg_promise_guard reports every guarantee your catalog advertises but no longer enforces — invalid unique indexes, NOT VALID constraints, triggers left disabled after a bulk load, RLS enabled but not forced — telling a live breach apart from a deliberate mid-migration gap. Reads only the system catalogs: no user data, no locks, no shared library.
Released By
manu15
License
Apache 2.0
Resources
Special Files
Tags

Extensions

pg_promise_guard 0.2.10
Report constraints, indexes, triggers and RLS that stopped being enforced

Documentation

SECURITY
Security
TRADEMARK
Trademark
results
bench/scan_cost.sh results
CHANGELOG
Changelog
CONTRIBUTING
Contributing

README

pg_promise_guard

CI

Finds the guarantees your schema claims to give and silently stopped giving.

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.

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

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:

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

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.

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, 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. Copyright 2026 Manuel Reyes Bravo.

The name is not licensed with the code: see TRADEMARK.md.