Unable to run pg_partman jobs due to lost permissions on admin account to extension tables

Wolfjaw Studios 0 Reputation points
2025-07-16T15:21:09.14+00:00

I am using pg_partman to manage partitions. My admin account lost permission to access partman.part_config and cannot run partition jobs anymore and I am unable to grant permission to the table manually for that user. How can I fix this issue and regain use of this extension

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
{count} votes

1 answer

Sort by: Most helpful
  1. Mahesh Kurva 6,850 Reputation points Microsoft External Staff Moderator
    2025-07-16T16:38:34.89+00:00

    Hi Wolfjaw Studios ,

    Greetings!!

    As i understand that your admin account lost access to critical pg_partman extension tables like partman.part_config, which is preventing partition maintenance jobs from running. This issue can occur due to changes in ownership, revoked privileges, or role misconfigurations.

    Here's how you can fix it:

    1.Check Role Permissions: Ensure that the role you're using to run pg_partman has the necessary permissions on the partman.part_config table. pg_partman does not require a superuser role, but it does need a role that has ownership over the schema and objects involved.

    2.Re-grant Permissions: If possible, re-establish the permissions for your admin account. To do this:

    GRANT ALL PRIVILEGES ON partman.part_config TO your_admin_role;
    

    Replace your_admin_role with the actual name of your admin role.

    3.Create a Separate Role:

    A superuser role isn't required with pg_partman. The only requirement is that the role that runs pg_partman functions has ownership over all the partition sets and schemas where new objects will be created.

    We recommend that you create a separate role for pg_partman and give it ownership over the schema and all the objects that pg_partman will operate on:

    CREATE ROLE partman_role WITH LOGIN; 
    CREATE SCHEMA partman; 
    GRANT ALL ON SCHEMA partman TO partman_role; 
    GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_role; 
    GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman_role; 
    GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman_role; 
    GRANT ALL ON SCHEMA <partition_schema> TO partman_role; 
    GRANT TEMPORARY ON DATABASE <databasename> to partman_role; --  This allows temporary table creation to move data.
    

    4.Run SQL Queries: If you can't manually adjust permissions, you might need to run SQL commands as a different user (if you have access) or contact your database administrator if permissions were revoked inappropriately.

    For more information, please refer the document: Set up permissions.

    I hope this information helps. Please do let us know if you have any further queries.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.