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.