Keg-only PostgreSQL is incompatible with extensions like PostGIS

[9999years]

Summary

The postgresql@16 formula looks for extension files in directories like /opt/homebrew/opt/postgresql@16/lib. Extensions like PostGIS cannot install files into these directories (correctly) due to the Homebrew sandbox. As a result, keg-only PostgreSQL formulae like postgresql@16 are inherently incompatible with extensions.

This is a blocker for upgrading the default PostgreSQL version (as suggested in discussions like #5244), and has not been identified as an issue in discussions on workarounds for installing PostGIS for alternate PostgreSQL versions (like #3987).

brew gist-logs <formula> link OR brew config AND brew doctor output

brew config:

HOMEBREW_VERSION: 4.3.12-62-g870b342
ORIGIN: https://github.com/Homebrew/brew
HEAD: 870b3423bc1f49205536316a1be25e268a653a8b
Last commit: 2 hours ago
Core tap HEAD: 3e9955380ed95685903c38919367b7a1d4d84de6
Core tap last commit: 4 days ago
Core tap JSON: 02 Aug 16:38 UTC
Core cask tap HEAD: 09c42760175cc88bc0c1fb2ace3e82521ca5df4f
Core cask tap last commit: 4 days ago
Core cask tap JSON: 02 Aug 16:38 UTC
HOMEBREW_PREFIX: /opt/homebrew
HOMEBREW_CASK_OPTS: []
HOMEBREW_EDITOR: nvim
HOMEBREW_MAKE_JOBS: 20
HOMEBREW_SORBET_RUNTIME: set
Homebrew Ruby: 3.3.4 => /opt/homebrew/Library/Homebrew/vendor/portable-ruby/3.3.4/bin/ruby
CPU: 20-core 64-bit arm_firestorm_icestorm
Clang: 15.0.0 build 1500
Git: 2.45.2 => /opt/homebrew/bin/git
Curl: 8.6.0 => /usr/bin/curl
macOS: 14.5-arm64
CLT: 15.3.0.0.1.1708646388
Xcode: N/A
Rosetta 2: false

brew doctor:

Your system is ready to brew.

Verification

• My brew doctor output says Your system is ready to brew. and am still able to reproduce my issue.
• I ran brew update and am still able to reproduce my issue.
• I have resolved all warnings from brew doctor and that did not fix my problem.
• I searched for recent similar issues at https://github.com/Homebrew/homebrew-core/issues?q=is%3Aissue and found no duplicates.

What were you trying to do (and why)?

I want to use a version of PostgreSQL other than PostgreSQL 14 with extensions such as PostGIS.

What happened (include all command output)?

PostgreSQL looks in several directories for extensions, including a --libdir (for .a files), --pkglibdir (for .dylib files), and --sharedir (for .sql and .control) files.

In the postgresql@14 formula (which is not keg-only), these directories are under $HOMEBREW_PREFIX:

$ pg_config --libdir
/opt/homebrew/lib/postgresql@14
$ pg_config --pkglibdir
/opt/homebrew/lib/postgresql@14
$ pg_config --sharedir
/opt/homebrew/share/postgresql@14

This means that when postgis is installed and linked into $HOMEBREW_PREFIX, files like /opt/homebrew/opt/postgis/share/postgresql@14/extension/postgis_topology--3.4.2.sql are visible at paths like /opt/homebrew/share/postgresql@14/extension/postgis_topology--3.4.2.sql.

Newer PostgreSQL formulae like postgresql@16 are keg-only, so they look for extensions within their opt prefix:

$ /opt/homebrew/opt/postgresql@16/bin/pg_config --libdir
/opt/homebrew/opt/postgresql@16/lib
$ /opt/homebrew/opt/postgresql@16/bin/pg_config --pkglibdir
/opt/homebrew/opt/postgresql@16/lib/postgresql
$ /opt/homebrew/opt/postgresql@16/bin/pg_config --sharedir
/opt/homebrew/opt/postgresql@16/share/postgresql@16

As a result, it is impossible to use any extensions with the postgresql@16 formula as designed, because if (e.g.) the postgis formula tries to install files into /opt/homebrew/opt/postgresql@16/lib the Homebrew sandbox will rightfully stop it. And because PostgreSQL cannot look in multiple directories for extensions, there appears to be no easy way around this.

What did you expect to happen?

I expected postgresql@16 to locate the postgis extension I installed.

Step-by-step reproduction instructions (by running brew commands)

Here we install [the `postgis@3.4.2` formula I've created](https://github.com/9999years/homebrew-tap-bad-postgis/blob/21111035da0ecd0a1a1c90107349b632669fcfbf/Formula/postgis@3.4.2.rb) with `brew extract` as [suggested by maintainers on the GitHub discussions](https://github.com/orgs/Homebrew/discussions/3987#discussioncomment-4341618). I've replaced the `postgresql@14` dependency with `postgresql@16`, and `postgresql@16` cannot find the newly-installed PostGIS:


brew tap 9999years/homebrew-tap-bad-postgis
brew install postgresql@16 9999years/homebrew-tap-bad-postgis/postgis@3.4.2
# You may need to stop any other running PostgreSQL servers.
brew services start postgresql@16
/opt/homebrew/opt/postgresql@16/bin/psql postgres -c "SELECT name, default_version FROM pg_available_extensions WHERE name LIKE '%postgis%'"
#  name | default_version
# ------+-----------------
# (0 rows)

Meanwhile, postgresql@14 isn't keg-only and as a result can find postgis:

brew install postgresql@14 postgis
brew services start postgresql@14
psql postgres -c "SELECT name, default_version FROM pg_available_extensions WHERE name LIKE '%postgis%'"
#           name          | default_version
# ------------------------+-----------------
#  postgis                | 3.4.2
#  postgis_tiger_geocoder | 3.4.2
#  postgis_topology       | 3.4.2
#  postgis_raster         | 3.4.2
#  postgis_sfcgal         | 3.4.2
# (5 rows)

Note that the installation directories don't quite line up; the postgis@3.4.2 formula I've created gets installed to /opt/homebrew/lib/postgresql@16 and /opt/homebrew/share/postgresql@16, but the postgresql@16 formula (if it were linked into the $HOMEBREW_PREFIX, which it is not) would be looking in /opt/homebrew/lib, /opt/homebrew/lib/postgresql, and /opt/homebrew/share/postgresql@16 instead. However, this is irrelevant because postgresql@16 isn't looking for extensions anywhere outside of its opt prefix regardless.

[9999years]

I think that the path forward may be to make PostgreSQL formulae not keg-only but also to include versioned executables (like psql@16) to avoid link conflicts and make it possible to upgrade from one PostgreSQL version to another without using brew link --overwrite or similar techniques. However, this is a breaking change and requires commitment, so I wanted to open an issue here for discussion first.

[carlocab]

There's ongoing work on this at #167634 -- it sounds similar to the approach you're proposing.

[cho-m]

I think that the path forward may be to make PostgreSQL formulae not keg-only but also to include versioned executables (like psql@16)

I've tried this before but it impacts too much existing tooling/users.

In current state, removing keg_only is impossible as a large enough number of users want the official commands on their PATH (currently possible after brew link and some external projects expect this to work). This means all postgresql@* formulae must install unversioned commands into bin and thus conflict. Having hard conflicting postgresql@* formulae then makes DB migrations difficult so most need to be keg_only.

Only way forward I see for non-keg_only would be implementing a "soft" conflict (Homebrew/brew#16398).

---

The goal of #167634 is to hack around the user voiced requirements like

• Official/Unversioned command must be available in #{bin} for every postgresql@* formula.
• If keg_only, brew link postgresql@X must expose the official binaries on default PATH along with libs/headers in locations that are non-breaking to existing usage.

Anyways, my plan is to try to introduce some modifications like #167634 with PostgreSQL 17.

[github-actions]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.

[github-actions]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.