Extensions
- pg_promise_guard 0.2.10
- Report constraints, indexes, triggers and RLS that stopped being enforced
Documentation
- SECURITY
- Security
- TRADEMARK
- Trademark
- CONTRIBUTING
- Contributing
- results
- bench/scan_cost.sh results
- CHANGELOG
- Changelog
README
Contents
pg_promise_guard
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 VALIDconstraint 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_pathshadowing, 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.