Subject: Include extension path on pg_available_extensions

From: "Matheus Alcantara" <[email-masked]>
Date: Mon, 15 Sep 2025 21:18:25 -0300

Hi all,

On [1] it was mentioned that it could be a good idea to include the
extension location when listening the available extensions on
pg_available_extensions to make it clear to the user the location of an
extension that Postgres is seeing based on the extension_control_path
GUC.

The attached patch implements this idea. Extensions installed on $system
path will not show the actual value of the $system macro and it will
show the macro itself, for example:

postgres=# show extension_control_path;
              extension_control_path
---------------------------------------------------
 /usr/local/my/extensions/share/postgresql:$system
(1 row)

postgres=# select * from pg_available_extensions;
  name   | default_version | installed_version |                     comment                      |                  location
---------+-----------------+-------------------+--------------------------------------------------+---------------------------------------------------
 envvar  | 1.0.0           |                   | Get the value of a server environment variable   | /usr/local/my/extensions/share/postgresql/extension
 amcheck | 1.5             |                   | functions for verifying relation integrity       | $system
 bloom   | 1.0             |                   | bloom access method - signature file based index | $system

I'm not sure if this should be included on 18 release since this is not
a bug fix but an improvement on the extension system by itself.

Any opinions on this?

[1] https://www.postgresql.org/message-id/CAKFQuwbR1Fzr8yRuMW%3DN1UMA1cTpFcqZe9bW_-ZF8%3DBa2Ud2%3Dw%40mail.gmail.com

--
Matheus Alcantara
From 7a1c93f344c61c21f56692fe8eff77e5092929c0 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email-masked]>
Date: Mon, 15 Sep 2025 15:46:24 -0300
Subject: [PATCH v1] Add path of extension on pg_available_extensions

---
 src/backend/catalog/system_views.sql          |  4 +-
 src/backend/commands/extension.c              | 80 ++++++++++++++-----
 src/include/catalog/pg_proc.dat               | 10 +--
 .../t/001_extension_control_path.pl           | 13 ++-
 src/test/regress/expected/rules.out           | 10 ++-
 5 files changed, 83 insertions(+), 34 deletions(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index c77fa0234bb..8e3bc61fe4a 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -402,14 +402,14 @@ CREATE VIEW pg_cursors AS
 
 CREATE VIEW pg_available_extensions AS
     SELECT E.name, E.default_version, X.extversion AS installed_version,
-           E.comment
+           E.comment, E.location
       FROM pg_available_extensions() AS E
            LEFT JOIN pg_extension AS X ON E.name = X.extname;
 
 CREATE VIEW pg_available_extension_versions AS
     SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed,
            E.superuser, E.trusted, E.relocatable,
-           E.schema, E.requires, E.comment
+           E.schema, E.requires, E.comment, E.location
       FROM pg_available_extension_versions() AS E
            LEFT JOIN pg_extension AS X
              ON E.name = X.extname AND E.version = X.extversion;
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 93ef1ad106f..3f8e74f1211 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -126,6 +126,19 @@ typedef struct
 	ParseLoc	stmt_len;		/* length in bytes; 0 means "rest of string" */
 } script_error_callback_arg;
 
+/*
+ * A location configured on extension_control_path GUC.
+ *
+ * The macro is the macro plaeholder that the extension_control_path support
+ * and which is replaced by a system value that is stored on loc. For custom
+ * paths that don't have a macro the macro field is NULL.
+ */
+typedef struct
+{
+	char	   *macro;
+	char	   *loc;
+} Location;
+
 /* Local functions */
 static List *find_update_path(List *evi_list,
 							  ExtensionVersionInfo *evi_start,
@@ -140,7 +153,8 @@ static Oid	get_required_extension(char *reqExtensionName,
 								   bool is_create);
 static void get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 												 Tuplestorestate *tupstore,
-												 TupleDesc tupdesc);
+												 TupleDesc tupdesc,
+												 Location *location);
 static Datum convert_requires_to_datum(List *requires);
 static void ApplyExtensionUpdates(Oid extensionOid,
 								  ExtensionControlFile *pcontrol,
@@ -354,7 +368,11 @@ get_extension_control_directories(void)
 
 	if (strlen(Extension_control_path) == 0)
 	{
-		paths = lappend(paths, system_dir);
+		Location   *location = palloc0_object(Location);
+
+		location->macro = NULL;
+		location->loc = system_dir;
+		paths = lappend(paths, location);
 	}
 	else
 	{
@@ -366,6 +384,7 @@ get_extension_control_directories(void)
 			int			len;
 			char	   *mangled;
 			char	   *piece = first_path_var_separator(ecp);
+			Location   *location = palloc0_object(Location);
 
 			/* Get the length of the next path on ecp */
 			if (piece == NULL)
@@ -382,15 +401,21 @@ get_extension_control_directories(void)
 			 * suffix if it is a custom extension control path.
 			 */
 			if (strcmp(piece, "$system") == 0)
+			{
+				location->macro = pstrdup(piece);
 				mangled = substitute_path_macro(piece, "$system", system_dir);
+			}
 			else
+			{
+				location->macro = NULL;
 				mangled = psprintf("%s/extension", piece);
-
+			}
 			pfree(piece);
 
 			/* Canonicalize the path based on the OS and add to the list */
 			canonicalize_path(mangled);
-			paths = lappend(paths, mangled);
+			location->loc = mangled;
+			paths = lappend(paths, location);
 
 			/* Break if ecp is empty or move to the next path on ecp */
 			if (ecp[len] == '\0')
@@ -2215,9 +2240,9 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 
 	locations = get_extension_control_directories();
 
-	foreach_ptr(char, location, locations)
+	foreach_ptr(Location, location, locations)
 	{
-		dir = AllocateDir(location);
+		dir = AllocateDir(location->loc);
 
 		/*
 		 * If the control directory doesn't exist, we want to silently return
@@ -2229,13 +2254,13 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 		}
 		else
 		{
-			while ((de = ReadDir(dir, location)) != NULL)
+			while ((de = ReadDir(dir, location->loc)) != NULL)
 			{
 				ExtensionControlFile *control;
 				char	   *extname;
 				String	   *extname_str;
-				Datum		values[3];
-				bool		nulls[3];
+				Datum		values[4];
+				bool		nulls[4];
 
 				if (!is_extension_control_filename(de->d_name))
 					continue;
@@ -2259,7 +2284,7 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 					found_ext = lappend(found_ext, extname_str);
 
 				control = new_ExtensionControlFile(extname);
-				control->control_dir = pstrdup(location);
+				control->control_dir = pstrdup(location->loc);
 				parse_extension_control_file(control, NULL);
 
 				memset(values, 0, sizeof(values));
@@ -2279,6 +2304,12 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 				else
 					values[2] = CStringGetTextDatum(control->comment);
 
+				/* location */
+				if (location->macro == NULL)
+					values[3] = CStringGetTextDatum(location->loc);
+				else
+					values[3] = CStringGetTextDatum(location->macro);
+
 				tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
 									 values, nulls);
 			}
@@ -2313,9 +2344,9 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 
 	locations = get_extension_control_directories();
 
-	foreach_ptr(char, location, locations)
+	foreach_ptr(Location, location, locations)
 	{
-		dir = AllocateDir(location);
+		dir = AllocateDir(location->loc);
 
 		/*
 		 * If the control directory doesn't exist, we want to silently return
@@ -2327,7 +2358,7 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 		}
 		else
 		{
-			while ((de = ReadDir(dir, location)) != NULL)
+			while ((de = ReadDir(dir, location->loc)) != NULL)
 			{
 				ExtensionControlFile *control;
 				char	   *extname;
@@ -2356,12 +2387,13 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 
 				/* read the control file */
 				control = new_ExtensionControlFile(extname);
-				control->control_dir = pstrdup(location);
+				control->control_dir = pstrdup(location->loc);
 				parse_extension_control_file(control, NULL);
 
 				/* scan extension's script directory for install scripts */
 				get_available_versions_for_extension(control, rsinfo->setResult,
-													 rsinfo->setDesc);
+													 rsinfo->setDesc,
+													 location);
 			}
 
 			FreeDir(dir);
