supabase-postgres.git / summary / log / commit / refs
commit e030a881977672675b2e51eef587075cdb320ee6
Author: Artyom Borissov <33684505+srcCraftsman@users.noreply.github.com>
Commit: GitHub <noreply@github.com>
Date: Fri Sep 18 13:16:09 2026 +0000
fix: (ansible/admin-api) removing collation refresh script (#2466)
.../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 -
4 files changed, 424 deletions(-)
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
deleted file mode 100755
index 1b464436..00000000
--- a/ansible/files/admin_api_scripts/pg_upgrade_scripts/refresh_collation.sh
+++ /dev/null
@@ -1,416 +0,0 @@
-#! /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 ab10665a..e6d27bd1 100644
--- a/ansible/files/adminapi.sudoers.conf
+++ b/ansible/files/adminapi.sudoers.conf
@@ -14,7 +14,6 @@ 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 8159af1d..be1673a5 100644
--- a/ansible/tasks/internal/admin-api.yml
+++ b/ansible/tasks/internal/admin-api.yml
@@ -73,7 +73,6 @@
- { 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 71c9b422..c3f06554 100644
--- a/audit-specs/baselines/baseline.yml
+++ b/audit-specs/baselines/baseline.yml
@@ -145,12 +145,6 @@ 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"