supabase-postgres.git - commit b24a87313540

supabase-postgres.git / summary / log / commit / refs

commit    b24a87313540f08538c25cd3f93a3d07419476db
Author:   Artyom Borissov <33684505+srcCraftsman@users.noreply.github.com>
Commit:   GitHub <noreply@github.com>
Date:     Tue Aug 11 15:57:10 2026 +0000

    feat: [ansible/tasks] adding reindex script for collation version change (#2343)
    
    * feat: [ansible/tasks] adding on-boot reindex in an even of collation version change
    
    * feat: [ansible/files] moved refresh collation into adminapi static scripts
    
    * fix: [ansible/files] addressing comments
    
    * fix: [ansible/files] shfmt formatting
    
    * fix: [ansible/files] addressing comments
    
    * fix: [ansible/files] adding status file for exclusion indexes to be raised to user
    
    * fix: [ansible/files] shfmt fix


 .../workflows/publish-nix-pgupgrade-scripts.yml    |   1 +
 .../pg_upgrade_scripts/refresh_collation.sh        | 416 +++++++++++++++++++++
 ansible/files/adminapi.sudoers.conf                |   1 +
 ansible/tasks/internal/admin-api.yml               |   1 +
 audit-specs/baselines/baseline.yml                 |   6 +
 5 files changed, 425 insertions(+)

diff --git a/.github/workflows/publish-nix-pgupgrade-scripts.yml b/.github/workflows/publish-nix-pgupgrade-scripts.yml
index 810bfb8b..6202a1ef 100644
--- a/.github/workflows/publish-nix-pgupgrade-scripts.yml
+++ b/.github/workflows/publish-nix-pgupgrade-scripts.yml
@@ -8,6 +8,7 @@ on:
     paths:
       - '.github/workflows/publish-nix-pgupgrade-scripts.yml'
       - 'ansible/vars.yml'
+      - 'ansible/files/admin_api_scripts/pg_upgrade_scripts/**'
   workflow_dispatch:
     inputs:
       postgresVersion:
diff --git a/ansible/files/admin_api_scripts/pg_upgrade_scripts/refresh_collation.sh b/ansible/files/admin_api_scripts/pg_upgrade_scripts/refresh_collation.sh
new file mode 100755
index 00000000..1b464436
--- /dev/null
+++ b/ansible/files/admin_api_scripts/pg_upgrade_scripts/refresh_collation.sh
@@ -0,0 +1,416 @@
+#! /usr/bin/env bash
+
+## Rebuilds affected indexes and refreshes recorded collation versions after an
+## AMI ships new glibc/ICU over existing data. Invoked on demand by adminapi (not
+## a boot service); the exit code is the signal: 0 = work done, or a legitimate
+## no-op (replica, pre-PG15); 1 = something failed, OR preconditions could not be
+## established (Postgres unreachable) so we don't actually know the collation
+## state. Per-statement errors don't abort — we fix what we can,
+## then exit non-zero (like the sibling pg_upgrade scripts). Reindex runs BEFORE
+## refresh so a stale index is never masked by an updated catalog; a failed reindex
+## skips that database's refresh to preserve the signal.
+##
+## SECURITY: object names never reach the shell. Enumeration returns integer OIDs
+## only; DDL is built and run server-side via format('%I',...) + \gexec, so a
+## hostile collation/index name (any user with CREATE on a schema) can't inject a
+## psql meta-command (\!) to get a root shell via this script's sudoers grant.
+
+set -uo pipefail # deliberately NOT -e: per-statement errors are handled inline
+
+# pg_database_collation_actual_version() + datcollversion are PG15+; nothing to
+# compare against below that. (The collation-level function is older, but this
+# database-level gate sets the floor.)
+MIN_SERVER_VERSION_NUM=150000
+
+# libc always; ICU always. Reindex-before-refresh makes ICU safe.
+PROVIDERS="'c', 'i'"
+
+# Caps REINDEX CONCURRENTLY's brief locks so one idle-in-transaction client can't
+# block it forever. Passed via PGOPTIONS, not a ;-joined SET — that would open a
+# transaction block, which REINDEX CONCURRENTLY refuses to run inside.
+REINDEX_LOCK_TIMEOUT_MS=2000
+
+# Set by any enumeration/reindex/refresh failure; becomes the exit code.
+SCRIPT_FAILED=0
+
+# Advisory snapshot consumed by adminapi (api/refresh_collation.go). Rewritten
+# fresh every run; readable by the adminapi user (0644 — index names, not secret).
+ADVISORY_FILE="/tmp/collation-refresh-status.json"
+ADVISORY_NDJSON="" # per-run temp NDJSON accumulator; set in main()
+ADVISORY_DBS=""    # per-run temp file of databases that produced advisories
+
+log() {
+	echo "[$(date '+%Y-%m-%d %H:%M:%S')] refresh_collation: $1"
+}
+
+# ON_ERROR_STOP makes psql exit non-zero on any SQL error (incl. inside \gexec) —
+# that is how per-statement failure is detected below.
+run_sql() {
+	psql -h localhost -U supabase_admin -v ON_ERROR_STOP=1 --no-psqlrc "$@"
+}
+
+# Retry to guard against a not-yet-ready Postgres socket.
+retry() {
+	local attempts=$1
+	shift
+	local i=0
+	until "$@"; do
+		i=$((i + 1))
+		if [ "$i" -ge "$attempts" ]; then
+			return 1
+		fi
+		sleep 1
+	done
+	return 0
+}
+
+# Wrap a db name in dbname='...' (escaping \ and ') so characters special to -d
+# parsing (=, spaces, quotes) stay part of a literal name, not a conninfo fragment.
+conninfo_for_db() {
+	local d="$1"
+	d="${d//\\/\\\\}"
+	d="${d//\'/\\\'}"
+	printf "dbname='%s'" "$d"
+}
+
+# --- Enumeration queries: emit integer OIDs only (never object names) ----------
+
+# Leaf indexes ('i') in user schemas depending on a stale collation (explicit or
+# the db default). Partitioned parents ('I') have no storage; catalogs use
+# version-less collations; temp schemas are skipped (reindexing another session's
+# temp index fails).
+#
+# SCOPE / KNOWN LIMITATION (follow-up): detection keys off pg_index.indcollation —
+# the collations of the index's KEY COLUMNS only. Collation dependencies that live
+# elsewhere are NOT detected and NOT rebuilt: partial-index predicates
+# (pg_index.indpred), CHECK constraints (pg_constraint), and partition bound
+# expressions (pg_class.relpartbound). The refresh step below bumps the recorded
+# version for EVERY stale collation regardless of where it is used, so those
+# dependencies get stamped "refreshed" without any revalidation. The intended
+# follow-up surfaces them via the adminapi advisory channel rather than silently
+# refreshing. See:
+# https://github.com/supabase/postgres/pull/2343#discussion_r3756738261
+# Shared WITH clause: collations (affected_coll) and the current db's default
+# (affected_default) whose recorded version is stale. Used by both the reindex
+# enumeration and the advisory query so their notion of "affected" cannot drift.
+_affected_ctes() {
+	cat <<SQL
+with affected_coll as (
+  select c.oid
+  from pg_collation c
+  where c.collprovider in ($PROVIDERS)
+    and c.collversion is not null
+    and c.collversion is distinct from pg_collation_actual_version(c.oid)
+),
+affected_default as (
+  select 1
+  from pg_database d
+  where d.datname = current_database()
+    and d.datlocprovider in ($PROVIDERS)
+    and d.datcollversion is not null
+    and d.datcollversion is distinct from pg_database_collation_actual_version(d.oid)
+)
+SQL
+}
+
+# Shared FROM/WHERE: leaf indexes ('i') in user schemas that depend on a stale
+# collation (explicit or the db default). Callers prepend their own SELECT and
+# append an index-class filter + ORDER BY. Aliases: i=pg_index, ci=pg_class,
+# n=pg_namespace.
+_affected_index_from_where() {
+	cat <<SQL
+from pg_index i
+join pg_class ci on ci.oid = i.indexrelid
+join pg_namespace n on n.oid = ci.relnamespace
+where ci.relkind = 'i'
+  and n.nspname not in ('pg_catalog', 'pg_toast', 'information_schema')
+  and n.nspname not like 'pg\_temp\_%'
+  and n.nspname not like 'pg\_toast\_temp\_%'
+  and (
+    exists (
+      select 1 from unnest(i.indcollation) as ic(oid)
+      where ic.oid in (select oid from affected_coll)
+    )
+    or (
+      exists (select 1 from affected_default)
+      and exists (
+        select 1 from unnest(i.indcollation) as ic(oid)
+        where ic.oid = 'pg_catalog."default"'::regcollation
+      )
+    )
+  )
+SQL
+}
+
+affected_index_oids_sql() {
+	cat <<SQL
+$(_affected_ctes)
+select i.indexrelid
+$(_affected_index_from_where)
+  and not i.indisexclusion
+  and i.indisvalid
+order by i.indexrelid;
+SQL
+}
+
+# Exclusion-constraint ('i' with indisexclusion) and invalid (not indisvalid)
+# indexes depending on a stale collation. These cannot be REINDEX-ed CONCURRENTLY,
+# so we never touch them here — we emit an advisory for the customer. Output is one
+# JSON object per index (NDJSON under -Atq); everything is built server-side so a
+# hostile index name stays data, never shell/psql code.
+affected_advisory_sql() {
+	cat <<SQL
+$(_affected_ctes)
+select json_build_object(
+  'database',    current_database(),
+  'index',       format('%I.%I', n.nspname, ci.relname),
+  'reason',      case when i.indisexclusion then 'exclusion_constraint'
+                      else 'invalid_index' end,
+  'remediation', format('REINDEX INDEX %I.%I;', n.nspname, ci.relname)
+)
+$(_affected_index_from_where)
+  and (i.indisexclusion or not i.indisvalid)
+order by n.nspname, ci.relname;
+SQL
+}
+
+# Named collations in the CURRENT database whose recorded version is stale.
+stale_collation_oids_sql() {
+	cat <<SQL
+select c.oid
+from pg_collation c
+where c.collprovider in ($PROVIDERS)
+  and c.collversion is not null
+  and c.collversion is distinct from pg_collation_actual_version(c.oid)
+order by c.oid;
+SQL
+}
+
+# Databases with a stale default version. datallowconn is NOT filtered: template0
+# rejects connections but ALTER DATABASE ... REFRESH runs from the shared catalog,
+# so it must be refreshed too.
+stale_db_default_oids_sql() {
+	cat <<SQL
+select oid
+from pg_database
+where datlocprovider in ($PROVIDERS)
+  and datcollversion is not null
+  and datcollversion is distinct from pg_database_collation_actual_version(oid)
+order by oid;
+SQL
+}
+
+# --- Server-side executors: DDL generated from an OID, run via \gexec ----------
+
+reindex_index_by_oid() {
+	local conn="$1" oid="$2"
+	PGOPTIONS="-c lock_timeout=$REINDEX_LOCK_TIMEOUT_MS" run_sql -d "$conn" <<SQL
+select format('reindex index concurrently %I.%I;', n.nspname, c.relname)
+from pg_class c
+join pg_namespace n on n.oid = c.relnamespace
+where c.oid = $oid and c.relkind = 'i'
+\gexec
+SQL
+}
+
+refresh_collation_by_oid() {
+	local conn="$1" oid="$2"
+	run_sql -d "$conn" <<SQL
+select format('alter collation %I.%I refresh version;', n.nspname, c.collname)
+from pg_collation c
+join pg_namespace n on n.oid = c.collnamespace
+where c.oid = $oid
+\gexec
+SQL
+}
+
+refresh_db_default_by_oid() {
+	local oid="$1"
+	run_sql -d postgres <<SQL
+select format('alter database %I refresh collation version;', datname)
+from pg_database
+where oid = $oid
+\gexec
+SQL
+}
+
+process_database() {
+	local db="$1"
+	local conn oids rc oid reindex_failed=0
+
+	conn="$(conninfo_for_db "$db")"
+
+	# a. Reindex first, so a stale index is never masked by an updated catalog.
+	oids="$(run_sql -d "$conn" -Atq -c "$(affected_index_oids_sql)")"
+	rc=$?
+	if [ "$rc" -ne 0 ]; then
+		log "WARN could not enumerate affected indexes on $db (psql rc=$rc); skipping refresh to preserve signal"
+		SCRIPT_FAILED=1
+		return
+	fi
+	while IFS= read -r oid; do
+		[ -z "$oid" ] && continue
+		if ! [[ $oid =~ ^[0-9]+$ ]]; then
+			log "WARN ignoring non-numeric index oid '$oid' on $db"
+			SCRIPT_FAILED=1
+			reindex_failed=1
+			continue
+		fi
+		log "reindex $db :: index oid $oid"
+		if ! reindex_index_by_oid "$conn" "$oid"; then
+			log "WARN reindex failed on $db :: index oid $oid (continuing)"
+			SCRIPT_FAILED=1
+			reindex_failed=1
+		fi
+	done <<<"$oids"
+
+	# b. Refresh collation versions — only if every reindex succeeded, else we'd
+	#    erase the signal that the index still needs rebuilding.
+	if [ "$reindex_failed" -ne 0 ]; then
+		log "skipping collation refresh on $db: reindex incomplete (stale-index signal preserved)"
+		printf '%s\n' "$db" >>"$ADVISORY_DBS"
+		return
+	fi
+
+	# Exclusion/invalid indexes we cannot rebuild automatically: record an advisory,
+	# do NOT fail, and skip this database's refresh entirely (named collations AND
+	# the db default in section c) so a stale version is never stamped over an
+	# un-rebuilt index. The advisory persists until the customer reindexes manually.
+	local advisories
+	advisories="$(run_sql -d "$conn" -Atq -c "$(affected_advisory_sql)")"
+	rc=$?
+	if [ "$rc" -ne 0 ]; then
+		log "WARN could not enumerate advisory indexes on $db (psql rc=$rc); skipping refresh to preserve signal"
+		SCRIPT_FAILED=1
+		printf '%s\n' "$db" >>"$ADVISORY_DBS"
+		return
+	fi
+	if [ -n "$advisories" ]; then
+		printf '%s\n' "$advisories" >>"$ADVISORY_NDJSON"
+		printf '%s\n' "$db" >>"$ADVISORY_DBS"
+		log "advisory: $db has exclusion/invalid indexes needing manual REINDEX; skipping this DB's collation refresh"
+		return
+	fi
+
+	oids="$(run_sql -d "$conn" -Atq -c "$(stale_collation_oids_sql)")"
+	rc=$?
+	if [ "$rc" -ne 0 ]; then
+		log "WARN could not enumerate stale collations on $db (psql rc=$rc)"
+		SCRIPT_FAILED=1
+		return
+	fi
+	while IFS= read -r oid; do
+		[ -z "$oid" ] && continue
+		if ! [[ $oid =~ ^[0-9]+$ ]]; then
+			log "WARN ignoring non-numeric collation oid '$oid' on $db"
+			SCRIPT_FAILED=1
+			continue
+		fi
+		log "refresh collation $db :: collation oid $oid"
+		if ! refresh_collation_by_oid "$conn" "$oid"; then
+			log "WARN collation refresh failed on $db :: collation oid $oid (continuing)"
+			SCRIPT_FAILED=1
+		fi
+	done <<<"$oids"
+}
+
+main() {
+	# Fresh, readable, empty snapshot up front; overwritten at the end if findings.
+	printf '[]' >"$ADVISORY_FILE" 2>/dev/null || true
+	chmod 0644 "$ADVISORY_FILE" 2>/dev/null || true
+	ADVISORY_NDJSON="$(mktemp)"
+	ADVISORY_DBS="$(mktemp)"
+	trap 'rm -f "$ADVISORY_NDJSON" "$ADVISORY_DBS"' EXIT
+
+	if ! retry 8 pg_isready -h localhost -U supabase_admin -d postgres; then
+		log "postgres not ready after retries; could not run (reporting failure)"
+		SCRIPT_FAILED=1
+		return
+	fi
+
+	# Read replica: catalogs are read-only (changes stream from the primary) and
+	# every REINDEX/ALTER would fail "read-only transaction"; skip.
+	local in_recovery
+	in_recovery="$(run_sql -d postgres -Atq -c "select pg_is_in_recovery();")" || in_recovery=""
+	if [ "$in_recovery" = "t" ]; then
+		log "server is in recovery (replica); skipping"
+		return 0
+	fi
+
+	# Unknown/non-numeric version must not pass the floor check — skip, don't proceed.
+	local svn
+	svn="$(run_sql -d postgres -Atq -c "select current_setting('server_version_num');")" || svn=""
+	if ! [[ $svn =~ ^[0-9]+$ ]]; then
+		log "could not read a numeric server_version_num (got '${svn}'); skipping"
+		return 0
+	fi
+	if [ "$svn" -lt "$MIN_SERVER_VERSION_NUM" ]; then
+		log "server_version_num=$svn < $MIN_SERVER_VERSION_NUM; collation version tracking unavailable, skipping"
+		return 0
+	fi
+
+	local dbs rc db oids oid dbname adv_json
+	dbs="$(run_sql -d postgres -Atq -c "select datname from pg_database where datallowconn and datname <> 'template0' order by datname;")"
+	rc=$?
+	if [ "$rc" -ne 0 ]; then
+		log "WARN could not enumerate databases (psql rc=$rc)"
+		SCRIPT_FAILED=1
+		return
+	fi
+	while IFS= read -r db; do
+		[ -z "$db" ] && continue
+		process_database "$db"
+	done <<<"$dbs"
+
+	# c. Database-default versions (shared catalog), after every db's default-
+	#    collated indexes were reindexed above. Includes template0.
+	oids="$(run_sql -d postgres -Atq -c "$(stale_db_default_oids_sql)")"
+	rc=$?
+	if [ "$rc" -ne 0 ]; then
+		log "WARN could not enumerate stale database defaults (psql rc=$rc)"
+		SCRIPT_FAILED=1
+	else
+		while IFS= read -r oid; do
+			[ -z "$oid" ] && continue
+			if ! [[ $oid =~ ^[0-9]+$ ]]; then
+				log "WARN ignoring non-numeric database oid '$oid'"
+				SCRIPT_FAILED=1
+				continue
+			fi
+			dbname="$(run_sql -d postgres -Atq -c "select datname from pg_database where oid = $oid;")"
+			rc=$?
+			if [ "$rc" -ne 0 ] || [ -z "$dbname" ]; then
+				log "WARN could not resolve datname for database oid $oid (psql rc=$rc); skipping db-default refresh to avoid masking advisories"
+				SCRIPT_FAILED=1
+				continue
+			fi
+			if grep -qxF "$dbname" "$ADVISORY_DBS"; then
+				log "skipping db-default refresh for $dbname: outstanding collation advisories"
+				continue
+			fi
+			log "refresh (db default) :: database oid $oid"
+			if ! refresh_db_default_by_oid "$oid"; then
+				log "WARN database-default refresh failed :: database oid $oid (continuing)"
+				SCRIPT_FAILED=1
+			fi
+		done <<<"$oids"
+	fi
+
+	# Assemble the JSON array snapshot from per-DB NDJSON. Fail-open: any problem
+	# leaves the "[]" written at entry. jq matches the sibling scripts' JSON tooling.
+	if [ -s "$ADVISORY_NDJSON" ]; then
+		if adv_json="$(jq -s '.' "$ADVISORY_NDJSON" 2>/dev/null)"; then
+			printf '%s\n' "$adv_json" >"${ADVISORY_FILE}.tmp" &&
+				mv "${ADVISORY_FILE}.tmp" "$ADVISORY_FILE"
+			chmod 0644 "$ADVISORY_FILE" 2>/dev/null || true
+		else
+			log "WARN could not assemble advisory JSON; leaving prior snapshot"
+			SCRIPT_FAILED=1
+		fi
+	fi
+
+	log "done (failed=$SCRIPT_FAILED)"
+}
+
+main
+exit "$SCRIPT_FAILED"
diff --git a/ansible/files/adminapi.sudoers.conf b/ansible/files/adminapi.sudoers.conf
index e6d27bd1..ab10665a 100644
--- a/ansible/files/adminapi.sudoers.conf
+++ b/ansible/files/adminapi.sudoers.conf
@@ -14,6 +14,7 @@ Cmnd_Alias PGBOUNCER = /bin/systemctl start pgbouncer.service, /bin/systemctl st
 %adminapi ALL= NOPASSWD: /etc/adminapi/pg_upgrade_scripts/check.sh
 %adminapi ALL= NOPASSWD: /etc/adminapi/pg_upgrade_scripts/common.sh
 %adminapi ALL= NOPASSWD: /etc/adminapi/pg_upgrade_scripts/pgsodium_getkey.sh
+%adminapi ALL= NOPASSWD: /etc/adminapi/pg_upgrade_scripts/refresh_collation.sh
 %adminapi ALL= NOPASSWD: /usr/bin/systemctl daemon-reload
 # pgBackRest wrapper scripts: constrained helpers called by supabase-admin-agent.
 # pgdata-chown runs as root (default); pgdata-signal runs as postgres so it can
diff --git a/ansible/tasks/internal/admin-api.yml b/ansible/tasks/internal/admin-api.yml
index 6affb840..39d43569 100644
--- a/ansible/tasks/internal/admin-api.yml
+++ b/ansible/tasks/internal/admin-api.yml
@@ -66,6 +66,7 @@
     - { file: "prepare.sh" }
     - { file: "pgsodium_getkey.sh" }
     - { file: "common.sh" }
+    - { file: "refresh_collation.sh" }
 
 - name: adminapi - create service file
   template:
diff --git a/audit-specs/baselines/baseline.yml b/audit-specs/baselines/baseline.yml
index 0c7810c6..395366c1 100644
--- a/audit-specs/baselines/baseline.yml
+++ b/audit-specs/baselines/baseline.yml
@@ -145,6 +145,12 @@ file:
     owner: "1006"
     group: "0"
     filetype: file
+  /etc/adminapi/pg_upgrade_scripts/refresh_collation.sh:
+    exists: true
+    mode: "0755"
+    owner: "1006"
+    group: "0"
+    filetype: file
   /etc/alternatives/README:
     exists: true
     mode: "0644"


[parent: fc7086da5c57]