Harden the PGXN distribution

[pinodeca]

Harden the PGXN distribution

Tracking issue for everything needed to make pg_durable on
PGXN actually work for the people who find
it there. The first release (0.2.6, 2026-08-24) is live and correct as a
package, but an end-to-end validation on a clean machine found the advertised
install command does not work, the documentation does not cover the PGXN entry
point, and the search index carries our internal development notes.

Three work items, in priority order. Item 1 is a functional blocker; items 2 and
3 are quality.

#	Item	Severity
1	pgxn install pg_durable fails on a clean machine	Blocker
2	No documentation for the PGXN install path	Should fix
3	69 documents indexed, including internal prompts and open-problem notes	Low

What is already verified working

So this issue is not read as "PGXN is broken". Full end-to-end run on Debian
bookworm, PostgreSQL 17.11 from PGDG, Rust 1.98.0, cargo-pgrx 0.16.1,
pgxnclient 1.3.2, after cargo pgrx init:

Step	Result
pgxn install pg_durable	success, 4m 32s wall on 6 vCPU including download
Installed library	pg_durable.so, 11,595,832 bytes, release build, in /usr/lib/postgresql/17/lib/
Control file	default_version = '0.2.6' correctly substituted
SQL files	8 installed — pg_durable--0.2.6.sql plus 7 upgrade paths
CREATE EXTENSION pg_durable	success, extversion 0.2.6, 44 df.* functions
Background worker	started, connected, epoch sentinel written, processing loop running
Durable function	df.start('SELECT ''Hello, durable world!''') → instance e2a73657 → completed → {"rows": [{"?column?": "Hello, durable world!"}], "row_count": 1}
pgxn uninstall pg_durable	success, all 10 files removed, no build required
pgxn uninstall twice	idempotent — pg_durable is not installed for /usr/bin/pg_config; nothing to remove

The published archive is also byte-identical in Makefile, META.json.in,
scripts/test-make-install.sh, pg_durable.control and Cargo.toml to git tag
v0.2.6 (6799781), and the macOS .dylib fix from #348 shipped in it.

---

1. Blocker: pgxn install pg_durable fails with $PGRX_HOME does not exist

On a clean machine with PostgreSQL 17, Rust and cargo-pgrx all installed, the
advertised command fails after 16 seconds:

INFO: building extension
DEBUG: running: ['gmake', 'PG_CONFIG=/usr/bin/pg_config', 'all']
cargo pgrx package --pg-config "/usr/bin/pg_config" ...
Error:
   0: $PGRX_HOME does not exist

Location:
   pgrx-pg-config-0.16.1/src/lib.rs:582

gmake: *** [Makefile:27: package] Error 1
ERROR: command returned 2: gmake PG_CONFIG=/usr/bin/pg_config all

make all → package → cargo pgrx package, which requires $PGRX_HOME
(~/.pgrx). That directory is created by cargo pgrx init, and pgxn install
never runs it. Everything downstream is fine; this is purely a first-run gate.

It matters because PGXN is now an advertised install channel and is the only
source path for anyone not on Debian or Docker. It is the first thing a PGXN
user hits, and the error names an environment variable that appears nowhere in
our documentation.

The requirement is narrower than it looks

Three probes against the published 0.2.6 archive with cargo-pgrx 0.16.1:

$PGRX_HOME state	gmake PG_CONFIG=... all
absent	fails — $PGRX_HOME does not exist
exists, empty (no config.toml)	fails — /root/.pgrx/config.toml not found. Have you run `cargo pgrx init` yet?
config.toml present listing only pg18 = "/nonexistent/pg_config"	compiles successfully

So the requirement is precisely that $PGRX_HOME/config.toml exists. Its
contents are irrelevant when --pg-config is passed explicitly, which our
package target always does. Two consequences:

• An existing pgrx configuration pointing at a different PostgreSQL cannot
  hijack the build. That was a plausible failure mode; it does not occur.

• cargo pgrx init --pg<major> <pg_config> is instant when handed an
  existing pg_config — it validates and writes config.toml, and does not
  download or build PostgreSQL:

  Creating PGRX_HOME at `/root/.pgrx`
  Validating /usr/bin/pg_config
  Skipping initdb as current user is root user
  

