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 for Heroku Postgres databases between July and August, 2023. As per Changelog #2662, PostgreSQL extensions are no longer limited to the heroku_ext schema.

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.

To relocate existing extensions from heroku_ext to public, please contact Heroku Support. The default Heroku Postgres credential doesn't have privileges to run ALTER EXTENSION ... SET SCHEMA statements.

On --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