Remove heroku_ext schema restriction for Postgres Extensions

[jbrown-heroku]

Required Terms

• I agree to follow this project's Code of Conduct
• I have read and accept the Salesforce Program Agreement

What service(s) is this request for?

Postgres

Tell us about what you're trying to solve. What challenges are you facing?

Disclaimer: Due to the security-related nature of this work, this roadmap item is being shared retrospectively.

In August 2022, Heroku implemented a restriction to only allow heroku_ext schema to install Postgres extensions, removing public and any other schemas from options. This restriction was put in place to mitigate security vulnerabilities. This change was abrupt and caused less-than-desirable developer experience.
Reference to FAQ: https://help.heroku.com/ZOFBHJCJ/heroku-postgres-extension-changes-faq

The proposal:
Restore the experience. The heroku_ext schema restrictions should be lifted and allow Postgres extensions to be installed on any schema, including the default public schema, while keeping the database secure.

[jbrown-heroku]

Heroku Data team has made progress in restoring the original experience.

In March, 2023, --allow-extensions-on-public-schema option was released.
In May, 2023, Heroku Data Labs feature extensions-on-any-schema was introduced.
In June, 2023, we announced the upcoming changes to completely restore the experience by Aug 7th, 2023
On July 10, 2023, as part of the planned changes, the functionality of extensions-on-any-schema Data Labs feature was rolled out to all newly provisioned Heroku Postgres databases.

Upcoming:
On July 24, 202, all Essential tier databases (new and existing) will have the restriction removed
On August 7, 2023, all existing databases (hence all Heroku Postgres database) will have the restriction removed.

[swrobel]

Will their be instructions provided for how to move all of our extensions safely back to public and remove the heroku_ext schema for production databases? This is great progress, but still leaves us with inconsistencies between newly-created databases in lower environments, and our production databases that still have the older heroku_ext cruft.

[jbrown-heroku]

This work has completed and databases are no longer restricted to heroku_ext schema by default: https://devcenter.heroku.com/changelog-items/2662

@swrobel thank you for your question. The general instruction would be for you to drop the extension and recreate them on public. If this might cause issues on your production environment, please open a support ticket and we might be able to help, depending on the scope.

[sasharevzin]

@jbrown-heroku if I'll drop and recreate the extension what will happen to the existing data? e.g. citext extension that is used at one of the tables? will it go back to text data type?