@@ -2378,7 +2410,8 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 static void
 get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 									 Tuplestorestate *tupstore,
-									 TupleDesc tupdesc)
+									 TupleDesc tupdesc,
+									 Location *location)
 {
 	List	   *evi_list;
 	ListCell   *lc;
@@ -2391,8 +2424,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 	{
 		ExtensionVersionInfo *evi = (ExtensionVersionInfo *) lfirst(lc);
 		ExtensionControlFile *control;
-		Datum		values[8];
-		bool		nulls[8];
+		Datum		values[9];
+		bool		nulls[9];
 		ListCell   *lc2;
 
 		if (!evi->installable)
@@ -2434,6 +2467,12 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 		else
 			values[7] = CStringGetTextDatum(control->comment);
 
+		/* location */
+		if (location->macro == NULL)
+			values[8] = CStringGetTextDatum(location->loc);
+		else
+			values[8] = CStringGetTextDatum(location->macro);
+
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 
 		/*
@@ -2475,6 +2514,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 				}
 				/* comment stays the same */
 
+				/* location stays the same */
+
 				tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 			}
 		}
@@ -3903,7 +3944,8 @@ find_in_paths(const char *basename, List *paths)
 
 	foreach(cell, paths)
 	{
-		char	   *path = lfirst(cell);
+		Location   *location = lfirst(cell);
+		char	   *path = location->loc;
 		char	   *full;
 
 		Assert(path != NULL);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 03e82d28c87..4c2d16a4f6c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10735,16 +10735,16 @@
 { oid => '3082', descr => 'list available extensions',
   proname => 'pg_available_extensions', procost => '10', prorows => '100',
   proretset => 't', provolatile => 's', prorettype => 'record',
-  proargtypes => '', proallargtypes => '{name,text,text}',
-  proargmodes => '{o,o,o}', proargnames => '{name,default_version,comment}',
+  proargtypes => '', proallargtypes => '{name,text,text,text}',
+  proargmodes => '{o,o,o,o}', proargnames => '{name,default_version,comment,location}',
   prosrc => 'pg_available_extensions' },
 { oid => '3083', descr => 'list available extension versions',
   proname => 'pg_available_extension_versions', procost => '10',
   prorows => '100', proretset => 't', provolatile => 's',
   prorettype => 'record', proargtypes => '',
-  proallargtypes => '{name,text,bool,bool,bool,name,_name,text}',
-  proargmodes => '{o,o,o,o,o,o,o,o}',
-  proargnames => '{name,version,superuser,trusted,relocatable,schema,requires,comment}',
+  proallargtypes => '{name,text,bool,bool,bool,name,_name,text,text}',
+  proargmodes => '{o,o,o,o,o,o,o,o,o}',
+  proargnames => '{name,version,superuser,trusted,relocatable,schema,requires,comment,location}',
   prosrc => 'pg_available_extension_versions' },
 { oid => '3084', descr => 'list an extension\'s version update paths',
   proname => 'pg_extension_update_paths', procost => '10', prorows => '100',
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
index 7fbe5bde332..332c74d72bc 100644
--- a/src/test/modules/test_extensions/t/001_extension_control_path.pl
+++ b/src/test/modules/test_extensions/t/001_extension_control_path.pl
@@ -25,6 +25,10 @@ my $ext_name2 = "test_custom_ext_paths_using_directory";
 mkpath("$ext_dir/$ext_name2");
 create_extension($ext_name2, $ext_dir, $ext_name2);
 
+# Make windows path use Unix slashes as canonicalize_path() is called when
+# collecting extension control paths. See get_extension_control_directories().
+my $ext_dir_canonicalized = $windows_os ? ($ext_dir =~ s/\\/\//gr) : $ext_dir;
+
 # Use the correct separator and escape \ when running on Windows.
 my $sep = $windows_os ? ";" : ":";
 $node->append_conf(
@@ -43,29 +47,30 @@ is($ecp, "\$system$sep$ext_dir$sep$ext_dir2",
 $node->safe_psql('postgres', "CREATE EXTENSION $ext_name");
 $node->safe_psql('postgres', "CREATE EXTENSION $ext_name2");
 
+
 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",
+	"test_custom_ext_paths|1.0|1.0|Test extension_control_path|$ext_dir_canonicalized/extension",
 	"extension is installed correctly on pg_available_extensions");
 
 $ret = $node->safe_psql('postgres',
 	"select * from pg_available_extension_versions where name = '$ext_name'");
 is( $ret,
-	"test_custom_ext_paths|1.0|t|t|f|t|||Test extension_control_path",
+	"test_custom_ext_paths|1.0|t|t|f|t|||Test extension_control_path|$ext_dir_canonicalized/extension",
 	"extension is installed correctly on pg_available_extension_versions");
 
 $ret = $node->safe_psql('postgres',
 	"select * from pg_available_extensions where name = '$ext_name2'");
 is( $ret,
-	"test_custom_ext_paths_using_directory|1.0|1.0|Test extension_control_path",
+	"test_custom_ext_paths_using_directory|1.0|1.0|Test extension_control_path|$ext_dir_canonicalized/extension",
 	"extension is installed correctly on pg_available_extensions");
 
 $ret = $node->safe_psql('postgres',
 	"select * from pg_available_extension_versions where name = '$ext_name2'"
 );
 is( $ret,
-	"test_custom_ext_paths_using_directory|1.0|t|t|f|t|||Test extension_control_path",
+	"test_custom_ext_paths_using_directory|1.0|t|t|f|t|||Test extension_control_path|$ext_dir_canonicalized/extension",
 	"extension is installed correctly on pg_available_extension_versions");
 
 # Ensure that extensions installed on $system is still visible when using with
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 35e8aad7701..c876d2af0b8 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1310,14 +1310,16 @@ pg_available_extension_versions| SELECT e.name,
     e.relocatable,
     e.schema,
     e.requires,
-    e.comment
-   FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment)
+    e.comment,
+    e.location
+   FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment, location)
      LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
 pg_available_extensions| SELECT e.name,
     e.default_version,
     x.extversion AS installed_version,
-    e.comment
-   FROM (pg_available_extensions() e(name, default_version, comment)
+    e.comment,
+    e.location
+   FROM (pg_available_extensions() e(name, default_version, comment, location)
      LEFT JOIN pg_extension x ON ((e.name = x.extname)));
 pg_backend_memory_contexts| SELECT name,
     ident,
-- 
2.39.5 (Apple Git-154)

---

From: Quan Zongliang <[email-masked]>
Date: Thu, 23 Oct 2025 08:19:46 +0800

> [1] https://www.postgresql.org/message-id/CAKFQuwbR1Fzr8yRuMW%3DN1UMA1cTpFcqZe9bW_-ZF8%3DBa2Ud2%3Dw%40mail.gmail.com
Just as the discussion here. Adding extension location is a good idea.
Suppose there is an amcheck 1.5 located in the $system directory. There 
is also an amcheck 1.4.5 located in another path.

Strange results will then occur:
postgres=# SHOW extension_control_path;
  extension_control_path
------------------------
  $system
(1 row)

postgres=# CREATE EXTENSION amcheck;
CREATE EXTENSION
postgres=# select * from pg_available_extensions; 
  
                                name    | default_version | 
installed_version |                  comment                   | location
------------+-----------------+-------------------+--------------------------------------------+----------
  amcheck    | 1.5             | 1.5               | functions for 
verifying relation integrity | $system

This seems to be fine.

However, if another path is added, strange results will occur.

postgres=# SET extension_control_path TO 
'/Users/quanzl/build/pg-availext:$system';
SET
postgres=# select * from pg_available_extensions;
     name    | default_version | installed_version | 
comment                   |                 location
------------+-----------------+-------------------+--------------------------------------------+-------------------------------------------
  amcheck    | 1.4.5           | 1.5               | functions for 
verifying relation integrity | /Users/quanzl/build/pg-availext/extension

The results shown here will cause confusion. It is better to show the 
path used at creation.

So, it would be a better option to add a new column to the pg_extension 
table.

--
Quan Zongliang

---

From: Chao Li <[email-masked]>
Date: Thu, 23 Oct 2025 09:28:13 +0800

> Just as the discussion here. Adding extension location is a good idea.

+1. I like the ideal.

> --
> Matheus Alcantara
> <v1-0001-Add-path-of-extension-on-pg_available_extensions.patch>

Got a few comments:

1 - extension.c
```
+/*
```

Some problems in the comment:

* typo: plaebholder -> placeholder
* "the extension_control_path support” where “support” should be “supports”
* “stored on loc” should be “stored in loc”

Also, “The macro is the macro placeholder …” sounds redundant, suggested revision: “The macro field stores the name of a macro (for example “$system”) that  extension_control_path supports, which is replaced by …"

2 - extension.c
```
```

As you immediately assign values to all fields, palloc0_object() is not needed, palloc_object() is good enough.

3 - extension.c
```
```

In all execution paths, location will be initiated, thus palloc_object() is good enough.

4 - extension.c
```
+				/* location */
```

There are multiple places of this “if-else”. So, “macro” basically is for display, and loc is the real location. I am thinking, maybe we can change the definition of Location to:

```
structure Location {
  Char *display;
  Char *real;
```

When it is not a macro, just assign real to display, so that we can avoid all these “if-else”.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

---

From: "Euler Taveira" <[email-masked]>
Date: Thu, 23 Oct 2025 10:56:02 -0300

>> Just as the discussion here. Adding extension location is a good idea.

Exposing useful information might be a good idea except if it doesn't
compromise security. IIRC there is no function or view that exposes absolute
path to regular users.

The view pg_available_extensions has PUBLIC access. Check similar functions
using a query like:

SELECT proname,
       x.unnest AS argname
FROM
  (SELECT proname,
          unnest(proargnames)
   FROM pg_proc) AS x
WHERE x.unnest ~ 'file'
  OR x.unnest ~ 'path';

Some of the functions that return absolute path revoked PUBLIC access for
security reason. See pg_show_all_file_settings, pg_hba_file_rules, and
pg_ident_file_mappings. (All of these functions have a view that returns its
content similar to pg_available_extensions.) See system_views.sql.

Do we want to use a similar pattern (revoke PUBLIC access from the function)?
It breaks the compatibility but perhaps using an existent pre-defined role
(pg_read_all_settings?) may be less harmful.

There are at least 2 alternatives:

* separate function: add a new function that returns the absolute path. Don't
  grant PUBLIC access. It doesn't break compatibility but you need to modify
  your query.

* insufficient privilege: if the role doesn't have the sufficient privileges,
  return NULL or '<insufficient privilege>' (similar to pg_stat_activity). I
  don't have a strong preference but the latter can impose more effort to use
  if you don't know the role has sufficient privilege. However, it is clear why
  the absolute path is not returned.

-- 
Euler Taveira
EDB   https://www.enterprisedb.com/

---

From: "Matheus Alcantara" <[email-masked]>
Date: Thu, 23 Oct 2025 15:12:12 -0300

Thanks for testing this!

> postgres=# SHOW extension_control_path;
I agree that this sounds strange but the documentation [1] mention the
following:
    If extensions with equal names are present in multiple directories
    in the configured path, only the instance found first in the path
    will be used.

So I think that users should not use different paths to install the same
extension with different versions in practice.

> So, it would be a better option to add a new column to the pg_extension 
You mean add the location column on pg_extension instead of
pg_available_extensions? I'm not sure if I get the point here.

[1] https://www.postgresql.org/docs/18/runtime-config-client.html#GUC-EXTENSION-CONTROL-PATH

--
Matheus Alcantara

---

From: "Matheus Alcantara" <[email-masked]>
Date: Thu, 23 Oct 2025 15:13:46 -0300

Thanks for reviewing this!

> * “stored on loc” should be “stored in loc”
Fixed

> +		paths = lappend(paths, location);
Fixed

> In all execution paths, location will be initiated, thus palloc_object() is good enough.
Fixed

> When it is not a macro, just assign real to display, so that we can avoid all these “if-else”.
These struct fields sounds a bit unclear by just looking it without
reading the usages to me TBH. What do you think by creating a static
function that do the if-else and just use it? Perhaps make into a macro?

Attached v2 with all the fixes.

--
Matheus Alcantara
From 05c271a38d0a63c9aaf49c21c74c07fa8767a724 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email-masked]>
Date: Mon, 15 Sep 2025 15:46:24 -0300
Subject: [PATCH v2] Add path of extension on pg_available_extensions

---
 src/backend/catalog/system_views.sql          |  4 +-
 src/backend/commands/extension.c              | 85 ++++++++++++++-----
 src/include/catalog/pg_proc.dat               | 10 +--
 .../t/001_extension_control_path.pl           | 13 ++-
 src/test/regress/expected/rules.out           | 10 ++-
 5 files changed, 88 insertions(+), 34 deletions(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 823776c1498..76eea11d48b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -412,14 +412,14 @@ CREATE VIEW pg_cursors AS

index 93ef1ad106f..2e5489b4946 100644
@@ -126,6 +126,20 @@ typedef struct
 
+/*
+ * A location configured on extension_control_path GUC.
+ *
+ * The macro field stores the name of a macro (for example “$system”) that
+ * extension_control_path supports, which is replaced by a system value that is
+ * stored in loc. For custom paths that don't have a macro the macro field is
+ * NULL.
+ */
+typedef struct
+{
+	char	   *macro;
+	char	   *loc;
+} Location;
+
 /* Local functions */
 static List *find_update_path(List *evi_list,
 							  ExtensionVersionInfo *evi_start,
@@ -140,7 +154,8 @@ static Oid	get_required_extension(char *reqExtensionName,
 								   bool is_create);
 static void get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 												 Tuplestorestate *tupstore,
-												 TupleDesc tupdesc);
+												 TupleDesc tupdesc,
+												 Location *location);
 static Datum convert_requires_to_datum(List *requires);
 static void ApplyExtensionUpdates(Oid extensionOid,
 								  ExtensionControlFile *pcontrol,
@@ -157,6 +172,16 @@ static ExtensionControlFile *new_ExtensionControlFile(const char *extname);
 
 char	   *find_in_paths(const char *basename, List *paths);
 
+/*  Return the correct value to display the Location */
+static char *
+location_for_display(Location *loc)
+{
+	if (loc->macro == NULL)
+		return loc->loc;
+	else
+		return loc->macro;
+}
+
 /*
  * get_extension_oid - given an extension name, look up the OID
  *
@@ -354,7 +379,11 @@ get_extension_control_directories(void)
 
 	if (strlen(Extension_control_path) == 0)
 	{
-		paths = lappend(paths, system_dir);
+		Location   *location = palloc_object(Location);
+
+		location->macro = NULL;
+		location->loc = system_dir;
+		paths = lappend(paths, location);
 	}
 	else
 	{
@@ -366,6 +395,7 @@ get_extension_control_directories(void)
 			int			len;
 			char	   *mangled;
 			char	   *piece = first_path_var_separator(ecp);
+			Location   *location = palloc_object(Location);
 
@@ -382,15 +412,21 @@ get_extension_control_directories(void)

 			/* Break if ecp is empty or move to the next path on ecp */
 			if (ecp[len] == '\0')
@@ -2215,9 +2251,9 @@ pg_available_extensions(PG_FUNCTION_ARGS)

 
 		/*
@@ -2229,13 +2265,13 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 
 				if (!is_extension_control_filename(de->d_name))
 					continue;
@@ -2259,7 +2295,7 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 					found_ext = lappend(found_ext, extname_str);

 				memset(values, 0, sizeof(values));
@@ -2279,6 +2315,9 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 				else
 					values[2] = CStringGetTextDatum(control->comment);
 
+				/* location */
+				values[3] = CStringGetTextDatum(location_for_display(location));
+
 				tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
 									 values, nulls);
 			}
@@ -2313,9 +2352,9 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)

 
 		/*
 		 * If the control directory doesn't exist, we want to silently return
@@ -2327,7 +2366,7 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 		}
 		else
 		{
-			while ((de = ReadDir(dir, location)) != NULL)
+			while ((de = ReadDir(dir, location->loc)) != NULL)
 			{
 				ExtensionControlFile *control;
 				char	   *extname;
@@ -2356,12 +2395,13 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)

 
 			FreeDir(dir);
@@ -2378,7 +2418,8 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 static void
 get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 									 Tuplestorestate *tupstore,
-									 TupleDesc tupdesc)
+									 TupleDesc tupdesc,
+									 Location *location)
 {
 	List	   *evi_list;
 	ListCell   *lc;
@@ -2391,8 +2432,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 	{
 		ExtensionVersionInfo *evi = (ExtensionVersionInfo *) lfirst(lc);
 		ExtensionControlFile *control;
-		Datum		values[8];
-		bool		nulls[8];
+		Datum		values[9];
+		bool		nulls[9];
 		ListCell   *lc2;
 
 		if (!evi->installable)
@@ -2434,6 +2475,9 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 		else
 			values[7] = CStringGetTextDatum(control->comment);
 
+		/* location */
+		values[8] = CStringGetTextDatum(location_for_display(location));
+
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 
 		/*
@@ -2475,6 +2519,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 				}
 				/* comment stays the same */
 
