Pg Security Release Analysis Agent Skill

You help the Supabase Postgres team analyze upstream PG security releases. The goal is to convert "PG just shipped a new minor security release" into a complete, verifiable catalog of (a) every customer-affecting change (CVE and non-CVE), (b) the Supabase surface area it touches, and (c) what we need to communicate. Worked example: PSQL-1110 (May 2026 cycle).

Inputs

If not provided, ask the user for the upstream PG version ranges to analyze. Typically:

• pg15_from..pg15_to (e.g. REL_15_14..REL_15_18)
• pg17_from..pg17_to (e.g. REL_17_6..REL_17_10)

For each branch, the from version is what's currently in nix/config.nix; the to version is the target.

Critical universal gotchas (apply to every cycle)

• git log --grep does not tell you "first landed in version X." Use git tag --contains <sha> | grep -E '^REL_(15|17)_' | sort -V | head -1.
• CVSS in commit messages diverges from the canonical security page. Always cross-check against https://www.postgresql.org/support/security/. The May 2026 cycle had 5 misclassified severities on first pass.
• CVE refs in "Last-minute updates for release notes" commits aren't backports. Verify each CVE has at least one real fix commit, not just a release-notes-update reference. PG-18-only CVEs leak through this way (e.g. CVE-2026-2007 in May 2026).
• --grep=CVE alone misses everything that wasn't tagged with a CVE. The Pattern Matrix below is mandatory — walk it in full, every cycle, regardless of CVE count.
• "No CVE in this area" ≠ "no impact." Non-CVE bug fixes can have larger customer impact than CVEs (e.g. the May 2026 ltree REINDEX issue affected more projects than any single CVE).

Workflow

Step 1 — Set up the upstream postgres clone

Cache at /tmp/postgres-upstream so subsequent runs reuse it.

if [ ! -d /tmp/postgres-upstream ]; then
  git clone --filter=blob:none --no-checkout https://github.com/postgres/postgres.git /tmp/postgres-upstream
fi
git -C /tmp/postgres-upstream fetch --depth=600 origin \
  refs/tags/<pg15_from>:refs/tags/<pg15_from> \
  refs/tags/<pg15_to>:refs/tags/<pg15_to> \
  refs/tags/<pg17_from>:refs/tags/<pg17_from> \
  refs/tags/<pg17_to>:refs/tags/<pg17_to>

Verify: git -C /tmp/postgres-upstream tag --list 'REL_1[57]_*' | sort -V.

Step 2 — Enumerate CVEs and verify they're actually backported

git -C /tmp/postgres-upstream log <pg15_from>..<pg15_to> --format=%B | grep -oE 'CVE-[0-9]{4}-[0-9]+' | sort -u
git -C /tmp/postgres-upstream log <pg17_from>..<pg17_to> --format=%B | grep -oE 'CVE-[0-9]{4}-[0-9]+' | sort -u

For each CVE in either list, run git log <range> --grep='CVE-XXXX-YYYY' --format='%h %s' and confirm at least one commit is a real fix (subject line is not "Last-minute updates for release notes").

Step 3 — Cross-verify severity against the canonical security page

Fetch https://www.postgresql.org/support/security/. For every in-scope CVE, capture:

• CVSS score
• Severity label (CVSS ≥ 7.0 = High, 4.0–6.9 = Medium, < 4.0 = Low — verify label matches CVSS band)
• Affected versions
• Fixed-in versions

Step 4 — Map each CVE to its fix commits and first-landed version

For each in-scope CVE:

git -C /tmp/postgres-upstream log <range> --grep='CVE-XXXX-YYYY' --format='%h %s'
git -C /tmp/postgres-upstream tag --contains <fix-sha> | grep -E '^REL_(15|17)_' | sort -V | head -1

Step 5 — Walk the Pattern Matrix (the comprehensive part)

For every release, walk through the matrix below. For each class, run the detection command, read what comes back, and answer the Supabase-impact question. Do NOT skip classes that "feel unlikely" — the whole point is to catch the surprise.

(Matrix is its own section below — see Pattern Matrix.)

Step 6 — Cross-check against the Supabase Surface Map

For every finding from Step 5, ask: which piece of the Supabase Surface Map does it touch? If none, the risk is bounded. If one or more, document the customer-impact path explicitly.

(Surface map is its own section below — see Supabase Surface Map.)

Step 7 — Generate the catalog draft

Output a markdown catalog matching PSQL-1110's comment structure. Sections:

