heroku_ext schema limitations for PostgreSQL extensions in Heroku Postgres referenced in this article (and its previous versions) result from the security vulnerability mitigation changes released on August 1, 2022.
This restriction was removed first for new Heroku Postgres databases on July 10, 2023, all Essential databases on July 24, 2023 and later for all existing Heroku Postgres databases on August 7, 2023. Check our blogpost "Improving the Heroku Postgres Extension Experience" for more details on these changes.
Currently, you can install PostgreSQL extensions on any schema on your Heroku Postgres databases using
CREATE EXTENSION <extension> WITH SCHEMA <schema>. Installing PostgreSQL extensions with
CREATE EXTENSION installs the extension in the
public schema by default.
--extensions flag CLI functionality
In order to ensure compatibility between new and old databases while the extension restrictions were in place between August 2022 and July/August 2023, we extended several Heroku Postgres features to support a new
--extensions flag. This flag allowed installing extensions on user-defined schemas and it's no longer needed to achieve this, as you can now directly install extensions in any schemas. These main use cases for this flag were:
On database creation, with
addons:create, or the
addons.options.extensions option for
heroku addons:create heroku-postgresql:standard-0 --extensions 'my_extensions.uuid-ossp,my_extensions.postgis,postgis_topology' -a example-app
After database resets with
heroku pg:reset STAGING_DATABASE_URL --extensions 'public.pgcrypto' -a example-app
Before database restores
pg:backups:restore, when restoring backups that included extensions installed in user-defined schemas:
heroku pg:backups:restore b010 STAGING_DATABASE_URL --extensions 'public.hstore,public.pgcrypto' -a example-app