Heroku Postgres extension schema restrictions

The 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 app.json files:

heroku addons:create heroku-postgresql:standard-0 --extensions 'my_extensions.uuid-ossp,my_extensions.postgis,postgis_topology' -a example-app

After database resets with pg:reset:

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

Ask on Stack Overflow

Engage with a community of passionate experts to get the answers you need

Ask on Stack Overflow

Heroku Support

Create a support ticket and our support experts will get back to you

Contact Heroku Support