Feedback-ID: i7c334307:Fastmail
Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3818.100.11.1.3\))
Subject: Re: RFC: Extension Packaging & Lookup
From: "David E. Wheeler" <david@justatheory.com>
In-Reply-To: <2CAD6FA7-DC25-48FC-80F2-8F203DECAE6A@justatheory.com>
Date: Mon, 28 Oct 2024 18:19:43 -0400
Cc: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>,
 Peter Eisentraut <peter@eisentraut.org>,
 Christoph Berg <myon@debian.org>,
 Andres Freund <andres@anarazel.de>
Content-Transfer-Encoding: quoted-printable
Message-Id: <0D3FE0EA-0E87-42BD-AB15-D3549BFD0F4C@justatheory.com>
References: <2CAD6FA7-DC25-48FC-80F2-8F203DECAE6A@justatheory.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/0D3FE0EA-0E87-42BD-AB15-D3549BFD0F4C%40justatheory.com>
Precedence: bulk

Greetings Postgres humans,

There was much discussion of this proposal at PGConf.eu =
<http://pgconf.eu/> last week, between Gabriele Bartolini, Peter =
Eisentraut, Christoph Berg, and Andres Freund (all Cc=E2=80=99d here), =
and me, among others. We agreed, in principle, to an approach to this =
feature. Overall I think the proposal doesn=E2=80=99t need to change, =
but there are a couple of things to tweak, and I=E2=80=99ve added a list =
of use cases I=E2=80=99m aware of below, plus a tangent on the =
challenges of loading system DOS.

Quoting a lot and responding inline.

On Oct 10, 2024, at 4:34=E2=80=AFPM, David E. Wheeler =
<david@justatheory.com> wrote:

> I guess I should get off my butt and do it. So let=E2=80=99s do this. =
Here=E2=80=99s what I propose.
>=20
> *   When an extension is installed, all of its files should live in a =
single directory. These include:
>=20
>    *   The Control file in directory describes extension
>    *   Subdirectories for SQL, shared libraries, docs, binaries
>        (also locales and tsearch dictionaries?)

Just to be clear, these directories correspond to the `pg_config =
`--*dir` options, excluding include directories:

```
=E2=9D=AF pg_config --help | grep 'dir\b' | grep -v include
  --bindir              show location of user executables
  --docdir              show location of documentation files
  --htmldir             show location of HTML documentation files
  --libdir              show location of object code libraries
  --pkglibdir           show location of dynamically loadable modules
  --localedir           show location of locale support files
  --mandir              show location of manual pages
  --sharedir            show location of architecture-independent =
support files
  --sysconfdir          show location of system-wide configuration files

