extension_control_path

The new GUC extension_control_path specifies a path to look for
extension control files.  The default value is $system, which looks in
the compiled-in location, as before.

The path search uses the same code and works in the same way as
dynamic_library_path.

Some use cases of this are: (1) testing extensions during package
builds, (2) installing extensions outside security-restricted
containers like Python.app (on macOS), (3) adding extensions to
PostgreSQL running in a Kubernetes environment using operators such as
CloudNativePG without having to rebuild the base image for each new
extension.

There is also a tweak in Makefile.global so that it is possible to
install extensions using PGXS into an different directory than the
default, using 'make install prefix=/else/where'.  This previously
only worked when specifying the subdirectories, like 'make install
datadir=/else/where/share pkglibdir=/else/where/lib', for purely
implementation reasons.  (Of course, without the path feature,
installing elsewhere was rarely useful.)

Author: Peter Eisentraut <peter@eisentraut.org>
Co-authored-by: Matheus Alcantara <matheusssilv97@gmail.com>
Reviewed-by: David E. Wheeler <david@justatheory.com>
Reviewed-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Reviewed-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
Reviewed-by: Niccolò Fei <niccolo.fei@enterprisedb.com>
Discussion: https://www.postgresql.org/message-id/flat/E7C7BFFB-8857-48D4-A71F-88B359FADCFD@justatheory.com

3 files changed, 86 insertions, 0 deletions

diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index 1dbec14cba3..a3591bf3d2f 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -28,6 +28,7 @@ DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext_req_schema3--1.0.sql
 
 REGRESS = test_extensions test_extdepend
+TAP_TESTS = 1
 
 # force C locale for output stability
 NO_LOCALE = 1
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index dd7ec0ce56b..3c7e378bf35 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -57,4 +57,9 @@ tests += {
     ],
     'regress_args': ['--no-locale'],
   },
+  'tap': {
+    'tests': [
+      't/001_extension_control_path.pl',
+    ],
+  },
 }
diff --git a/src/test/modules/test_extensions/t/001_extension_control_path.pl b/src/test/modules/test_extensions/t/001_extension_control_path.pl
new file mode 100644
index 00000000000..7160009739a
--- /dev/null
+++ b/src/test/modules/test_extensions/t/001_extension_control_path.pl
@@ -0,0 +1,80 @@
+# Copyright (c) 2024-2025, PostgreSQL Global Development Group
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('node');
+
+$node->init;
+
+# Create a temporary directory for the extension control file
+my $ext_dir = PostgreSQL::Test::Utils::tempdir();
+my $ext_name = "test_custom_ext_paths";
+my $control_file = "$ext_dir/$ext_name.control";
+my $sql_file = "$ext_dir/$ext_name--1.0.sql";
+
+# Create .control .sql file
+open my $cf, '>', $control_file or die "Could not create control file: $!";
+print $cf "comment = 'Test extension_control_path'\n";
+print $cf "default_version = '1.0'\n";
+print $cf "relocatable = true\n";
+close $cf;
+
+# Create --1.0.sql file
+open my $sqlf, '>', $sql_file or die "Could not create sql file: $!";
+print $sqlf "/* $sql_file */\n";
+print $sqlf
+  "-- complain if script is sourced in psql, rather than via CREATE EXTENSION\n";
+print $sqlf
+  qq'\\echo Use "CREATE EXTENSION $ext_name" to load this file. \\quit\n';
+close $sqlf;
+
+# Use the correct separator and escape \ when running on Windows.
+my $sep = $windows_os ? ";" : ":";
+$node->append_conf(
+	'postgresql.conf', qq{
+extension_control_path = '\$system$sep@{[ $windows_os ? ($ext_dir =~ s/\\/\\\\/gr) : $ext_dir ]}'
+});
+
+# Start node
+$node->start;
+
+my $ecp = $node->safe_psql('postgres', 'show extension_control_path;');
+
+is($ecp, "\$system$sep$ext_dir",
+	"custom extension control directory path configured");
+
+$node->safe_psql('postgres', "CREATE EXTENSION $ext_name");
+
+my $ret = $node->safe_psql('postgres',
+	"select * from pg_available_extensions where name = '$ext_name'");
+is( $ret,
+	"test_custom_ext_paths|1.0|1.0|Test extension_control_path",
+	"extension is installed correctly on pg_available_extensions");
+
+my $ret2 = $node->safe_psql('postgres',
+	"select * from pg_available_extension_versions where name = '$ext_name'");
+is( $ret2,
+	"test_custom_ext_paths|1.0|t|t|f|t|||Test extension_control_path",
+	"extension is installed correctly on pg_available_extension_versions");
+
+# Ensure that extensions installed on $system is still visible when using with
+# custom extension control path.
+my $ret3 = $node->safe_psql('postgres',
+	"select count(*) > 0 as ok from pg_available_extensions where name = 'amcheck'"
+);
+is($ret3, "t",
+	"\$system extension is installed correctly on pg_available_extensions");
+
+
+my $ret4 = $node->safe_psql('postgres',
+	"set extension_control_path = ''; select count(*) > 0 as ok from pg_available_extensions where name = 'amcheck'"
+);
+is($ret4, "t",
+	"\$system extension is installed correctly on pg_available_extensions with empty extension_control_path"
+);
+
+done_testing();