Heroku Postgres Extension Changes FAQ

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 and plpgsql aren't installed on the heroku_ext schema by default. These extensions use their own schemas:

• postgis_topology is installed in the topology schema
• postgis_tiger_geocoder is installed in the tiger schema
• plpgsql is installed in the pg_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 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, 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 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 can be helpful:

  • Why do I get postgres extension errors when I run rake db structure load or rake db setup?

  • 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 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.

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

Further Reading

• Supported Postgres Extensions on Heroku Postgres