Proposed fix

Follow the ecosystem precedent. theory/pg-jsonschema-boon
— a pgrx extension published on PGXN by PGXN's own author, and the distribution
whose metadata shape we already copied — ships explicit make targets and
documents them, rather than auto-initializing:

.PHONY: install-pgrx # Install the version of PGRX specified in Cargo.toml.
install-pgrx: Cargo.toml
	@cargo install --locked cargo-pgrx --version "$(PGRXV)"

.PHONY: pgrx-init # Initialize pgrx for the PostgreSQL version identified by pg_config.
pgrx-init: Cargo.toml
	@cargo pgrx init "--pg$(PGV)"="$(PG_CONFIG)"

Its package target is a bare cargo pgrx package --pg-config with no init
guard, so by source reading it has the same $PGRX_HOME dependency we do.

Recommended, in order:

1. Add pgrx-init and install-pgrx targets to our Makefile. We already
   compute PG_MAJOR and PG_CONFIG, and Cargo.toml pins pgrx = "=0.16.1",
   so both targets are a few lines. This gives the documentation something
   concrete to name instead of a raw cargo pgrx init incantation.

2. Make the failure actionable. When $PGRX_HOME/config.toml is absent,
   fail before invoking cargo with the exact command to run:

   pg_durable: cargo-pgrx is not initialized for this PostgreSQL.
   Run:  make pgrx-init PG_CONFIG=/usr/bin/pg_config
   

3. Optionally, guarded auto-init in package. Running
   cargo pgrx init --pg$(PG_MAJOR) "$(PG_CONFIG)" when config.toml is absent
   would make pgxn install pg_durable work with no user action at all. The
   usual objection — that cargo pgrx init can download and compile its own
   PostgreSQL — does not apply, because we always pass an explicit pg_config.
   It only fires when config.toml is missing, so it cannot clobber an existing
   setup. This diverges from the reference distribution, so it is a judgement
   call: strictly friendlier, slightly more magic.

Note that pgxnclient passes PG_CONFIG explicitly on both make all and
make install (['gmake', 'PG_CONFIG=/usr/bin/pg_config', 'all']), so there is
no risk of the unprivileged build and the sudo install resolving different
PostgreSQL installations.

---

2. Document the PGXN install path

PGXN carries the source distribution: the published archive is 970 KB, 337
files, and contains zero .so/.dylib/.dll/.a/.o. Installing means
compiling a Rust extension on the user's machine, which needs the Rust
toolchain, cargo-pgrx 0.16.1, PostgreSQL development headers, and several
minutes. None of that is currently spelled out for someone arriving via
pgxn install.

Today the entire PGXN treatment is one sentence at README.md:162, at the end
of the Packages section, saying PGXN carries the source distribution "built and
installed exactly as described above". It never shows the pgxn command, and
"above" is the tarball recipe, which is a different entry point.

Where it goes

README.md, Packages section — primary. PGXN renders README.md as the
distribution landing page: pgxn.org/dist/pg_durable/ contains an h3: README
followed by the entire README. A PGXN visitor is already reading it.

Replace line 162 with a short "Installing from PGXN" block covering
prerequisites, make pgrx-init (per item 1), pgxn install pg_durable,
pgxn uninstall pg_durable, and a pointer to the Docker image or Debian
packages for anyone who wants a prebuilt binary instead.

USER_GUIDE.md, Getting Started → Prerequisites — cross-reference.
USER_GUIDE.md is the provides.docfile and PGXN links it as "Documentation".
Its Prerequisites currently begin after installation —
shared_preload_libraries, restart, CREATE EXTENSION — so there is a gap
where "how the files got there" belongs. One line pointing at the README is
enough; the install content should not be duplicated.

A separate INSTALL.md is not recommended: it splits install guidance across
three files and is not a PGXN convention.

Relative links in the README break on PGXN

Whatever we write has to survive being rendered on pgxn.org, and today's README
links do not. Verified 404:

• https://pgxn.org/dist/pg_durable/USER_GUIDE.md
• https://pgxn.org/dist/pg_durable/docs/pg_durable_mvp.md

The PGXN-rendered copy lives at /dist/pg_durable/0.2.6/USER_GUIDE.html (HTTP
200), so the content is reachable — the link is what is broken. Affected
relative links in README.md: USER_GUIDE.md,
USER_GUIDE.md#privilege-grants, docs/pg_durable_mvp.md, SECURITY.md,
docs/TESTING.md#2-pg_regress-tests, examples/README.md, tests/e2e/,
.github/workflows/ci.yml.

Any link intended to work for a PGXN reader needs an absolute URL. Whether to
convert them all, or only the ones in the install path, is a judgement call —
relative links are correct on GitHub, which is the larger audience.

---

3. Narrow the indexed document set with no_index

The 0.2.6 release indexed 69 documents. PGXN indexes documentation for
full-text search, so the distribution page now advertises, alongside the User
Guide:

Indexed path	Title
prompts/pg_durable-release	Release Workflow
prompts/pg_durable-merge-main	Merge Branch to Main
.github/copilot-instructions	pg_durable AI Coding Instructions
.agents/skills/pg-durable-sql/SKILL	pg_durable SQL Generation
docs/dep_issues	Dependency Issues & Blockers
docs/http_problems	Open Problems in the HTTP Activities
docs/loop_rework_problems	Open Problems in the Loop Rework
docs/DUROXIDE_PG_DEADLOCK_ISSUE	Duroxide-PG Deadlock Issue in Parallel Orchestrations
docs/CODESPACES_PREBUILDS	GitHub Codespaces Pre-builds Configuration
TODO	TODO
examples/*/function-app/requirements	requirements

Nothing here is secret — the repository is public. The problem is signal: a
reader searching PGXN gets our internal AI development prompts and a list of our
open bugs presented as product documentation, and two requirements.txt pip
manifests are indexed as "documents" because Text::Markup treats .txt as
markup. All seven files under prompts/ are indexed, including the release
runbook describing our own PGXN publishing procedure.

The fix: no_index in META.json.in

The PGXN Meta Spec has a field for exactly this. From
the spec, section no_index:

This Map describes any files or directories that are private to the packaging
or implementation of the distribution and should be ignored by indexing or
search tools.

It is not merely aspirational. Implementation, pgxn/pgxn-api
lib/PGXN/API/Indexer.pm, find_docs() (lines 473-501):

my $skip = { directory => [], file => [], %{ $meta->{no_index} || {} } };
...
for my $member ($p->{zip}->members) {
    next if $member->isDirectory;
    (my $fn = $member->fileName) =~ s{^$prefix/}{};
    next if $seen{$fn}++;
    next if first { $fn eq $_ }      @{ $skip->{file} };
    next if first { $fn =~ /^\Q$_/ } @{ $skip->{directory} };
    next unless $markup->guess_format($fn) || $fn =~ /^README(?:[.][^.]+)?$/i;
    push @docs => { filename => $fn };
}

Confirmed working in production. pgcollection publishes
no_index: {"directory": [".github"], "file": ["AGENTS.md", "CODE_OF_CONDUCT.md", "CONTRIBUTING.md", "GOVERNANCE.md", "SECURITY.md"]}, and all five files are
absent from its indexed docs while README and doc/* remain. Roughly a
dozen other current distributions use it, including pg_trickle, provsql,
pg_local_cache and pg_clickhouse.

Why not .gitattributes export-ignore

It would work — make pgxn-zip uses git archive, which honours
export-ignore — but it is the wrong instrument:

1. It removes the files from the tarball entirely. Someone who downloads the
   source distribution to read or build gets a tree that silently differs from
   the repository.
2. It would also strip the GitHub release assets. package-release.yml:269
   (build-source) builds pg_durable-X.Y.Z.tar.gz and .tar.bz2 with
   git archive from the same tree, so one .gitattributes line silently
   changes three published artifacts at once. GitHub's auto-generated "Source
   code" archives honour it too.
3. It solves the wrong problem. We do not want the files gone; we want
   them not indexed as product documentation. That is precisely the
   distinction no_index draws.

no_index is one field in a file we already generate, affects only indexing and
search, and leaves every artifact byte-identical.

Two implementation traps

1. file entries are matched by exact string equality, with the extension.
The API's docs keys are extension-stripped, so copying them straight out of
https://api.pgxn.org/dist/pg_durable.json does not work:

API docs key	no_index.file entry required
docs/dep_issues	docs/dep_issues.md
TODO	TODO.md

Paths are relative to the archive root — the pg_durable-0.2.6/ prefix is
stripped before matching, so write prompts/README.md, not
pg_durable-0.2.6/prompts/README.md.

2. directory entries are an anchored literal prefix match (/^\Q$_/), not
a path-component match. "docs" would also match a hypothetical docs2/.
Include the trailing slash: "docs/website/".

One useful safety property: provides.<ext>.docfile is collected before the
skip checks run, so USER_GUIDE.md can never be excluded by accident.

Proposed change

Add to META.json.in:

"no_index": {
   "directory": [
      ".agents/",
      ".github/",
      "prompts/",
      "docs/website/"
   ],
   "file": [
      "TODO.md",
      "docs/CODESPACES_PREBUILDS.md",
      "docs/DUROXIDE_PG_DEADLOCK_ISSUE.md",
      "docs/dep_issues.md",
      "docs/http_problems.md",
      "docs/loop_rework_problems.md",
      "examples/azure-functions/function-app/requirements.txt",
      "examples/invoice-approval/function-app/requirements.txt"
   ]
}

That takes the indexed set from 69 to about 50, removing every entry that is
unambiguously internal: AI prompts and skills, CI instructions, the website
source, the repo TODO, three open-problem notes, the Codespaces config, and two
pip manifests.

Deliberately left indexed: README, USER_GUIDE, CHANGELOG, LICENSE,
SECURITY, CONTRIBUTING, CODE_OF_CONDUCT, the docs/spec-* and
docs/ARCHITECTURE / docs/api-reference / docs/grammar set, and all 13
examples/ documents.

Worth a decision, not obviously either way:

• docs/security-review/ (3 documents). Publishing a Microsoft OSS security
  review is arguably a credibility asset; workbook-data is raw table data
  rather than prose and probably should not be indexed regardless.
• Design and proposal documents — parameterized_queries_proposal (explicitly
  titled "Status: Not Implemented"), proposal-management-api,
  nested-graph-design, named-results-v2, move-duroxide-schema,
  design-azure-functions. Useful to a contributor, potentially misleading to a
  user searching for features that do not exist.
• Test plans — TESTING, E2E_TESTING, spec-pg-regress, upgrade-testing.
• docs/pg_durable_mvp ("pg_durable MVP User Guide") appears superseded by
  USER_GUIDE. Two user guides in one search index is worse than one.

Whatever item 2 adds to the README must not be excluded here.

---

Verification

Items 1 and 2 are testable before release, on a machine with no ~/.pgrx:

pip install pgxnclient
cargo install cargo-pgrx --version 0.16.1 --locked
pgxn install pg_durable
pgxn uninstall pg_durable

Item 3 applies at index time, not bundle time, so make pgxn-zip output is
unchanged and there is nothing to check locally beyond metadata validity:

docker run --rm -v "$PWD:/repo" -w /repo pgxn/pgxn-tools sh -c '
  make META.json && pgxn validate-meta META.json'

The real check for item 3 happens after the next release lands:

curl -s https://api.pgxn.org/dist/pg_durable.json \
  | python3 -c 'import json,sys; d=json.load(sys.stdin); print(len(d["docs"])); print(*sorted(d["docs"]), sep="\n")'

Suggested CI coverage

No workflow currently invokes pgxn at all. Source Install Checks runs the
offline scripts/test-make-install.sh contract tests, and
macOS Source Install (PG17) calls make package / make install directly
with an explicit PG_CONFIG=. Neither would have caught item 1, because both
runners already have ~/.pgrx.

A job running the four commands above on a clean runner would. Because it
exercises the published distribution it can only run after a release, so a
scheduled or manually dispatched job is more appropriate than a per-PR gate.

Acceptance criteria

• On a clean machine with PostgreSQL 17 or 18, Rust and cargo-pgrx but no
  ~/.pgrx, either pgxn install pg_durable succeeds outright, or it fails
  with an error naming the exact command to run.
• make pgrx-init and make install-pgrx exist and are listed in make help.
• README.md documents the PGXN install path including prerequisites and the
  expected build time, and any link in that section works when rendered on
  pgxn.org.
• USER_GUIDE.md Prerequisites cross-references it.
• META.json.in carries a no_index map and pgxn validate-meta passes.
• After the next release the indexed set contains no prompts/, .github/,
  .agents/ or docs/website/ entry, no requirements entry, and README and
  USER_GUIDE remain indexed and render.
• The release archive and the GitHub source assets are unchanged in content —
  item 3 must not become an export-ignore change.

Notes

None of this can be retrofitted to 0.2.6; a PGXN release is immutable. It all
applies from the next release onward, and 0.2.6 keeps its 69-document index
and its install gate.

Two unrelated observations found during validation

Recorded here because that is where they surfaced. Neither belongs to this
issue; splitting either out is reasonable.

1. pg_durable.enable_superuser_instances is PGC_POSTMASTER but its error
   text does not say so:

   ERROR: pg_durable: superuser instances are disabled. current_user "postgres" is a
   superuser, but pg_durable.enable_superuser_instances is off.
   Set pg_durable.enable_superuser_instances = on to allow this.
   

   Following that instruction with ALTER SYSTEM SET + pg_reload_conf() does
   nothing; the server log says cannot be changed without restarting the server. Adding "and restart PostgreSQL" to the hint would close the loop.

2. The background worker resolves its connection through libpq environment
   defaults. In a container with PGHOST set, the worker silently connected
   to that host instead of the local server:

   LOG: pg_durable: background worker connected to PostgreSQL at
        postgres://postgres@<PGHOST-from-environment>:5433/postgres
   LOG: pg_durable: failed to create management pool (will retry in 5s):
        pool timed out while waiting for an open connection
   

   PGHOST is routinely set in Docker Compose and Kubernetes environments, so a
   worker that follows it can end up pointed at an entirely different database
   with only a log line to say so. Whether that is intended is a design question
   I have not investigated.

---

Evidence gathered 2026-08-24 and 2026-08-25 against the published pg_durable
0.2.6 distribution (sha1 c48e369b0d9ac10a177c41c482afa9854b96a47d), a clean
Debian bookworm install with PostgreSQL 17.11 from PGDG, pgxn/pgxn-api
lib/PGXN/API/Indexer.pm at main, pgxn/pgxn-manager
sql/11-dist_management.sql at main, pgxn/pgxnclient
pgxnclient/commands/install.py at master, the PGXN Meta Spec 1.0.0, and the
live pgcollection, pg_local_cache and jsonschema distributions.

[waldemort-auto]

Status: all three items implemented; staying open for post-release verification

Item	State	Landed in
1 — pgxn install fails on a clean machine	Implemented	#370 → a03dc87
2 — No documentation for the PGXN install path	Implemented	#373 → 69eefdc
3 — 69 documents indexed, 19 internal	Implemented	#373 → 69eefdc

Why this is not closed yet

The acceptance criteria in this issue require checking the published index:

After the next release the indexed set contains no prompts/, .github/, .agents/ or docs/website/ entry, no requirements entry, and README and USER_GUIDE remain indexed and render.

no_index is applied by PGXN at index time, so nothing on main proves it until a release carrying it is actually published. The next release is the first one that can.

0.2.7 cannot be re-published to clean up its index

Worth recording, since it is the obvious idea. PGXN rejects a duplicate version in the database — pgxn-manager sql/15-dist_processing.sql, add_distribution:

INSERT INTO distributions (name, version, relstatus, abstract, description, sha1, creator, meta)
VALUES (...);
EXCEPTION WHEN unique_violation THEN
   RAISE EXCEPTION 'Distribution “% %” already exists', distmeta.name, distmeta.version;

Releases are immutable by construction rather than by policy, so credentials do not help. check_dist_version additionally requires a new version to be greater than or equal to existing ones. 0.2.6 and 0.2.7 keep their 69-document indexes permanently; the fix applies from the next release onward.

Item 3 has been validated locally, without publishing

Rather than wait blind, the upstream indexing algorithm was run directly against the real artifact: find_docs() from pgxn-api lib/PGXN/API/Indexer.pm, with the real Text::Markup and Archive::Zip, against the zip built by make pgxn-zip from merged main (69eefdc).

INDEXED DOCUMENTS: 50        (was 69)

LICENSE present:                                              yes
README / USER_GUIDE present:                                  yes
leftovers under prompts/ .github/ .agents/ docs/website/:     none
requirements / TODO / dep_issues / http_problems /
loop_rework / DEADLOCK / CODESPACES:                          none

This is the expected outcome, not proof of the published result — PGXN's indexer runs on their infrastructure with their module versions. It does mean a surprise at release time is unlikely.

One finding from that exercise is worth keeping, because it explains something in the current index. The first run produced 49 documents and dropped LICENSE. The cause is Indexer.pm line 12:

use Text::Markup::None qr/te?xt/;

Since Text::Markup 0.33 the None parser is fallback-only unless registered with a regex in the use statement, so without that line .txt is not markup at all. That single line is why LICENSE, examples/azure-functions/function-app/requirements and examples/invoice-approval/function-app/requirements appear in the published 0.2.6 and 0.2.7 indexes — and it confirms the two requirements.txt entries in no_index are load-bearing rather than decorative.

Item 1 and item 2 are verified as far as they can be

Both were checked end to end on a clean Debian bookworm box with PostgreSQL 17 from PGDG and no ~/.pgrx:

• Item 1: make all followed by make install — the exact sequence pgxnclient runs — both exit 0, where previously make all failed with $PGRX_HOME does not exist.
• Item 2: review of Keep internal documents out of the PGXN search index and document the PGXN install #373 caught that the documented pgxn install pg_durable fails on the very platform the section names, because pgxnclient elevates only when told to. Fixed in 122fd36 before merge. Note the obvious correction is also wrong: --sudo takes an optional PROG, so pgxn install --sudo pg_durable exits 2 with an argparse usage error, the distribution name having been consumed as the program name. The working form is pgxn install --sudo -- pg_durable, verified as a non-root user: exit 0, pg_durable.so installed root-owned into pg_config --pkglibdir while ~/.pgrx stays owned by the building user — confirming the build runs unprivileged and only the install step is elevated. pgxn uninstall --sudo -- pg_durable likewise.

To close this issue

After the next release is published to PGXN:

curl -s https://api.pgxn.org/dist/pg_durable.json \
  | python3 -c 'import json,sys; d=json.load(sys.stdin); print(d["version"], len(d["docs"])); print(*sorted(d["docs"]), sep="\n")'

Expect the new version and 50 documents, with no prompts/, .github/, .agents/, docs/website/ or requirements entries, and README and USER_GUIDE still present and rendering.

Publishing a testing release purely to verify sooner is not recommended: it consumes a version number and would exercise the same algorithm, classifier and archive already run locally.

Optional follow-up, not required to close

The review of #373 noted that no_index is a silent-failure contract — a file entry missing its extension, or a directory entry missing its trailing slash, simply stops matching, and nothing catches it until a release is already immutable. A cheap offline check would assert that every file entry exists in git ls-files and carries an extension, every directory entry ends with /, README is not listed, and the surviving set still contains the user-facing documents.

That fits naturally in scripts/test-make-install.sh, which the existing Source Install Checks job already runs, so it needs no workflow change.

Deliberately left indexed

For the record, four groups were left in rather than decided unilaterally: docs/security-review/ (3 documents), the design and proposal documents, the test plans, and docs/pg_durable_mvp — which appears superseded by USER_GUIDE, and two user guides in one search index is worse than one. Unlike a release, this list is not immutable and can be tightened at any time.