• Top-of-document context: how many upstream cycles bundled, total CVE count, severity rollup
• ⚠ Highest-impact item callout (most customer-affecting non-CVE or CVE)
• A. CVE table, sorted High → Medium → Low. Columns: #, CVE, Severity (CVSS), First landed in, Affected component, Upstream fix (15.x) (linked sha), Upstream fix (17.x) (linked sha), Supabase impact, Mitigation / action
• Out of scope (PG 18-only CVEs)
• PG-major-version-only ABI concerns (if any)
• C. Non-CVE behavior changes (table, customer-visible only)
• D. Fleet detection queries (SQL the support team / data-eng can run)
• E. Action items (Phase 0–5 of the breaking-change rollout)
• F. Reproducing this analysis (the commands used)

Commit-link format: <8-char-sha>.

Pattern Matrix

Walk every class for every release. Detection commands assume git -C /tmp/postgres-upstream and <range> = the version range (e.g. REL_15_14..REL_15_18).

1. Privilege / auth tightenings

What it looks like: new superuser() checks, new pg_*_aclcheck / object_aclcheck calls, default role privilege changes.

Detection:

git log <range> --oneline -- src/backend/commands/ src/backend/catalog/ | grep -iE 'privilege|aclcheck|superuser'
git log <range> -p -- src/backend/commands/ | grep -nE '^\+.*\b(superuser\(\)|pg_proc_aclcheck|object_aclcheck)' | head -40

Supabase impact questions: Customer roles (postgres, anon, authenticated, service_role, supabase_admin_lite) are non-superuser. Which of the new checks do they trip? Does the trip happen at runtime, at dump/restore time, or at pg_upgrade? Are any default-installed extensions affected?

Historical example: CVE-2026-2004 (May 2026) — superuser required for non-built-in selectivity estimators on operators. Fired at pg_dump / pg_restore / pg_upgrade time, not runtime.

2. Silent data correctness

What it looks like: multibyte / locale / collation / case-folding fixes, comparison function fixes, index correctness fixes (GiST / GIN / B-tree / BRIN). Failures are typically SILENT — wrong query results, not errors.

Detection:

git log <range> --oneline -- src/backend/utils/ src/backend/access/ contrib/ | grep -iE 'multibyte|collation|locale|case[- ]?fold|comparison|overflow|truncat'
git log <range> --oneline -- contrib/ | grep -iE 'fix|wrong|incorrect' | grep -viE 'test|comment|typo|docs?|format'

Supabase impact questions: Does this require REINDEX on existing indexes? Which encodings / collation providers (libc vs ICU) are affected? Which Supabase-shipped extensions touch this code path? Is the failure mode silent (wrong results) or noisy (error)? If silent, it warrants headline customer comms.

Historical example: ltree multibyte case-folding fix (May 2026 cycle) — required REINDEX on UTF-8 / ICU databases; ~2,245 projects affected.

3. Memory safety / buffer overrun

What it looks like: palloc / alloc overflow fixes, bounds-check additions, length validation in parsers.

Detection:

git log <range> --oneline | grep -iE 'overflow|palloc|bound|overrun|MaxAllocSize|integer overflow'
git log <range> --oneline -- src/backend/utils/mb/ src/backend/utils/adt/ | grep -iE 'overflow|length'

Supabase impact questions: Was the bug exploitable by an authenticated customer with crafted input? Does the affected code path get exercised by default-shipped extensions (pgcrypto, pg_trgm, intarray, ltree) or RLS-policy expressions?

4. SQL injection / quoting / escaping

What it looks like: fixes in pg_dump output formatting, dynamic SQL inside contrib modules, identifier quoting bugs.

Detection:

git log <range> --oneline | grep -iE 'quot|escape|inject|sql.+inject'
git log <range> --oneline -- src/bin/pg_dump/ contrib/ | grep -iE 'quot|escape'

Supabase impact questions: Does the affected tool (pg_dump, pg_restore, or a contrib module) run with elevated privileges in any Supabase flow (logical backup, project clone, wal-g)? Are customer-supplied identifiers (table names, role names) ever interpolated unsafely?

Historical example: CVE-2026-6637 (May 2026) — refint check_foreign_key() SQL injection. refint not default-enabled at Supabase but available via CREATE EXTENSION.

5. Tool-side fixes (Supabase infra binaries)

What it looks like: bug fixes in pg_basebackup, pg_rewind, pg_dump, pg_restore, pg_upgrade, pg_createsubscriber, pg_verifybackup, pg_combinebackup, libpq, psql, ecpg.

Detection:

git log <range> --oneline -- src/bin/ src/interfaces/libpq/

Supabase impact questions: Which Supabase services use this binary?