+				/* location stays the same */
+
 				tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 			}
 		}
@@ -3903,7 +3949,8 @@ find_in_paths(const char *basename, List *paths)
 
 	foreach(cell, paths)
 	{
 
 		Assert(path != NULL);
index eecb43ec6f0..ab5ec86ca9d 100644
@@ -10743,16 +10743,16 @@

+

 
 
index 16753b2e4c0..85da655b893 100644
2.51.0

---

From: "Matheus Alcantara" <[email-masked]>
Date: Thu, 23 Oct 2025 15:14:12 -0300

>   the absolute path is not returned.
Yeah, I agree. I've implemented the first version in a way it doesn't
show the real value of the $system macro because of security reasons but
I agree that we don't want that any user can see the configured path of
custom extensions too. I would prefer to go with the '<insufficient privilege>' 
since it's something that we already have today in other views and users
may already know how to deal with it.

--
Matheus Alcantara

---

From: Quan Zongliang <[email-masked]>
Date: Fri, 24 Oct 2025 09:57:51 +0800

>    the absolute path is not returned.

+1
I think this way is better.

---

From: "Matheus Alcantara" <[email-masked]>
Date: Tue, 28 Oct 2025 09:29:13 -0300

> I think this way is better.
So here it is, see attached.

I've created a new role pg_read_extension_paths for this, I'm not sure
if it's the best way to do this. I'm open for other ideas, perhaps we
can reuse some other role?

--
Matheus Alcantara
From b398d76e53164263f93cbc6649ea7a79f6dc16b6 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email-masked]>
Date: Mon, 15 Sep 2025 15:46:24 -0300
Subject: [PATCH v3] Add path of extension on pg_available_extensions

