Issue
I'm configuring partitioning for my tables using the pg_partman extension and I see the following error when running create_parent:
DATABASE=> SELECT create_parent('public.log_entries', 'ts', 'native', 'daily');
ERROR: permission denied for schema heroku_ext
CONTEXT: PL/pgSQL function create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean,text) line 161 at EXECUTE
Resolution
Note on Heroku Postgres extension functionality: The heroku_ext schema limitations for PostgreSQL extensions in Heroku Postgres referenced in this article result from the security vulnerability mitigation changes released on August 1, 2022 (see Changelog). On March 27, 2023, Heroku announced improvements to the Heroku Postgres extension experience. 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.
The default credential for Heroku Postgres doesn't have privileges to create new objects in the heroku_ext schema where extension are installed by default.
If your database has the pg_partman extension installed in the heroku_ext schema, you might get this permission error when pg_partman attempts to create the template table (if no template table name is provided via the create_parent function arguments).
To work around this, you can create a template on any other schemas owned by your Heroku Postgres credential and provide its name in the p_template_table parameter when running create_parent.
For example, if you want to set up partitioning for your log_entries table, you could run:
CREATE TABLE public.log_entries_template (LIKE log_entries);
SELECT create_parent('public.log_entries', 'ts', 'native', 'daily', p_template_table := 'public.log_entries_template');