• pg_basebackup → wal-g PITR pipeline
• libpq → bundled in AMI, used by psql / pgbouncer / PostgREST / GoTrue connection paths
• pg_dump / pg_restore → dashboard "Clone Project" flow, logical backups
• pg_upgrade → dashboard version-upgrade flow
• pg_createsubscriber → grep supabase/postgres for usage; PG 17+ only

For each binary, also check: does Supabase pin the version, or does it pick up whatever ships with the new minor?

Historical example: CVE-2026-6475 (May 2026) — pg_basebackup / pg_rewind path traversal. wal-g uses pg_basebackup (verified in ansible/tasks/setup-wal-g.yml).

6. ABI / API breaks (affect compiled extensions)

What it looks like: changes to struct layouts, enum value ordering, function signatures, or header definitions in src/include/.

Detection:

git log <range> --oneline -- src/include/ | head -40
git diff <pg17_from>..<pg17_to> -- src/include/ | grep -E '^-[^-].*\b(struct|enum|typedef|extern)' | head -40
git log <range> --oneline | grep -iE 'ABI|API|signature|enum.+order'

Supabase impact questions: All extensions in nix/ext/ rebuild from source via nix, so internal ABI shifts don't affect them. But: customer-supplied / non-nix-built C extensions could break. Is any third-party C extension whitelisted today?

Historical example: ProcSignalReason enum ordering restored in PG 17.x (commit 586f4266) — would have broken any C extension that read enum values out of position.

7. Plan / selectivity / statistics changes

What it looks like: planner optimization fixes, statistics gathering changes, selectivity estimator updates, cost-estimation fixes, memoization fixes.

Detection:

git log <range> --oneline -- src/backend/optimizer/ src/backend/utils/adt/selfuncs.c src/backend/statistics/ | head -40
git log <range> --oneline -- contrib/ | grep -iE 'selectivity|estimate|stat|plan'

Supabase impact questions: Could query plans shift for existing customers? Are statistics rebuild (ANALYZE) or REINDEX required for the fix to take effect? Are there cost regressions on common Supabase query shapes (RLS-heavy, JSON path expressions, full-text search)?

Historical example: c89510431a (May 2026) — intarray selectivity estimation overflow near INT_MAX. Wrong plans for intarray-heavy workloads.

8. Default / config / GUC behavior shifts

What it looks like: changes to default GUC values, postgresql.conf.sample, parameter renames, deprecations.

Detection:

git log <range> --oneline -- src/backend/utils/misc/postgresql.conf.sample src/backend/utils/misc/guc_tables.c
git diff <range> -- src/backend/utils/misc/postgresql.conf.sample | head -100
git log <range> --oneline | grep -iE 'default.+value|GUC|setting'

Supabase impact questions: Does Supabase already override this GUC in ansible/files/postgresql_config/postgresql.conf.j2? If not, does the new default affect anything customers depend on (logging verbosity, timeouts, connection limits, replication, SSL params)?

9. Replication / WAL behavior

What it looks like: walsender / walreceiver fixes, logical replication (subscriptions, publications, slotsync), WAL format changes, recovery / checkpoint fixes, FSM/VM persistence.

Detection:

git log <range> --oneline -- src/backend/replication/ src/backend/access/transam/ | head -40
git log <range> --oneline | grep -iE 'walsender|walreceiver|slotsync|publication|subscription|recovery|checkpoint|WAL'

Supabase impact questions:

• Realtime depends on logical replication (pgoutput decoder + publications). Any change to publication catalog, REFRESH PUBLICATION, or decoding behavior?
• wal-g depends on physical replication / WAL streaming. Any change to WAL format or basebackup?
• Read replicas (where Supabase offers them) depend on streaming replication.
• Customer-managed logical subscribers (rare but exists).

Historical example: PG 17.10 fixed walsender shutdown hang, slotsync workers blocking standby promotion. Both affect production-cluster behavior.

10. Authentication / TLS / password handling

What it looks like: SCRAM / GSS / SSL handshake fixes, certificate handling, password hashing (MD5, SCRAM-SHA-256), timing-safe comparisons.

Detection:

git log <range> --oneline -- src/backend/libpq/ src/backend/utils/adt/cryptohashes.c | head -40
git log <range> --oneline | grep -iE 'SCRAM|GSS|SSL|TLS|password|hash|cert|auth'

Supabase impact questions: Default auth method on Supabase is SCRAM-SHA-256 (verify in ansible/files/postgresql_config/postgresql.conf.j2 or pg_hba.conf). Are legacy MD5 users still present in any tier? Does the change affect pgbouncer's auth pass-through?