This adds a new "location" column on pg_available_extensions and
pg_available_extension_versions views to show the path of locations that
Postgres is seeing based on the extension_control_path GUC.

The default system location is show as $system macro, the same value
that is used to configure the extension_control_path GUC.

User configured paths is only visible for users that has the
pg_read_extension_paths role, otherwise <insufficient privilege> is
returned as a column value, the same behaviour that we already have on
pg_stat_activity.
---
 src/backend/catalog/system_views.sql          |   4 +-
 src/backend/commands/extension.c              | 103 ++++++++++++++----
 src/include/catalog/pg_authid.dat             |   5 +
 src/include/catalog/pg_proc.dat               |  10 +-
 .../t/001_extension_control_path.pl           |  48 +++++++-
 src/test/regress/expected/rules.out           |  10 +-
 6 files changed, 146 insertions(+), 34 deletions(-)

 
index 93ef1ad106f..c7f3568190c 100644
 
+/*
@@ -157,6 +172,34 @@ static ExtensionControlFile *new_ExtensionControlFile(const char *extname);

+/*
+ *  Return the location to display for the given Location based on the user
+ *  privileges. If the user connected to the database don't have the
+ *  permissions <insufficient privilege> is returned.
+ */
+static char *
+location_to_display(Location *loc)
+{
+	Oid			userid = GetUserId();
+	bool		is_allowed_role;
+
+	is_allowed_role = has_privs_of_role(userid, ROLE_PG_READ_EXTENSION_PATHS);
+
+	if (is_allowed_role)
+	{
+		/*
+		 * Return the macro value if it's present to don't show system paths.
+		 * We only want to show user custom paths if it has the privilege.
+		 */
+		if (loc->macro == NULL)
+			return loc->loc;
+		else
+			return loc->macro;
+	}
+	else
+		return "<insufficient privilege>";
+}
+
 /*
  * get_extension_oid - given an extension name, look up the OID
  *
@@ -354,7 +397,11 @@ get_extension_control_directories(void)
 
@@ -366,6 +413,7 @@ get_extension_control_directories(void)
 
@@ -382,15 +430,21 @@ get_extension_control_directories(void)

 			/* Break if ecp is empty or move to the next path on ecp */
 			if (ecp[len] == '\0')
@@ -2215,9 +2269,9 @@ pg_available_extensions(PG_FUNCTION_ARGS)

 
 		/*
@@ -2229,13 +2283,13 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 
 				if (!is_extension_control_filename(de->d_name))
 					continue;
@@ -2259,7 +2313,7 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 					found_ext = lappend(found_ext, extname_str);

 				memset(values, 0, sizeof(values));
@@ -2279,6 +2333,9 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 				else
 					values[2] = CStringGetTextDatum(control->comment);
 
+				/* location */
+				values[3] = CStringGetTextDatum(location_to_display(location));
+
 				tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
 									 values, nulls);
 			}
@@ -2313,9 +2370,9 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)

 
 		/*
 		 * If the control directory doesn't exist, we want to silently return
@@ -2327,7 +2384,7 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 		}
 		else
 		{
-			while ((de = ReadDir(dir, location)) != NULL)
+			while ((de = ReadDir(dir, location->loc)) != NULL)
 			{
 				ExtensionControlFile *control;
 				char	   *extname;
@@ -2356,12 +2413,13 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)

 
 			FreeDir(dir);
@@ -2378,7 +2436,8 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 static void
 get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 									 Tuplestorestate *tupstore,
-									 TupleDesc tupdesc)
+									 TupleDesc tupdesc,
+									 Location *location)
 {
 	List	   *evi_list;
 	ListCell   *lc;
@@ -2391,8 +2450,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 	{
 		ExtensionVersionInfo *evi = (ExtensionVersionInfo *) lfirst(lc);
 		ExtensionControlFile *control;
-		Datum		values[8];
-		bool		nulls[8];
+		Datum		values[9];
+		bool		nulls[9];
 		ListCell   *lc2;
 
 		if (!evi->installable)
@@ -2434,6 +2493,9 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 		else
 			values[7] = CStringGetTextDatum(control->comment);
 
+		/* location */
+		values[8] = CStringGetTextDatum(location_to_display(location));
+
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 
 		/*
@@ -2475,6 +2537,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 				}
 				/* comment stays the same */
 
+				/* location stays the same */
+
 				tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 			}
 		}
