[MASSMAIL]RFC: Additional Directory for Extensions

Hackers,

In the Security lessons from liblzma thread[1], walther broached the subject of an extension directory path[1]:

> Also a configurable directoy to look up extensions, possibly even to be
> changed at run-time like [2]. The patch says this:
>
>> This directory is prepended to paths when loading extensions (control and SQL files), and to the '$libdir' directive
whenloading modules that back functions. The location is made configurable to allow build-time testing of extensions
thatdo not have been installed to their proper location yet. 
>
> This seems like a great thing to have. This might also be relevant in
> light of recent discussions in the ecosystem around extension management.


That quotation comes from this Debian patch[2] maintained by Christoph Berg. I’d like to formally propose integrating
thispatch into the core. And not only because it’s overhead for package maintainers like Christoph, but because a
numberof use cases have emerged since we originally discussed something like this back in 2013[3]: 

Docker Immutability
-------------------

Docker images must be immutable. In order for users of a Docker image to install extensions that persist, they must
createa persistent volume, map it to SHAREDIR/extensions, and copy over all the core extensions (or muck with symlink
magic[4]).This makes upgrades trickier, because the core extensions are mixed in with third party extensions.  

By supporting a second directory pretended to the list of directories to search, as the Debian patch does, users of
Dockerimages can keep extensions they install separate from core extensions, in a directory mounted to a persistent
volumewith none of the core extensions. Along with tweaking dynamic_library_path to support additional directories for
sharedobject libraries, which can also be mounted to a separate path, we can have a persistent and clean separation of
immutablecore extensions and extensions installed at runtime. 

Postgres.app
------------

The Postgres.app project also supports installing extensions. However, because they must go into the
SHAREDIR/extensions,once a user installs one the package has been modified and the Apple bundle signature will be
broken.The OS will no longer be able to validate that the app is legit. 

If the core supported an additional extension (and PKGLIBDIR), it would allow an immutable PostgreSQL base package and
stillallow extensions to be installed into directories outside the app bundle, and thus preserve bundle signing on
macOS(and presumably other systems --- is this the nix issue, too?) 

RFC
---

I know there was some objection to changes like this in the past, but the support I’m seeing in the liblzma thread for
makingpkglibdir configurable  me optimistic that this might be the right time to support additional configuration for
theextension directory, as well, starting with the Debian patch, perhaps. 

Thoughts?

I would be happy to submit a clean version of the Debian patch[2].

Best,

David

[1] https://www.postgresql.org/message-id/99c41b46-616e-49d0-9ffd-a29432cec818%40technowledgy.de
[2] https://salsa.debian.org/postgresql/postgresql/-/blob/17/debian/patches/extension_destdir?ref_type=heads
[3] https://www.postgresql.org/message-id/flat/51AE0845.8010600@ocharles.org.uk
[4] https://speakerdeck.com/ongres/postgres-extensions-in-kubernetes?slide=14