Historical example: CVE-2026-6478 (May 2026) — timing-unsafe MD5 password comparison. Affects legacy MD5-auth users.

11. Privileged-extension changes (Supabase-shipped extensions)

What it looks like: any commit under contrib/<name>/ for extensions Supabase preloads or default-installs, OR any of the Supabase-shipped extensions in nix/ext/ getting upstream updates.

Detection:

# Look at every contrib extension we ship
for ext in pgcrypto pg_stat_statements pgaudit pg_cron pg_net pgsodium pg_graphql pg_tle plan_filter supabase_vault auto_explain plpgsql plpgsql_check timescaledb intarray ltree hstore citext refint xml2 amcheck pgvector pg_trgm postgres_fdw; do
  count=$(git log <range> --oneline -- contrib/$ext/ 2>/dev/null | wc -l)
  if [ "$count" -gt 0 ]; then echo "$ext: $count commits"; fi
done

Supabase impact questions: For each affected extension, is it (a) preloaded via shared_preload_libraries, (b) auto-created in migrations/db/init-scripts/, or (c) merely available via CREATE EXTENSION? The first two affect every project; the third only affects opt-in customers.

Supabase Surface Map

Inventory of what to cross-check Pattern Matrix findings against. Update this map when surface area changes.

Default-enabled extensions (every Supabase project gets these)

From migrations/db/init-scripts/00000000000000-initial-schema.sql: pgcrypto, uuid-ossp, pg_stat_statements, supabase_vault.

shared_preload_libraries (worker extensions preloaded into every postmaster)

From ansible/files/postgresql_config/postgresql.conf.j2:

pg_stat_statements, pgaudit, plpgsql, plpgsql_check, pg_cron, pg_net, pgsodium, timescaledb, auto_explain, pg_tle, plan_filter, supabase_vault.

Available extensions (in nix/ext/, opt-in via CREATE EXTENSION)

Run ls nix/ext/ for the current list. Includes (non-exhaustive): pgvector, pgroonga, pgrouting, postgis, pgtap, pgjwt, hypopg, index_advisor, pg_hashids, pg_jsonschema, pg_partman, pg_repack, pg_safeupdate, pg_stat_monitor, pgmq, pljava, plv8, pg_backtrace.

Customer-facing roles (non-superuser)

postgres, anon, authenticated, service_role, supabase_admin_lite. Note: in older clusters, postgres had elevated privileges; verify role definitions in migrations/db/init-scripts/.

Privileged roles

supabase_admin (superuser), supabase_storage_admin, supabase_auth_admin, supabase_replication_admin, supabase_functions_admin.

Backup / recovery tooling

• wal-g: PITR pipeline, uses pg_basebackup. Setup in ansible/tasks/setup-wal-g.yml.
• pg_dumpall: logical backups path. Used by dashboard backup flow.
• pg_upgrade: dashboard version-upgrade flow.

API / connection-layer services that link libpq

psql (bundled in AMI), pgbouncer (connection pooling), PostgREST, GoTrue, Realtime (logical replication consumer).

Other moving parts

• supautils: extension that enforces privilege restrictions (in nix/ext/); runtime config in supautils.conf, often overridden by salt.
• orioledb-17: alternative storage engine (PG 17 fork); check whether the issue applies to vanilla 17 only or orioledb too.
• Dashboard flows: project clone (uses pg_dump/pg_restore), version upgrade (uses pg_upgrade), in-dashboard "REINDEX" hints (where supported).
• Supabase CLI: bundles docker/Dockerfile-15 and Dockerfile-17 for local dev; libpq version bundled too.

Boundaries

• Always: walk the full Pattern Matrix; verify CVE-to-commit mappings with git tag --contains; cross-check severity against the security page; cross-check every finding against the Supabase Surface Map.
• Ask first: before requesting a data-eng fleet scan (the analysis itself is read-only on /tmp/postgres-upstream).
• Never: trust commit-message CVSS over the security page; declare "no impact" without explicitly checking each surface area; rely on --grep=CVE alone.

Reference

• Upstream security listing: https://www.postgresql.org/support/security/
• Per-release notes: https://www.postgresql.org/docs/release/<version>/
• FirstGenbkiObjectId threshold: src/include/access/transam.h (= 10000, used by upstream privilege gates)
• Breaking-changes process playbook: playbooks/product-ops/how-to-do-breaking-changes.md
• PG-version-update infra playbook: playbooks/infra/running-minor-major-postgres-version-updates-for-platform-images.md
• Worked example: PSQL-1110 (May 2026 cycle)