@@ -3903,7 +3967,8 @@ find_in_paths(const char *basename, List *paths)
 
 	foreach(cell, paths)
 	{
 
 		Assert(path != NULL);
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index c881c13adf1..af18eb8c1c9 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -104,5 +104,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '6434', oid_symbol => 'ROLE_PG_READ_EXTENSION_PATHS',
+  rolname => 'pg_read_extension_paths', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
index 7fbe5bde332..1c70a27cffa 100644
 
@@ -35,6 +39,10 @@ extension_control_path = '\$system$sep@{[ $windows_os ? ($ext_dir =~ s/\\/\\\\/g
 # Start node
 $node->start;
 
+# Create an user to test permissions to read extension locations.
+my $user = "user01";
+$node->safe_psql('postgres', "CREATE USER $user");
+
 my $ecp = $node->safe_psql('postgres', 'show extension_control_path;');
 
 is($ecp, "\$system$sep$ext_dir$sep$ext_dir2",
@@ -43,31 +51,63 @@ is($ecp, "\$system$sep$ext_dir$sep$ext_dir2",
 $node->safe_psql('postgres', "CREATE EXTENSION $ext_name");
 $node->safe_psql('postgres', "CREATE EXTENSION $ext_name2");
 
+

 
 
+# Test insufficient roles to read extension locations
+$ret = $node->safe_psql('postgres',
+	"select location from pg_available_extensions where name = '$ext_name2'",
+	connstr => "user=$user");
+is( $ret,
+	"<insufficient privilege>",
+	"extension location is hide on pg_available_extensions for insufficient privilege");
+
+$ret = $node->safe_psql('postgres',
+	"select location from pg_available_extension_versions where name = '$ext_name2'",
+	connstr => "user=$user");
+is( $ret,
+	"<insufficient privilege>",
+	"extension location is hide on pg_available_extension_versions for insufficient privilege");
+
+# Now give the access and test it again
+$node->safe_psql('postgres', "GRANT pg_read_extension_paths TO $user");
+
+$ret = $node->safe_psql('postgres',
+	"select location from pg_available_extensions where name = '$ext_name2'");
+is( $ret,
+	"$ext_dir_canonicalized/extension",
+	"extension location is not hide on pg_available_extensions with sufficient privilege");
+
+$ret = $node->safe_psql('postgres',
+	"select location from pg_available_extension_versions where name = '$ext_name2'"
+);
+is( $ret,
+	"$ext_dir_canonicalized/extension",
+	"extension location is not hide on pg_available_extension_versions with sufficient privilege");
+
 # Ensure that extensions installed on $system is still visible when using with
 # custom extension control path.
 $ret = $node->safe_psql('postgres',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 16753b2e4c0..85da655b893 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1310,14 +1310,16 @@ pg_available_extension_versions| SELECT e.name,
     e.relocatable,
     e.schema,
     e.requires,
-    e.comment
-   FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment)
+    e.comment,
+    e.location
+   FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment, location)
      LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
 pg_available_extensions| SELECT e.name,
     e.default_version,
     x.extversion AS installed_version,
-    e.comment
-   FROM (pg_available_extensions() e(name, default_version, comment)
+    e.comment,
+    e.location
+   FROM (pg_available_extensions() e(name, default_version, comment, location)
      LEFT JOIN pg_extension x ON ((e.name = x.extname)));
 pg_backend_memory_contexts| SELECT name,
     ident,
-- 
2.51.0

---

From: "Euler Taveira" <[email-masked]>
Date: Tue, 28 Oct 2025 17:56:18 -0300

> So here it is, see attached.

I took another look at this patch.

! This adds a new "location" column on pg_available_extensions and
! pg_available_extension_versions views to show the path of locations that
! Postgres is seeing based on the extension_control_path GUC.

Maybe the sentence above can be written in a different way such as

Add a new "location" column to pg_available_extensions and
pg_available_extension_versions views. It exposes the directory that the
extension is located.

! User configured paths is only visible for users that has the
! pg_read_extension_paths role, otherwise <insufficient privilege> is
! returned as a column value, the same behaviour that we already have on
! pg_stat_activity.

s/User configured paths is/User-defined locations are/

+typedef struct
+{
+	char	   *macro;
+	char	   *loc;
+} Location;

Location is a generic name. I would use something like ExtensionLocation or
ExtLocation or ExtControlPath. Do you really need a struct here? You are
storing the same element (location) in both members. Couldn't you use a single
string to represent the location (with and without the macro)?

+/*

Could you improve this comment?

Return the extension location. If the current user doesn't have sufficient
privileges, don't show the location. You could also rename this function
(something like get_extension_location). Since has_privs_of_role returns a
bool, you can simplify the code and call it directly into the if block. You can
also use GetUserId() directly instead of declaring another variable.

I'm not convinced that we need a new predefined role just to control if it can
expose the extension location. Should it return the location only for
superusers? Can't one of the current predefined roles be used? If it doesn't
fit, you should probably use a generic name so this new predefined role can be
used by other extension-related stuff in the future.

+

Remove this new line.

Adding more tests is always a good thing. However, in this case, we can
simplify the tests. The current queries already cover the
get-the-extension-location case. If you add an additional test showing the
insufficient privilege case, that's fine. The other tests are basically
exercising the permission system.

Documentation is missing. These views are documented in system-views.sgml.

---

From: "Matheus Alcantara" <[email-masked]>
Date: Wed, 29 Oct 2025 11:24:55 -0300

> I took another look at this patch.
Thanks for reviewing!

> pg_available_extension_versions views. It exposes the directory that the
Sounds better, fixed.

> s/User configured paths is/User-defined locations are/
Fixed.

> string to represent the location (with and without the macro)?
ExtensionLocation sounds good, fixed for this.

I think that the struct is necessary because we use the "loc" field for
other things other than just printing on "location" column results. For
instance, on pg_available_extension_versions() we get the list of
ExtensionLocation's and use the "loc" field to call AllocateDir() and
ReadDir() and then we pass the location pointer to
get_available_versions_for_extension() that it will decide if it should
use the "loc" or the "macro" field by calling the location_to_display().
So I don't think that we can use a single string to represent the
location because we may use the raw location or the macro value
depending on the case.

> Could you improve this comment?
Fixed.

> also use GetUserId() directly instead of declaring another variable.
Fixed.

> used by other extension-related stuff in the future.
Yeah, I think that we can limit this only for superusers. Fixed.

>  	"select * from pg_available_extensions where name = '$ext_name'");
Fixed.

> exercising the permission system.
Fixed.

Fixed

--
Matheus Alcantara

From fa6560a81c746ea28c12256fcbe0372850385332 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email-masked]>
Date: Mon, 15 Sep 2025 15:46:24 -0300
Subject: [PATCH v4] Add path of extension on pg_available_extensions

User-defined locations are only visible for users that has the
pg_read_extension_paths role, otherwise <insufficient privilege> is
returned as a column value, the same behaviour that we already have on
pg_stat_activity.
---
 doc/src/sgml/system-views.sgml                | 22 +++++
 src/backend/catalog/system_views.sql          |  4 +-
 src/backend/commands/extension.c              | 95 +++++++++++++++----
 src/include/catalog/pg_proc.dat               | 10 +-
 .../t/001_extension_control_path.pl           | 35 ++++++-
 src/test/regress/expected/rules.out           | 10 +-
 src/tools/pgindent/typedefs.list              |  2 +-
 7 files changed, 142 insertions(+), 36 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 7971498fe75..aef0f067436 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -607,6 +607,17 @@
        Comment string from the extension's control file
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>location</structfield> <type>text</type>
+      </para>
+      <para>
+       The location where the extension was found based on the <link
+       linkend="guc-extension-control-path"><structname>extension_control_path</structname></link>
+       GUC. Only superusers can see the contents of this column.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
@@ -731,6 +742,17 @@
        Comment string from the extension's control file
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>location</structfield> <type>text</type>
+      </para>
+      <para>
+       The location where the extension was found based on the <link
+       linkend="guc-extension-control-path"><structname>extension_control_path</structname></link>
+       GUC. Only superusers can see the contents of this column.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index dec8df4f8ee..8a084927954 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -412,14 +412,14 @@ CREATE VIEW pg_cursors AS

index 93ef1ad106f..e64a681790e 100644
 
+/*
+} ExtensionLocation;
+												 ExtensionLocation *location);
@@ -157,6 +172,26 @@ static ExtensionControlFile *new_ExtensionControlFile(const char *extname);

+/*
+ *  Return the extension location. If the current user doesn't have sufficient
+ *  privileges, don't show the location.
+ */
+static char *
+get_extension_location(ExtensionLocation *loc)
+{
+	/* We only want to show extension paths for superusers. */
+	if (superuser_arg(GetUserId()))
+	{
+		/* Return the macro value if it's present to don't show system paths. */
+		if (loc->macro == NULL)
+			return loc->loc;
+		else
+			return loc->macro;
+	}
+	else
+		return "<insufficient privilege>";
+}
+
 /*
  * get_extension_oid - given an extension name, look up the OID
  *
@@ -354,7 +389,11 @@ get_extension_control_directories(void)
 
 	if (strlen(Extension_control_path) == 0)
 	{
-		paths = lappend(paths, system_dir);
+		ExtensionLocation *location = palloc_object(ExtensionLocation);
+
+		location->macro = NULL;
+		location->loc = system_dir;
+		paths = lappend(paths, location);
 	}
 	else
 	{
@@ -366,6 +405,7 @@ get_extension_control_directories(void)
 			int			len;
 			char	   *mangled;
 			char	   *piece = first_path_var_separator(ecp);
+			ExtensionLocation *location = palloc_object(ExtensionLocation);
 
@@ -382,15 +422,21 @@ get_extension_control_directories(void)

 			/* Break if ecp is empty or move to the next path on ecp */
 			if (ecp[len] == '\0')
@@ -2215,9 +2261,9 @@ pg_available_extensions(PG_FUNCTION_ARGS)

-	foreach_ptr(char, location, locations)
+	foreach_ptr(ExtensionLocation, location, locations)
 	{
-		dir = AllocateDir(location);
+		dir = AllocateDir(location->loc);
 
 		/*
@@ -2229,13 +2275,13 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 
 				if (!is_extension_control_filename(de->d_name))
 					continue;
@@ -2259,7 +2305,7 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 					found_ext = lappend(found_ext, extname_str);

 				memset(values, 0, sizeof(values));
@@ -2279,6 +2325,9 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 				else
 					values[2] = CStringGetTextDatum(control->comment);
 
+				/* location */
+				values[3] = CStringGetTextDatum(get_extension_location(location));
+
 				tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
 									 values, nulls);
 			}
@@ -2313,9 +2362,9 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)

-	foreach_ptr(char, location, locations)
+	foreach_ptr(ExtensionLocation, location, locations)
 	{
-		dir = AllocateDir(location);
+		dir = AllocateDir(location->loc);
 
 		/*
 		 * If the control directory doesn't exist, we want to silently return
@@ -2327,7 +2376,7 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 		}
 		else
 		{
-			while ((de = ReadDir(dir, location)) != NULL)
+			while ((de = ReadDir(dir, location->loc)) != NULL)
 			{
 				ExtensionControlFile *control;
 				char	   *extname;
@@ -2356,12 +2405,13 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)

 
 			FreeDir(dir);
@@ -2378,7 +2428,8 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 static void
 get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 									 Tuplestorestate *tupstore,
-									 TupleDesc tupdesc)
+									 TupleDesc tupdesc,
+									 ExtensionLocation *location)
 {
 	List	   *evi_list;
 	ListCell   *lc;
@@ -2391,8 +2442,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 	{
 		ExtensionVersionInfo *evi = (ExtensionVersionInfo *) lfirst(lc);
 		ExtensionControlFile *control;
-		Datum		values[8];
-		bool		nulls[8];
+		Datum		values[9];
+		bool		nulls[9];
 		ListCell   *lc2;
 
 		if (!evi->installable)
@@ -2434,6 +2485,9 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 		else
 			values[7] = CStringGetTextDatum(control->comment);
 
+		/* location */
+		values[8] = CStringGetTextDatum(get_extension_location(location));
+
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 
 		/*
@@ -2475,6 +2529,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 				}
 				/* comment stays the same */
 
+				/* location stays the same */
+
 				tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 			}
 		}
