Heroku Postgres Extension Changes FAQ

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: Sept 15th 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.

Changelog Item 2446

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_geocoderand plpgsql are not on the heroku_ext schema by default. These extensions have to stay in the following schemas:

• postgis_topology needs to be in topology schema

• postgis_tiger_geocoder needs to be in tiger schema

• plpgsql needs to be in pg_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 with pg:push or pg_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.

CREATE EXTENSION <extension_name> IF NOT EXISTS WITH SCHEMA <schema_name> returns an error even if the extension exists already

Running CREATE EXTENSION <extension_name> IF NOT EXISTS WITH SCHEMA <schema_name> for a schema different than heroku_ext can result in an error even if the extension is already installed in the specified schema.

We are working on a fix.

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:

• In the structure.sql file, make sure all instances of CREATE EXTENSION IF NOT EXISTS "extension_name" WITH SCHEMA public; are replaced with CREATE EXTENSION IF NOT EXISTS "extension_name";

• We highly recommend using a structure.sql file, but if you're not using structure.sql file, these resources may be helpful:

  • Why do I get postgres extension errors when I run rake db structure load or rake db setup?
  • Migrating postgres extensions to the heroku_ext schema
  • Extension must be installed in heroku_ext errors

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.

I'm seeing an error saying that language "plpgsql" does not exist (or similar)

This happens when the plpgsql extension has dropped from a database. We're actively working on a fix to prevent this issue from happening in the future. But, to be safe; don't run DROP EXTENSION plpgsql. However, if it happens, let us know in your Support ticket and we can resolve it for you.

Resolved Issues

Customers are seeing pg:copy attempts to install postgis_topology in heroku_ext

This bug has been fixed, but if you see any further issues please let us know in your support ticket.

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.

Changelog Item 2463

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.

Further Reading

• Supported Postgres Extensions on Heroku Postgres