```

But perhaps also excluding --sysconfdir?

> *   Next, there should be an extension lookup path. The first item in =
the path is the compile-time default, and ideally would include only =
core extensions. Subsequent paths would be set by a GUC, similar to =
dynamic_library_path, but only for extensions (including their shared =
libraries).

Let=E2=80=99s call it extension_path.

I also suggest adding two new pg_config options, for the directory =
containing core extensions, and a second for system or user extensions. =
Something like:

  --extension-dir      show location of core extensions
  --extension-dir-user show location of user extensions

The default value for the `extension_path` GUC would be, assuming some =
new template variables:

    extension_path =3D '$userextdir,$extdir'

This will allow installers (PGXS) to know where to install non-core =
extensions without bothering the user about it.

> *   Modify PGXS (or create a new installer CLI used by PGXS?) to =
install an extension according to this pattern. Allow the specification =
of a prefix. This should differ from the current `PREFIX`, in that the =
values of `sharedir`, `pkglibdir`, etc. would not be fully-duplicated =
under the prefix, but point to a directory used in the extension path. =
For example, when installing an extension need =E2=80=9Cpair", something =
like
>=20
>        make install BASE_DIR=3D/opt/pg/extension
>=20
>    Would create `/opt/pg/extension/pair`, rather than =
`/opt/pg/extension/$(pg_config --sharedir)/extension/pair`.
>=20
> *   Perhaps there could also be an option to symlink binary files or =
man pages to keep paths simple.
>=20
> *   For CREATE EXTENSION, Postgres would look for an extension on the =
file system by directory name in each of the extension paths instead of =
control file name. It would then find the control file in that directory =
and the necessary SQL and shared library files in the `sql` and `lib` =
subdirectories of that directory.

In discussion, I think we clarified that it should look for =
$extension/$extension.control.

> *   Binary-only extensions might also be installed here; the =
difference is they have no control file. The LOAD command and =
shared_preload_libraries would need to know to look here, too.

Or perhaps we should require a control file for these, too, but add a =
=E2=80=9Ctype=E2=80=9D key or some such? Maybe such a shared module =
could be supported by CREATE EXTENSION, as well as, but not include SQL =
files?

> The basic idea, then, is three-fold:
>=20
> 1.  This pattern is more like a packaging pattern than CREATE =
EXTENSION-specific, since it includes other types of extensions
>=20
> 2.  All the files for a given extension live within a single =
directory, making it easier to reason about what=E2=80=99s installed and =
what=E2=80=99s not.
>=20
> 3.  These extension packages can live in multiple paths.

For dupes, the first one found in the list of extension_path directories =
is the one that Postgres will load.

We also discussed including the version in the directory name, so that =
multiple versions could be installed at once. Not sure how Postgres =
would pick the right one, though.

> Some examples. Core extensions, like citext, would live in, say, =
$(pg_config --extensiondir)/citext), and have a structure such as:
>=20
> ```
> citext
> =E2=94=9C=E2=94=80=E2=94=80 citext.control
> =E2=94=9C=E2=94=80=E2=94=80 lib
> =E2=94=82   =E2=94=9C=E2=94=80=E2=94=80 citext.dylib
> =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 bitcode
> =E2=94=82   =E2=94=9C=E2=94=80=E2=94=80 citext
> =E2=94=82   =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 citext.bc
> =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 citext.index.bc
> =E2=94=94=E2=94=80=E2=94=80 sql
>    =E2=94=9C=E2=94=80=E2=94=80 citext--1.0--1.1.sql
>    =E2=94=9C=E2=94=80=E2=94=80 citext--1.1--1.2.sql
>    =E2=94=9C=E2=94=80=E2=94=80 citext--1.2--1.3.sql
>    =E2=94=9C=E2=94=80=E2=94=80 citext--1.3--1.4.sql
>    =E2=94=9C=E2=94=80=E2=94=80 citext--1.4--1.5.sql
>    =E2=94=9C=E2=94=80=E2=94=80 citext--1.4.sql
>    =E2=94=94=E2=94=80=E2=94=80 citext--1.5--1.6.sql
> ```
>=20
> Third-party extensions would live in one or more other directories on =
the file system, unknown at compile time, but set in the extension path =
GUC and accessible to/owned by the Postgres system user. Let=E2=80=99s =
say we set `/opt/pgxn` as one of the paths. Within that directory, we =
might have a directory for a pure SQL extension in a a directory named =
=E2=80=9Cpair=E2=80=9D that looks like this:
>=20
> ```
> pair
> =E2=94=9C=E2=94=80=E2=94=80 LICENSE.md
> =E2=94=9C=E2=94=80=E2=94=80 README.md
> =E2=94=9C=E2=94=80=E2=94=80 pair.control
> =E2=94=9C=E2=94=80=E2=94=80 doc
> =E2=94=82   =E2=94=9C=E2=94=80=E2=94=80 html
> =E2=94=82   =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 pair.html
> =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 pair.md
> =E2=94=94=E2=94=80=E2=94=80 sql
> =E2=94=9C=E2=94=80=E2=94=80 pair--1.0--1.1.sql
> =E2=94=94=E2=94=80=E2=94=80 pair--1.1.sql
> ```
>=20
> A binary application like pg_top would live in the pg_top directory, =
structured something like:
>=20
> ```
> pg_top
> =E2=94=9C=E2=94=80=E2=94=80 HISTORY.rst
> =E2=94=9C=E2=94=80=E2=94=80 INSTALL.rst
> =E2=94=9C=E2=94=80=E2=94=80 LICENSE
> =E2=94=9C=E2=94=80=E2=94=80 README.rst
> =E2=94=9C=E2=94=80=E2=94=80 bin
> |   =E2=94=94=E2=94=80=E2=94=80 pg_top
> =E2=94=94=E2=94=80=E2=94=80 doc
>    =E2=94=94=E2=94=80=E2=94=80 man
>        =E2=94=94=E2=94=80=E2=94=80 man3
>            =E2=94=94=E2=94=80=E2=94=80 pg_top.3
> ```
>=20
> And a C extension like semver would live in the semver directory and =
be structured something like:
>=20
> ```
> semver
> =E2=94=9C=E2=94=80=E2=94=80 LICENSE
> =E2=94=9C=E2=94=80=E2=94=80 README.md
> =E2=94=9C=E2=94=80=E2=94=80 semver.control
> =E2=94=9C=E2=94=80=E2=94=80 doc
> =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 semver.md
> =E2=94=9C=E2=94=80=E2=94=80 lib
> =E2=94=82   =E2=94=9C=E2=94=80=E2=94=80 semver.dylib
> =E2=94=82   =E2=94=9C=E2=94=80=E2=94=80 bitcode
> =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 semver
> =E2=94=82   =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 semver.bc
> =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 semver.index.bc
> =E2=94=94=E2=94=80=E2=94=80 sql
>    =E2=94=9C=E2=94=80=E2=94=80 semver--1.0--1.1.sql
>    =E2=94=94=E2=94=80=E2=94=80 semver--1.1.sql
> ```

Another example: a binary-only extension loaded via LOAD (or =
*_preload_libraries), and not `CREATE EXTENSION`, like auto_explain:

```
auto_explain
=E2=94=9C=E2=94=80=E2=94=80 auto_explain.control
=E2=94=94=E2=94=80=E2=94=80 lib
    =E2=94=9C=E2=94=80=E2=94=80 auto_explain.dylib
    =E2=94=9C=E2=94=80=E2=94=80 bitcode
    =E2=94=94=E2=94=80=E2=94=80 auto_explain
    =E2=94=82   =E2=94=94=E2=94=80=E2=94=80 auto_explain.bc
    =E2=94=94=E2=94=80=E2=94=80 auto_explain.index.bc