@@ -3903,7 +3959,8 @@ find_in_paths(const char *basename, List *paths)
 
 	foreach(cell, paths)
 	{
-		char	   *path = lfirst(cell);
+		ExtensionLocation *location = lfirst(cell);
+		char	   *path = location->loc;
 		char	   *full;
 
 		Assert(path != NULL);
index 9121a382f76..86d0509f2c7 100644
index 7fbe5bde332..498d96969f0 100644

 
@@ -46,28 +54,46 @@ $node->safe_psql('postgres', "CREATE EXTENSION $ext_name2");

 
 
+# Test that a non-superuser can not read the extension location on
+# pg_available_extensions
+$ret = $node->safe_psql('postgres',
+	"select location from pg_available_extensions where name = '$ext_name2'",
+	connstr => "user=$user");
+is( $ret,
+	"<insufficient privilege>",
+	"extension location is hide on pg_available_extensions for insufficient privilege");
+
+# Test that a non-superuser can not read the extension location on
+# pg_available_extension_versions
+$ret = $node->safe_psql('postgres',
+	"select location from pg_available_extension_versions where name = '$ext_name2'",
+	connstr => "user=$user");
+is( $ret,
+	"<insufficient privilege>",
+	"extension location is hide on pg_available_extension_versions for insufficient privilege");
+
 # Ensure that extensions installed on $system is still visible when using with
 # custom extension control path.
 $ret = $node->safe_psql('postgres',
@@ -76,7 +102,6 @@ $ret = $node->safe_psql('postgres',
 is($ret, "t",
 	"\$system extension is installed correctly on pg_available_extensions");
 
-
 $ret = $node->safe_psql('postgres',
 	"set extension_control_path = ''; select count(*) > 0 as ok from pg_available_extensions where name = 'plpgsql'"
 );
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 77e25ca029e..383ac569c43 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1310,14 +1310,16 @@ pg_available_extension_versions| SELECT e.name,
     e.relocatable,
     e.schema,
     e.requires,
-    e.comment
-   FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment)
+    e.comment,
+    e.location
+   FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment, location)
      LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
 pg_available_extensions| SELECT e.name,
     e.default_version,
     x.extversion AS installed_version,
-    e.comment
-   FROM (pg_available_extensions() e(name, default_version, comment)
+    e.comment,
+    e.location
+   FROM (pg_available_extensions() e(name, default_version, comment, location)
      LEFT JOIN pg_extension x ON ((e.name = x.extname)));
 pg_backend_memory_contexts| SELECT name,
     ident,
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ac2da4c98cf..a6e39ade3d2 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -789,6 +789,7 @@ ExtensibleNodeEntry
 ExtensibleNodeMethods
 ExtensionControlFile
 ExtensionInfo
+ExtensionLocation
 ExtensionVersionInfo
 FDWCollateState
 FD_SET
@@ -1568,7 +1569,6 @@ LoadStmt
 LocalBufferLookupEnt
 LocalPgBackendStatus
 LocalTransactionId
-Location
 LocationIndex
 LocationLen
 LockAcquireResult
-- 
2.51.0

---

From: Michael Banck <[email-masked]>
Date: Sun, 2 Nov 2025 16:11:43 +0100

Hi,

>   name   | default_version | installed_version |                     comment                      |                  location

I am not sure just adding the column at the end is best, I would have
put it before comment so that stays last, maybe somebody else has some
bikeshedding input here?

Michae

---

From: "Matheus Alcantara" <[email-masked]>
Date: Thu, 06 Nov 2025 12:29:14 -0300

> put it before comment so that stays last, maybe somebody else has some
Yeah, I think that it looks better to keep the comment at the end. If no
objections I'll swap the order of "comment" and "location" columns on
the next version.

-- 
Matheus Alcantara
EDB: http://www.enterprisedb.com

---

From: Manni Wood <[email-masked]>
Date: Mon, 10 Nov 2025 12:25:31 -0600

On Thu, Nov 6, 2025 at 9:29 AM Matheus Alcantara <[email-masked]>
wrote:

> >>  envvar  | 1.0.0           |                   | Get the value of a
> server environment variable   |
> /usr/local/my/extensions/share/postgresql/extension
Hello!

I have a small bikeshedding comment around making "location" the 4th column
returned for "select * from pg_available_extensions", as opposed to leaving
"comment" the 4th column returned for "select * from
pg_available_extensions".

If a bit of software runs "select * from pg_available_extensions" and
fetches the contents of the 4th column, that column will return "comment"
for current versions of postgres but "location" for patched versions of
postgres.

In many ways, this could be considered a feature and not a bug, because we
should be encouraged to write our SQL like so:

select name, default_version, installed_version, comment from
pg_available_extensions

and not like so:

select * from pg_available_extensions

I'm curious to know if this is a legitimate consideration or not.

Also, there were no surprises when I compiled and tested this: the location
shows correctly for a superuser, and "<insufficient privilege>" shows
correctly for a non-superuser.
-- 
-- Manni Wood EDB: https://www.enterprisedb.com

---

From: "Matheus Alcantara" <[email-masked]>
Date: Mon, 10 Nov 2025 19:48:03 -0300

Thank you for reviewing this!

> correctly for a non-superuser.
Good point, I think that it's a legitimate consideration. That being
said I would get back to prefer to keep the location as the latest
column to avoid such issues even if SELECT * is not something that users
should do in practice, but I think that it's worth to avoid break any
application with such change.

---

From: Michael Banck <[email-masked]>
Date: Tue, 11 Nov 2025 00:10:45 +0100

Hi,

> application with such change.

When the trusted column got added to the pg_availe_extensions view in
50fc694, it wasn't added to the end, but next to superuser, where it
logically makes sense IMO:

|@@ -317,7 +317,8 @@ CREATE VIEW pg_available_extensions AS
| 
| CREATE VIEW pg_available_extension_versions AS
|     SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed,
|-           E.superuser, E.relocatable, E.schema, E.requires, E.comment
|+           E.superuser, E.trusted, E.relocatable,
|+           E.schema, E.requires, E.comment
|       FROM pg_available_extension_versions() AS E
|            LEFT JOIN pg_extension AS X
|              ON E.name = X.extname AND E.version = X.extversion;

As far as I know, Postgres does not guarantee stable system catalogs
between major versions, so I don't think users should or could rely on
stable column ordering, really.

Michael

---

From: Rohit Prasad <[email-masked]>
Date: Tue, 11 Nov 2025 02:06:14 +0000

Hi Michael,

I am just getting started in the Postgres community (this is my first code review). So please excuse me if I have missed something (in terms of process etc).
I reviewed your proposed code changes in the attached patch file and they look good to me. I have some minor comments:

1. In src/test/modules/test_extensions/t/001_extension_control_path.pl, it would be nice if you could add a test that validates that the correct Extension location is displayed, if for example, the extension is being picked up from a customized location. 
2. Nit-pick: In src/backend/commands/extension.c:get_available_versions_for_extension(), you could probably combine the following 2 lines into one to say "'comment' & 'location' stay the same.
                    /* comment stays the same */                                                          
                   /* location stays the same */                                                                                                                                                                  

Hope this is helpful.

Thanks,
-Rohit

---

From: "Matheus Alcantara" <[email-masked]>
Date: Tue, 11 Nov 2025 08:24:56 -0300

> stable column ordering, really.
Thanks for pointing this, I didn't know about this detail. I'm not
against swapping the orders of "comment" and "location" columns, I also
think that it would look better, I'm just afraid of breaking any
compatibility with anything, but it seems that it's not the case.

---

From: "Matheus Alcantara" <[email-masked]>
Date: Tue, 11 Nov 2025 09:47:02 -0300

> Hi Michael,
I think you wanted to say Matheus :)

> I am just getting started in the Postgres community (this is my first
> code review). So please excuse me if I have missed something (in terms
> of process etc).
>

> I reviewed your proposed code changes in the attached patch file and
> they look good to me. 
>
Thanks.

> I have some minor comments:
> 1. In src/test/modules/test_extensions/t/001_extension_control_path.pl, 
> it would be nice if you could add a test that validates that the
> correct Extension location is displayed, if for example, the extension
> is being picked up from a customized location. 
>
I don't know if I get your point here. On the v4 patch we have:
-       "test_custom_ext_paths|1.0|1.0|Test extension_control_path",
+       "test_custom_ext_paths|1.0|1.0|Test extension_control_path|$ext_dir_canonicalized/extension",

The $ext_dir_canonicalized in this case is a custom path configured on
extension_control_path GUC that the "test_custom_ext_paths" extension
was installed.

> 2. Nit-pick: In
> src/backend/commands/extension.c:get_available_versions_for_extension(),
> you could probably combine the following 2 lines into one to say
> "'comment' & 'location' stay the same.
Fixed on attached v5

On this new v5 version I also swap the order of "comment" and "location"
columns as it was suggested by Michael.

From 0ccd9aa0d8a5e4ba9168b16f14e8c617f023b4c1 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email-masked]>
Date: Mon, 15 Sep 2025 15:46:24 -0300
Subject: [PATCH v5] Add path of extension on pg_available_extensions

