Issue
IMPORTANT: This is a live document and is updated regularly as we continue improving the processes mentioned in this document. Check back regularly for the latest updates.
Latest update: April 4, 2023
Resolution
Postgres Extension Changes
On August 1, 2022 the mechanism behind how Extensions on Heroku Postgres instances work was updated in order to mitigate security vulnerabilities. (See Changelog)
All Heroku Postgres databases that existed before August 1 had extensions installed in the public
schema, custom user-defined schemas, or other default schemas for specific extensions (these extensions are listed below).
After the changes on August 1, all new extensions will be installed in the heroku_ext
schema (except for the list below). You can no longer install new extensions in other schemas via CREATE EXTENSION ... WITH SCHEMA
for new or existing databases.
On March 27, 2023, Heroku announced improvements to the Heroku Postgres extension experience (see Changelog). We've implemented alternative security measures so users can install extensions outside of the heroku_ext
schema, effectively lifting the extension installation limitations introduced back on August 2022.
All existing databases continue to require extensions to be installed in the heroku_ext
schema. In the near future, we'll provide a method to change this configuration on existing databases.
Important:
postgis_topology
,postgis_tiger_geocoder
andplpgsql
aren't installed on theheroku_ext
schema by default. These extensions use their own schemas:
postgis_topology
is installed in thetopology
schemapostgis_tiger_geocoder
is installed in thetiger
schemaplpgsql
is installed in thepg_catalog
schema
What does this mean for my databases moving forward?
We have been working with our customers and we're aware of the challenges and conflicts that these extension changes have caused, especially when keeping all your environments consistent. We've received valuable feedback from our users and we're improving the experience of using Heroku Postgres extensions.
For existing databases, the extension features added to our tools like pg:copy
, pg:backups:restore
, pg:reset
and addons:create
currently allow customers to achieve consistency across all environments of an application's pipeline. These features allow customers to restore backups and copy databases that include extensions installed outside of heroku_ext
, as well as pre-installing extensions on specific schemas on new or empty databases.
Users can opt in to the improved extension experience by setting the --allow-extensions-on-public-schema
flag when creating a new database.
This flag works for all Standard, Premium, Private, and Shield Heroku Postgres plans.
$ heroku addons:create heroku-postgresql:standard-0 --allow-extensions-on-public-schema
In the near future, we'll provide a method to change this configuration also on existing databases.
Installing new extensions in existing databases
When you install new extensions in your existing database, the CREATE EXTENSION
command installs them in the heroku_ext
schema. Currently, for existing databases, it isn't possible to install an extension in a different schema.
As part of the extension management changes, heroku_ext
was added to the default database search_path
so that your queries can directly use any functions, types, or other objects related to the new extensions you install.
For example, if you install the uuid-ossp
extension to use its uuid_generate_v3
function to generate UUIDs as default values for your columns, any of the two approaches to call the function will work if your search_path
includes heroku_ext
:
- Referencing the function name directly:
CREATE TABLE public.my_table_example (
id uuid DEFAULT uuid_generate_v3() NOT NULL,
(...)
- Referencing the fully qualified function name:
CREATE TABLE public.my_table_example (
id uuid DEFAULT heroku_ext.uuid_generate_v3() NOT NULL,
(...)
Restoring, copying, or importing data from a database with extensions installed in public
or other user-defined schemas
We're extending our features so that you can specify which extensions you want to be pre-installed in public
or other user-defined schemas using the new --extensions
flag. The following Heroku CLI commands support the --extensions
functionality:
addons:create
pg:reset
pg:backups:restore
In addition, the pg:copy
command also installs extensions in public
or other user-defined schemas matching the source database.
This new flag helps ensure compatibility between new and old logical backups, as well as allow customers to create new databases using extensions in a way that's compatible with their old extension setup.
You can use this functionality for use cases like:
- Copying data from your staging database, with extensions in
public
or other user-defined schemas, to a new testing database - Cleaning up and preinstalling extensions with
pg:reset
before importing data withpg:push
orpg_restore
NOTE: This feature doesn't apply when creating extensions in public
for existing databases that contain data, as pg:reset
, pg:backups:restore
, and pg:copy
will delete any existing data.
Managing extensions for Review App databases
The app.json
file supports the extensions
option that allows to specify extensions (and their corresponding schemas) to be preinstalled when the database for a new Review App is provisioned. Use the extensions
option to supply a comma-separated list of extensions and their schemas, in the format <schema>.<extension>
.
If the extensions list doesn't specify a schema for an extension, the public
schema is assumed, except for postgis_topology
and postgis_tiger_geocoder
that use their own schema.
You can use the options.extensions
functionality as follows:
{
"addons" : [
{
"plan": "heroku-postgresql:mini",
"options": {
"extensions": "public.uuid-ossp,public.pgcrypto"
}
}
]
}
Extensions flag usage
NOTE: To use this feature, update the Heroku CLI to version (7.63.0). You can upgrade to the latest Heroku CLI version by running heroku update
.
The --extensions
flag is now supported by the Heroku CLI commands addons:create
, pg:reset
& pg:backups:restore
. Use the --extensions
flag to supply a comma-separated list of extensions and their schemas, in the format <schema>.<extension>
.
If the extensions list doesn't specify a schema for an extension, the public
schema is assumed, except for postgis_topology
and postgis_tiger_geocoder
that use their own schema.
Restoring a backup that includes extensions installed in public
or other user-defined schemas
heroku pg:backups:restore <BACKUP_ID> <DATABASE_NAME> --extensions '<LIST_OF_EXTENSIONS>' -a <APP_NAME>
e.g:
heroku pg:backups:restore b010 STAGING_DATABASE_URL --extensions 'public.pg_stat_statements,hstore,pgcrypto' -a example_app
Creating a new Heroku Postgres database with extensions preinstalled in public
or other user-defined schemas:
heroku addons:create heroku-postgresql:<PLAN> --extensions '<LIST_OF_EXTENSIONS>' -a <APP_NAME>
e.g:
heroku addons:create heroku-postgresql:hobby-basic --extensions `my_extensions.uuid-ossp,my_extensions.postgis,postgis_topology` -a example_app
Reset an existing database and preinstall extensions in public
or other user-defined schemas
heroku pg:reset <DATABASE_NAME> --extensions '<LIST_OF_EXTENSIONS>' -a <APP_NAME>
e.g:
heroku pg:reset STAGING_DATABASE_URL --extensions 'pgcrypto' -a example_app
Known Issues
Reminder: This is a live document and is updated as we release fixes for these issues.
Errors running pg:outliers
with the pg-extras
Heroku CLI plugin
We're working to update the pg:outliers
command from the pg-extras
CLI plugin to be compatible with the pg_stat_statements
extension existing in the heroku_ext
schema.
Customers are seeing command failures when using postgis_raster
with the extensions
flag
If you're using postgis_raster
with the extensions
flag, the command will fail, or hang. For example:
heroku addons:create heroku-postgresql:standard-0 --extensions postgis,postgis_raster -a example-app
heroku pg:backups:restore b010 STAGING_DATABASE_URL --extensions 'postgis,postgis_raster' -a example_app
We're actively working on a fix to resolve the issue.
Database Migration Issues in Rails.
We suggest using our new --extensions
flag to pre-install extensions in your old schema setup.
Or, the following workarounds can help to resolve your issues in some cases:
-
Monkey Patching Rails to allow for greater flexibility around how Rails specifies schemas resolves this issue. You can see an example of this type of pattern in our Review Apps Migration Issue with Ruby help article.
-
In the
structure.sql
file, make sure all instances ofCREATE EXTENSION IF NOT EXISTS "extension_name" WITH SCHEMA public;
are replaced withCREATE EXTENSION IF NOT EXISTS "extension_name";
-
We highly recommend using a
structure.sql
file, but if you're not usingstructure.sql
file, these resources can be helpful:
Database Migration Issues in Django.
We suggest using our new --extensions
flag to pre-install extensions in your old schema setup.
Or, the following workarounds can help to resolve your issues in some cases.
Resolved Issues
Restoring, copying, or importing data from a database with extensions installed in user-defined schemas
Backup restores and database copies failed if the backup or source database included extensions installed outside of the heroku_ext
schema.
For restores, the --extensions
flag functionality solves this problem for those extensions that have to be installed in public
or other user-defined schemas.
For copies, pg:copy
automatically installs extensions matching the schemas of the source database.
In-Dyno Postgres Issues
By default, In-Dyno PostgreSQL databases now include the heroku_ext
schema the same way standard Heroku Postgres databases do.
In-Dyno PostgreSQL databases don't currently have any limits and requirements around extension installation. Test any changes the way your applications manage extensions in a complete staging environment using a Heroku Postgres add-on before releasing to production.
FAQ
Are database forks and rollback databases impacted by these changes?
No. Forks and rollback databases are exact replicas of your database created from physical backups and this functionality isn't impacted by the PostgreSQL extension schema changes. Databases created via the Fork and Rollback have the same extension setup as their parent databases.
Do I need to migrate all my extensions to heroku_ext
?
You don't need to if the use cases above suit your needs.
How were customers notified of the extension changes?
- The first notification was issued via Changelog on August 1, 2022.
- Details of the security vulnerabilities were posted on August 11 on the Heroku status site
- Improvements to the Heroku Postgres extension experience [were announced via Changelog](Improving the Heroku Postgres Extension Experience) on March 27, 2023.