```

I=E2=80=99ve included the control file, as suggested above, as a way to =
manage *all* extensions, not just `CREATE EXTENSION` extensions. A =
non-core extension would be the same, but might include other files like =
a README, LICENSE, etc.

One wrinkle: Some extensions, such as pg_hint_plan[2], include both a =
`CREATE EXTENSION` extension and a `LOAD` module, and both have the same =
name. Not sure how best to adapt for such a case to the proposal to =
include a control file for both types of extension --- because both =
would have the same control file name. My proposal is that names would =
be unique between both `CREATE EXTENSION` and `LOAD` extensions, in =
which case one or the other extension would need to be renamed (probably =
the `LOAD` extension). Then perhaps the `CREATE EXTENSION` extension =
could declare the `LOAD` extension as a dependency.

## Use Cases

Here=E2=80=99s how the proposed file layout and extension_path feature =
would work for the use cases that have driven it.

### Apt/Yum testing

Rather than patching Postgres to look up pg_config directories under a =
prefix[3], a packager who wants to run tests and therefore needs to =
install an extension where Postgres can find it without writing to the =
installed server would follow these steps:

*   Set the extension_path GUC to search the package DESTDIR.
*   Install the extension into that directory: `make install =
BASE_DIR=3D$DESTDIR`
*   Run `make installcheck`

This should allow Postgres to find and load the extension during the =
tests. The Postgres installation will not have been modified, only the =
extension_path will have been changed.

### Postgres.app

The contents of the macOS Postgres.app bundle must be immutable in order =
to validate against the signature generated by an Apple-provided =
certificate. In order to allow extensions to be installed without =
changing the app bundle, the app would either:

1.  Ship with an extension_path pointing to a directory outside the =
bundle, and then users have to know what this directory is when `make =
install`ing an extension; or

2.  Ship with the --extension-dir-user pg_config value described above =
pointing to a directory outside the bundle, into which all non-core =
extensions would be `make install`ed into.

### Docker/Kubernetes

Like Postgres.app, Docker images are immutable, but unlike Postgres.app, =
they represent the entire system. The solution is identical to that for =
Postgres.app, except that instead of installing extensions into =
--extension-dir-user, they would be mounted as volumes in that =
directory.

So, instead of `make install`ing there, a Kubernetes pod can be =
configured to mount a volume for each extension. Need to add a new =
extension to a Pod? Just mount a volume for it that contains the =
necessary files, as described in the examples above.

One wrinkle: Some Kubernetes providers limit the number of volumes that =
can be mounted[4]. If someone needed more volumes than that, one would =
need to adopt a different pattern. Perhaps there could be one volume for =
the extension-dir-user, and an external service could add any and all =
necessary extensions to it?

## Challenge: Third Party Dependencies

Some extensions require third-party dependencies, usually provided by =
the OS. For example, pgsql-http[5] compiles into a DSO that dynamically =
requires another DSO, libcurl. Finding and loading of such dependencies =
is handled by the OS, not by Postgres. This configuration is an =
annoyance on most systems, where the user has to figure out what =
dependencies to install from their OS package manager in order to get it =
to work.

However, it presents a greater challenge for immutable conditions, as in =
Docker and Kubernetes containers. How can system dependencies be added =
without breaking immutability? There are a few options:

1.  Just include all likely dependencies in the base image. This works =
today, but it would be preferable not to include additional dependencies =
that may never be used. It also can unnecessarily bloat an image. Also =
just kinda gross.

2.  Mount a volume with all of the default base image DSOs, then have an =
external process add DSOs to it when required for a new extension. This =
also might work today, although it requires coding that external process =
(e.g., a Kubernetes operator).

3.  Mount a second volume for non-base image DSOs, and again have an =
external process put them there when needed, but then use some method to =
tell the OS where to find them, since they won=E2=80=99t be in the =
default location. More on that below.

4.  Mount individual DSO files as volumes[6] as needed in the system =
shared lib directory where the OS can find them. The wrinkle here is the =
mount limitation imposed by some providers, detailed above.

For solutions that require installing a DSO outside the default =
directories that the system is aware of, one needs a way to tell the =
system where to find them. There are two basic methods for doing so:

1.  Set LD_LIBRARY_PATH to the directory in which third-party DSOs are =
installed. Today, however, this is considered an insecure pattern[7]. It =
doesn=E2=80=99t work at all on macOS, for example, unless you disable =
SIP[8]. Few will do so, nor should they. Andres Freund reports that =
it=E2=80=99s on its way out on Linux, too. So perhaps some can do this, =
but it sounds as if LD_LIBRARY_PATH=E2=80=99s days are numbered.

2.  Use `-rpath` when compiling the DSOs to point to the proper place. =
This embeds the path in the DSO, so it always looks in the same place. =
However, this requires that the DSO be recompiled for every variant of =
the `-rpath`, which creates challenges for non-path specific binary =
packaging --- or if an OS vendor changes the director. But perhaps =
Postgres itself could be compiled with an `-rpath` that will be used =
when loading extensions, so the extension DSOs themselves don=E2=80=99t =
have to know the path? Then the base image just needs to be compiled =
with that option.

## Comments and Corrections

I think I captured most of the issues we discussed at PGConf.eu =
<http://pgconf.eu/> last week; please correct any misunderstandings, =
inaccuracies, and oversights you spot! And are there any other issues =
you can think of with the overall approach?

Thanks for reading to the end!

Best,

David

[1]: =
https://www.postgresql.org/message-id/D30A91FA-A6D4-4737-941F-0BBB2984B730=
%40justatheory.com
[2]: https://github.com/ossc-db/pg_hint_plan/
[3]: https://commitfest.postgresql.org/50/4913/
[4]: https://superuser.com/a/1603150/285886
[5]: https://github.com/pramsey/pgsql-http
[6]: https://stackoverflow.com/a/42260979/79202
[7]: http://xahlee.info/UnixResource_dir/_/ldpath.html
[8]: =
https://developer.apple.com/documentation/security/disabling-and-enabling-=
system-integrity-protection