User-defined locations are only visible for users that has the
pg_read_extension_paths role, otherwise <insufficient privilege> is
returned as a column value, the same behaviour that we already have on
pg_stat_activity.
---
 doc/src/sgml/system-views.sgml                |  22 ++++
 src/backend/catalog/system_views.sql          |   4 +-
 src/backend/commands/extension.c              | 105 ++++++++++++++----
 src/include/catalog/pg_proc.dat               |  10 +-
 .../t/001_extension_control_path.pl           |  35 +++++-
 src/test/regress/expected/rules.out           |   6 +-
 src/tools/pgindent/typedefs.list              |   2 +-
 7 files changed, 145 insertions(+), 39 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 7971498fe75..69b30bb6453 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -599,6 +599,17 @@
       </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>location</structfield> <type>text</type>
+      </para>
+      <para>
+       The location where the extension was found based on the <link
+       linkend="guc-extension-control-path"><structname>extension_control_path</structname></link>
+       GUC. Only superusers can see the contents of this column.
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>comment</structfield> <type>text</type>
@@ -723,6 +734,17 @@
       </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>location</structfield> <type>text</type>
+      </para>
+      <para>
+       The location where the extension was found based on the <link
+       linkend="guc-extension-control-path"><structname>extension_control_path</structname></link>
+       GUC. Only superusers can see the contents of this column.
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>comment</structfield> <type>text</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 059e8778ca7..52fa2ddcaa3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -412,14 +412,14 @@ CREATE VIEW pg_cursors AS
 
+           E.location, E.comment
 
+           E.schema, E.requires, E.location, E.comment
index 93ef1ad106f..9868f3684c6 100644
 
+/*

+/*

 

 
 
 		/*

 
 				memset(values, 0, sizeof(values));
@@ -2273,11 +2319,15 @@ pg_available_extensions(PG_FUNCTION_ARGS)
 					nulls[1] = true;
 				else
 					values[1] = CStringGetTextDatum(control->default_version);
+
+				/* location */
+				values[2] = CStringGetTextDatum(get_extension_location(location));
+
 				/* comment */
 				if (control->comment == NULL)
-					nulls[2] = true;
+					nulls[3] = true;
 				else
-					values[2] = CStringGetTextDatum(control->comment);
+					values[3] = CStringGetTextDatum(control->comment);
 
 				tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
 									 values, nulls);
