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

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

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