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