@@ -2313,9 +2363,9 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)

 
 		/*
 		 * If the control directory doesn't exist, we want to silently return
@@ -2327,7 +2377,7 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 		}
 		else
 		{
-			while ((de = ReadDir(dir, location)) != NULL)
+			while ((de = ReadDir(dir, location->loc)) != NULL)
 			{
 				ExtensionControlFile *control;
 				char	   *extname;
@@ -2356,12 +2406,13 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)

 
 			FreeDir(dir);
@@ -2378,7 +2429,8 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 static void
 get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 									 Tuplestorestate *tupstore,
-									 TupleDesc tupdesc)
+									 TupleDesc tupdesc,
+									 ExtensionLocation *location)
 {
 	List	   *evi_list;
 	ListCell   *lc;
@@ -2391,8 +2443,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 	{
 		ExtensionVersionInfo *evi = (ExtensionVersionInfo *) lfirst(lc);
 		ExtensionControlFile *control;
-		Datum		values[8];
-		bool		nulls[8];
+		Datum		values[9];
+		bool		nulls[9];
 		ListCell   *lc2;
 
 		if (!evi->installable)
@@ -2428,11 +2480,15 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 			nulls[6] = true;
 		else
 			values[6] = convert_requires_to_datum(control->requires);
+
+		/* location */
+		values[7] = CStringGetTextDatum(get_extension_location(location));
+
 		/* comment */
 		if (control->comment == NULL)
-			nulls[7] = true;
+			nulls[8] = true;
 		else
-			values[7] = CStringGetTextDatum(control->comment);
+			values[8] = CStringGetTextDatum(control->comment);

@@ -2473,7 +2529,7 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 					values[6] = convert_requires_to_datum(control->requires);
 					nulls[6] = false;
 				}
-				/* comment stays the same */
+				/* comment and location stays the same */

 	foreach(cell, paths)
 	{
 
 		Assert(path != NULL);
index 5cf9e12fcb9..e34da1357e1 100644
+  proargmodes => '{o,o,o,o}', proargnames => '{name,default_version,location,comment}',
+  proargnames => '{name,version,superuser,trusted,relocatable,schema,requires,location,comment}',
index 7fbe5bde332..11bfb77149d 100644

 
+	"test_custom_ext_paths|1.0|1.0|$ext_dir_canonicalized/extension|Test extension_control_path",
 
+	"test_custom_ext_paths|1.0|t|t|f|t|||$ext_dir_canonicalized/extension|Test extension_control_path",
 
+	"test_custom_ext_paths_using_directory|1.0|1.0|$ext_dir_canonicalized/extension|Test extension_control_path",
 
+	"test_custom_ext_paths_using_directory|1.0|t|t|f|t|||$ext_dir_canonicalized/extension|Test extension_control_path",

-
 $ret = $node->safe_psql('postgres',
 	"set extension_control_path = ''; select count(*) > 0 as ok from pg_available_extensions where name = 'plpgsql'"
 );
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7c52181cbcb..b402dc987ac 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1310,14 +1310,16 @@ pg_available_extension_versions| SELECT e.name,
     e.relocatable,
     e.schema,
     e.requires,
+    e.location,
     e.comment
-   FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment)
+   FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, location, comment)
      LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
 pg_available_extensions| SELECT e.name,
     e.default_version,
     x.extversion AS installed_version,
+    e.location,
     e.comment
-   FROM (pg_available_extensions() e(name, default_version, comment)
+   FROM (pg_available_extensions() e(name, default_version, location, comment)
      LEFT JOIN pg_extension x ON ((e.name = x.extname)));
 pg_backend_memory_contexts| SELECT name,
     ident,
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 432509277c9..71dbf756a55 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -790,6 +790,7 @@ ExtensibleNodeEntry
 ExtensibleNodeMethods
 ExtensionControlFile
 ExtensionInfo
+ExtensionLocation
 ExtensionVersionInfo
 FDWCollateState
 FD_SET
@@ -1570,7 +1571,6 @@ LoadStmt
 LocalBufferLookupEnt
 LocalPgBackendStatus
 LocalTransactionId
-Location
 LocationIndex
 LocationLen
 LockAcquireResult
-- 
2.51.2

---

From: Rohit Prasad <[email-masked]>
Date: Tue, 11 Nov 2025 23:17:05 +0000

Hi Matheus,

Apologies for the mix up on the names :-)

I agree with your responses (please ignore my prior comment about test_extension code; I had missed some of your changes there). 

I also reviewed v5 of your patch and the changes look good to me.

Thanks,
-Rohit

---

From: "Matheus Alcantara" <[email-masked]>
Date: Wed, 12 Nov 2025 10:26:50 -0300

> I also reviewed v5 of your patch and the changes look good to me.
No problem and thank you for reviewing!

---

From: Chao Li <[email-masked]>
Date: Thu, 13 Nov 2025 17:11:44 +0800

Hi Matheus,

I just reviewed and tested the patch again, got a few more comments:

> On Nov 11, 2025, at 20:47, Matheus Alcantara <[email-masked]> wrote:
> 
> 
> <v5-0001-Add-path-of-extension-on-pg_available_extensions.patch>

1 - commit comment
```

First, there is a typo: "for users that has” should be “have”.

Then, Is this still true? The code change shows:
```
+	/* We only want to show extension paths for superusers. */
+	if (superuser_arg(GetUserId()))
+	{
```

And the code change says:
```
+       GUC. Only superusers can see the contents of this column.
```

But the TAP test contains a case for normal user:
```
+# Create an user to test permissions to read extension locations.
+my $user = "user01";
+$node->safe_psql('postgres', "CREATE USER $user");
+
 my $ecp = $node->safe_psql('postgres', 'show extension_control_path;');
 
 	"extension is installed correctly on pg_available_extensions”);
```

And I tried to run the TAP test, it passed.

So I’m really confused here.

2
```
+	else

Why do we just show nothing (empty string)? Every row showing a long string of <insufficient privilege> just looks not good, that’s a lot of duplications.

```
evantest=> select * from pg_available_extension_versions;
        name        | version | installed | superuser | trusted | relocatable |   schema   |      requires       |         location         |                                comment
--------------------+---------+-----------+-----------+---------+-------------+------------+---------------------+--------------------------+------------------------------------------------------------------------
 refint             | 1.0     | f         | t         | f       | t           |            |                     | <insufficient privilege> | functions for implementing referential integrity (obsolete)
 unaccent           | 1.1     | f         | t         | t       | t           |            |                     | <insufficient privilege> | text search dictionary that removes accents
```

Best regards,

---

From: "Matheus Alcantara" <[email-masked]>
Date: Thu, 13 Nov 2025 11:36:09 -0300

> So I’m really confused here.
Oops, I forgot to update the commit message. The extension location is
only visible for super users. Fixed on v6.

The created user on TAP test is used to check that non superusers can not
see the location column content. The other test case changes are using
the 'postgres' user, so it should see the extension location value.

> Why do we just show nothing (empty string)? Every row showing a long string of <insufficient privilege> just looks not good, that’s a lot of duplications.
This is the same behavior of pg_stat_activity. Returning an empty string
could make the user think that something is not right with the
implementation. Returning <insufficient privilege> for every row is a
lot of duplication I agree but I think that it make clear for the user
that it don't have the necessary role to view the column value.

Thanks for reviewing!

From de163ebf950d9fe0152cc082a4bf26acc261aa2e Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email-masked]>
Date: Mon, 15 Sep 2025 15:46:24 -0300
Subject: [PATCH v6] Add path of extension on pg_available_extensions

User-defined locations are only visible for super users, otherwise
<insufficient privilege> is returned as a column value, the same
behaviour that we already have on pg_stat_activity.
---
 doc/src/sgml/system-views.sgml                |  22 ++++
 src/backend/catalog/system_views.sql          |   4 +-
 src/backend/commands/extension.c              | 105 ++++++++++++++----
 src/include/catalog/pg_proc.dat               |  10 +-
 .../t/001_extension_control_path.pl           |  35 +++++-
 src/test/regress/expected/rules.out           |   6 +-
 src/tools/pgindent/typedefs.list              |   2 +-
 7 files changed, 145 insertions(+), 39 deletions(-)

+     <row>
 
+     <row>

 
+/*

+/*

 

 
 
 		/*

 

 
 
 		/*

 
 			FreeDir(dir);
 
 		if (!evi->installable)

 
 
 	foreach(cell, paths)
 	{
 
 		Assert(path != NULL);

 

 

-
index 23bce72ae64..d742dcc8335 100644