Why am I getting a "permission denied" error when running `create_parent` with `pg_partman`?


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


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');

Ask on Stack Overflow

Engage with a community of passionate experts to get the answers you need

Ask on Stack Overflow

Heroku Support

Create a support ticket and our support experts will get back to you

Contact Heroku Support