supabase-postgres.git / summary / log / commit / refs
commit 1fa57498313b7a2f866132058495c758f8a7a930
Author: Utkarash Kumar Singh <utkarash2991@users.noreply.github.com>
Commit: GitHub <noreply@github.com>
Date: Mon Aug 03 14:33:30 2026 +0000
chore: enable extension version restriction (warn) and cut AMIs (#2315)
* chore: enable extension version restriction (warn) and cut AMIs
supautils v3.4.0 (already on develop, #2311) ships
supautils.restrict_extension_versions (off|warn|error, default off).
Set it to warn in supautils.conf.j2: CREATE/ALTER EXTENSION version
clauses from non-superuser roles are ignored with a WARNING and the
extension's default_version is used instead. Superusers and
supabase_admin (via privileged_extensions_superuser) are exempt.
warn is rollout phase 1: fleet log warnings measure explicit-version
usage before the separately-announced flip to error.
Bumps postgres_release for fresh AMIs.
Announcement (effective 2026-08-05): https://github.com/supabase/changelog/pull/128
Linear: PSQL-1159
* test: assert warn-mode version restriction as platform roles
The pg_regress suite runs against the rendered supautils.conf.j2 (via
start-postgres-server) with migrations applied, so the new conf setting
is asserted in the same PR that makes it: as the non-superuser postgres
role a CREATE EXTENSION version clause is ignored with a WARNING and the
default version is installed; supabase_admin (the configured
privileged_extensions_superuser) keeps version pinning, warning-free.
Skipped on the CLI variant, which lacks the postgres role (same as
pg_net_worker_privileges).
* chore: bump postgres_release to cut AMIs with the warn config
ansible/files/postgresql_config/supautils.conf.j2 | 1 +
ansible/vars.yml | 6 +--
nix/checks.nix | 1 +
nix/tests/expected/supautils_restrict_versions.out | 52 ++++++++++++++++++++++
nix/tests/sql/supautils_restrict_versions.sql | 38 ++++++++++++++++
5 files changed, 95 insertions(+), 3 deletions(-)
diff --git a/ansible/files/postgresql_config/supautils.conf.j2 b/ansible/files/postgresql_config/supautils.conf.j2
index 58739905..16ce476d 100644
--- a/ansible/files/postgresql_config/supautils.conf.j2
+++ b/ansible/files/postgresql_config/supautils.conf.j2
@@ -9,6 +9,7 @@ supautils.drop_trigger_grants = '{"postgres":["auth.audit_log_entries","auth.flo
# NOTE: keep nix/tests/prime-superuser.sql in sync with the "may be unsafe" + "deprecated" lists above.
supautils.privileged_extensions = 'address_standardizer, address_standardizer_data_us, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hstore, http, hypopg, index_advisor, insert_username, intarray, isn, ltree, moddatetime, orioledb, pg_buffercache, pg_cron, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_prewarm, pg_repack, pg_stat_monitor, pg_stat_statements, pg_tle, pg_trgm, pg_walinspect, pgaudit, pgcrypto, pgjwt, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgsodium, pgstattuple, pgtap, plcoffee, pljava, plls, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, vector, wrappers'
supautils.extension_custom_scripts_path = '/etc/postgresql-custom/extension-custom-scripts'
+supautils.restrict_extension_versions = 'warn'
supautils.privileged_extensions_superuser = 'supabase_admin'
supautils.privileged_role = 'supabase_privileged_role'
supautils.privileged_role_allowed_configs = 'auto_explain.*, deadlock_timeout, log_duration, log_lock_waits, log_min_duration_statement, log_min_error_statement, log_min_messages, log_parameter_max_length, log_replication_commands, log_statement, log_temp_files, pg_net.batch_size, pg_net.ttl, pg_stat_statements.*, pgaudit.log, pgaudit.log_catalog, pgaudit.log_client, pgaudit.log_level, pgaudit.log_relation, pgaudit.log_rows, pgaudit.log_statement, pgaudit.log_statement_once, pgaudit.role, pgrst.*, plan_filter.*, safeupdate.enabled, session_replication_role, track_functions, track_io_timing, wal_compression'
diff --git a/ansible/vars.yml b/ansible/vars.yml
index 43cac8c0..e1bb98a5 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -11,9 +11,9 @@ postgres_major:
# This is the source of truth for Postgres versions used in the Dockerfiles, and
# is used to derive image tags and base images in the release matrix.
postgres_release:
- postgresorioledb-17: "17.9.0.011-orioledb"
- postgres17: "17.6.1.158"
- postgres15: "15.14.1.158"
+ postgresorioledb-17: "17.9.0.012-orioledb"
+ postgres17: "17.6.1.159"
+ postgres15: "15.14.1.159"
# Docker release matrix — base images built first, layered images built on top.
# tag and base_tag are derived at build time from postgres_release via release_key.
# tag_suffix is appended to the release version to form the final image tag.
diff --git a/nix/checks.nix b/nix/checks.nix
index 776491bc..d56a3330 100644
--- a/nix/checks.nix
+++ b/nix/checks.nix
@@ -253,6 +253,7 @@
"extensions_schema" # tests extension loading
"roles" # includes roles/schemas from extensions not in CLI (pgtle, pgmq, repack, topology)
"pg_net_worker_privileges" # needs the authenticated/postgres roles from the full migrations, not present in the CLI prime file
+ "supautils_restrict_versions" # needs the postgres role + primed hstore from the full migrations/prime, not present in the CLI variant
# Version-specific extension tests
"z_17_ext_interface"
"z_17_pg_stat_monitor"
diff --git a/nix/tests/expected/supautils_restrict_versions.out b/nix/tests/expected/supautils_restrict_versions.out
new file mode 100644
index 00000000..514016f1
--- /dev/null
+++ b/nix/tests/expected/supautils_restrict_versions.out
@@ -0,0 +1,52 @@
+-- supautils.conf.j2 sets supautils.restrict_extension_versions = 'warn':
+-- CREATE/ALTER EXTENSION version clauses from non-exempt roles are ignored
+-- with a WARNING and the extension's default_version is used instead.
+--
+-- This suite runs against the rendered supautils.conf.j2 with the real
+-- migrations applied (see nix/tools/run-server.sh.in), so the restriction
+-- can be asserted as the actual platform roles. The supautils regress suite
+-- covers the GUC logic (all modes, ALTER, duplicate clauses); this test
+-- covers the platform wiring. See PSQL-1159.
+-- the platform config sets warn mode
+show supautils.restrict_extension_versions;
+ supautils.restrict_extension_versions
+---------------------------------------
+ warn
+(1 row)
+
+-- precondition: postgres is not a superuser, else it would be exempt
+select rolsuper from pg_roles where rolname = 'postgres';
+ rolsuper
+----------
+ f
+(1 row)
+
+-- drop the hstore created by prime.sql so the creates below are observable
+drop extension hstore;
+-- non-exempt role: the version clause is ignored with a warning and the
+-- default version is installed
+set role postgres;
+create extension hstore version '1.4';
+WARNING: only superusers can specify extension versions, ignoring version "1.4" and installing the default version
+select extversion = default_version as installed_default
+ from pg_extension, pg_available_extensions
+ where extname = name and extname = 'hstore';
+ installed_default
+-------------------
+ t
+(1 row)
+
+reset role;
+drop extension hstore;
+-- exempt role (supabase_admin, via supautils.privileged_extensions_superuser):
+-- the version clause is honored, with no warning
+create extension hstore version '1.4';
+select extversion from pg_extension where extname = 'hstore';
+ extversion
+------------
+ 1.4
+(1 row)
+
+-- restore the state prime.sql created (hstore at default version)
+drop extension hstore;
+create extension hstore;
diff --git a/nix/tests/sql/supautils_restrict_versions.sql b/nix/tests/sql/supautils_restrict_versions.sql
new file mode 100644
index 00000000..9f5d6f3a
--- /dev/null
+++ b/nix/tests/sql/supautils_restrict_versions.sql
@@ -0,0 +1,38 @@
+-- supautils.conf.j2 sets supautils.restrict_extension_versions = 'warn':
+-- CREATE/ALTER EXTENSION version clauses from non-exempt roles are ignored
+-- with a WARNING and the extension's default_version is used instead.
+--
+-- This suite runs against the rendered supautils.conf.j2 with the real
+-- migrations applied (see nix/tools/run-server.sh.in), so the restriction
+-- can be asserted as the actual platform roles. The supautils regress suite
+-- covers the GUC logic (all modes, ALTER, duplicate clauses); this test
+-- covers the platform wiring. See PSQL-1159.
+
+-- the platform config sets warn mode
+show supautils.restrict_extension_versions;
+
+-- precondition: postgres is not a superuser, else it would be exempt
+select rolsuper from pg_roles where rolname = 'postgres';
+
+-- drop the hstore created by prime.sql so the creates below are observable
+drop extension hstore;
+
+-- non-exempt role: the version clause is ignored with a warning and the
+-- default version is installed
+set role postgres;
+create extension hstore version '1.4';
+select extversion = default_version as installed_default
+ from pg_extension, pg_available_extensions
+ where extname = name and extname = 'hstore';
+reset role;
+
+drop extension hstore;
+
+-- exempt role (supabase_admin, via supautils.privileged_extensions_superuser):
+-- the version clause is honored, with no warning
+create extension hstore version '1.4';
+select extversion from pg_extension where extname = 'hstore';
+
+-- restore the state prime.sql created (hstore at default version)
+drop extension hstore;
+create extension hstore;
[parent: e75b52784229]