Issue
IMPORTANT: This is a live document and will be updated regularly as we continue improving the processes mentioned in this document, so please check back regularly for the latest updates.
Latest update: Dec 1st 2022
Postgres Extension Changes
The mechanism behind how Postgres Extensions on Heroku Postgres instances work has been updated in order to mitigate security vulnerabilities. The changes were introduced on August 1st.
All databases that existed before August 1st had extensions installed in the public
schema, custom user-defined schemas or other default schemas for specific extensions (these are listed below).
After the changes in August 1st, all new extensions will be installed in the heroku_ext
schema (except for the list below). You will not be able to install new extensions in other schemas via CREATE EXTENSION ... WITH SCHEMA
for new or existing databases.
Resolution
What does this mean for my database(s) moving forward?
We have been working with our customers and we are aware of the challenges and conflicts that these extension changes have caused, especially when keeping all your environments consistent.
The goal moving forward is for your database and schemas to exist in the same state across all environments of an applications pipeline. This "local consistency" will look different for your individual applications depending on a number of factors and will involve using either the public
schema, the heroku_ext
schema or a combination of both depending on what fits your needs.
Important:
postgis_topology
,postgis_tiger_geocoder
andplpgsql
are not on theheroku_ext
schema by default. These extensions have to stay in the following schemas:
postgis_topology
needs to be intopology
schema
postgis_tiger_geocoder
needs to be intiger
schema
plpgsql
needs to be inpg_catalog
schema
Installing new extensions in existing databases
If you need to install new extensions in your existing database, the CREATE EXTENSION
command will install them in the heroku_ext
schema. Currently, for existing databases, it is not 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 are 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 will also install extensions in public
or other user-defined schemas matching the source database.
This new flag will help 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, public
will be assumed, except in the case of postgis_topology
and postgis_tiger_geocoder
that use their own schema.
You can use the options.extensions
functionality as follows:
{
"addons" : [
{
"plan": "heroku-postgresql:hobby-dev",
"options": {
"extensions": "public.uuid-ossp,public.pgcrypto"
}
}
]
}
Extensions flag usage
NOTE: To use this feature you need to 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, public
will be assumed, except in the case of 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 will be updated as we release fixes for these issues.
Errors running pg:outliers
with the pg-extras
Heroku CLI plugin
We are 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 are using postgis_raster
with the extensions
flag you will see the command fail, or hang. e.g:
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 are actively working on a fix to resolve the issue.
Customers cannot perform a Postgres version upgrade if the postgis
extension is installed in their database.
We are currently working on a fix. If you're attempting to upgrade the version of a database that includes PostGIS and are seeing any issues, please let us know via a Support ticket, referencing this document.
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 may help to resolve your issues:
-
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 may 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 may help to resolve your issues.
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
will automatically install 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.
Note that In Dyno PostgreSQL databases don't currently have any limits/requirements around extension installation. Please 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 is not impacted by the PostgreSQL extension schema changes. Databases created via the Fork and Rollback will 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. If the steps mentioned here do not fit your needs we have steps on how to do this manually here.
How were customers notified of this change?
- The first notification was issued via changelog item 2446 on August 1st.
- Details of the security vulnerabilities were posted on August 11th on the Heroku status site
- We have been updating customers via support tickets since then
- A blog post with more details regarding the mitigation strategy and implementation is expected to be published in September.