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

Question

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

Wolfjaw Studios 10

Hello I had a previous thread on this issue that did not reach a resolution: https://learn.microsoft.com/en-us/answers/questions/4376274/unable-to-run-pg-partman-jobs-due-to-lost-permissi

The original issue was

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

working with the Support Person in the previous thread lead to some suggestions on adding permissions to my admin role

GRANT ALL PRIVILEGES ON partman.part_config TO your_admin_role;

However attempting to access the pg_partman.part_config table throws errors.

citus=> select * from partman.part_config;
ERROR:  permission denied for table part_config

citus=> GRANT SELECT ON SCHEMA partman TO citus;
ERROR:  invalid privilege type SELECT for schema
citus=> GRANT ALL ON SCHEMA partman TO citus;
GRANT
citus=> SELECT * from partman.part_config;
ERROR:  permission denied for table part_config
citus=>

Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2025-09-23T23:45:18.33+00:00

Hi Wolfjaw Studios Thank you for reaching out and sorry to hear about the issue you are facing.

Seem like this is a duplicate to this thread https://learn.microsoft.com/en-us/answers/questions/4376274/unable-to-run-pg-partman-jobs-due-to-lost-permissi

If yes, I will close the old one for now and we can work on the most recent one.

Could you try recreating the extension with correct Ownership first.

DROP EXTENSION pg_partman CASCADE;

CREATE EXTENSION pg_partman SCHEMA partman;
Wolfjaw Studios 10 Reputation points

2025-09-24T17:48:15.2966667+00:00

Hello! Thank you for the response.

Is there any risk of losing partitioned tables by running
DROP EXTENSION pg_partman CASCADE;
Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2025-09-25T19:34:39.6833333+00:00
Wolfjaw Studios

Yes, there is a risk of losing configuration and data.

Could you please share your current role.?

Did you do the prerequisites while enabling pg_partman https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-use-pg-partman#prerequisites

Let's try this:

SELECT extname, extowner::regrole FROM pg_extension WHERE extname = 'pg_partman';

This tells you which role owns the extension. Only that role (or azure_pg_admin) can grant access to its internal tables.

Safer Alternative (if you want to preserve data)

If you just need to fix permissions:

GRANT USAGE ON SCHEMA partman TO your_admin_role;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA partman TO your_admin_role;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO your_admin_role;

Test with

SELECT * FROM partman.part_config;

Please try the above and let me know the result.

Regards,

Oury

Wolfjaw Studios 10

Thank you for the response and suggestions

No we used the following when setting up the extension. we did not do this through the Azure dashboard

CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

The responses I received are

citus=> SELECT extname, extowner::regrole FROM pg_extension WHERE extname = 'pg_partman';
  extname   | extowner 
------------+----------
 pg_partman | postgres
(1 row)

citus=> SELECT * FROM partman.part_config;
ERROR:  permission denied for table part_config

citus is my admin account, i do not have any access to to the postgres account

attempting to grant permissions gives this

citus=> GRANT USAGE ON SCHEMA partman TO citus;
GRANT
citus=> GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA partman TO citus;
ERROR:  permission denied for table part_config

Mahesh Kurva 10,520 Reputation points Microsoft External Staff Moderator

2025-10-20T11:36:44.59+00:00

Hi Wolfjaw Studios

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2025-09-23T23:45:18.33+00:00

Hi Wolfjaw Studios Thank you for reaching out and sorry to hear about the issue you are facing.

Seem like this is a duplicate to this thread https://learn.microsoft.com/en-us/answers/questions/4376274/unable-to-run-pg-partman-jobs-due-to-lost-permissi

If yes, I will close the old one for now and we can work on the most recent one.

Could you try recreating the extension with correct Ownership first.

DROP EXTENSION pg_partman CASCADE;

CREATE EXTENSION pg_partman SCHEMA partman;
Wolfjaw Studios 10 Reputation points

2025-09-24T17:48:15.2966667+00:00

Hello! Thank you for the response.

Is there any risk of losing partitioned tables by running
DROP EXTENSION pg_partman CASCADE;
Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2025-09-25T19:34:39.6833333+00:00

Wolfjaw Studios

Yes, there is a risk of losing configuration and data.

Could you please share your current role.?

Did you do the prerequisites while enabling pg_partman https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-use-pg-partman#prerequisites

Let's try this:

SELECT extname, extowner::regrole FROM pg_extension WHERE extname = 'pg_partman';

This tells you which role owns the extension. Only that role (or azure_pg_admin) can grant access to its internal tables.

Safer Alternative (if you want to preserve data)

If you just need to fix permissions:

GRANT USAGE ON SCHEMA partman TO your_admin_role;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA partman TO your_admin_role;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO your_admin_role;

Test with

SELECT * FROM partman.part_config;

Please try the above and let me know the result.

Regards,

Oury
Wolfjaw Studios 10 Reputation points

2025-09-26T15:18:59.66+00:00

Thank you for the response and suggestions

No we used the following when setting up the extension. we did not do this through the Azure dashboard

CREATE SCHEMA IF NOT EXISTS partman; CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

The responses I received are

citus=> SELECT extname, extowner::regrole FROM pg_extension WHERE extname = 'pg_partman'; extname | extowner ------------+---------- pg_partman | postgres (1 row)

citus=> SELECT * FROM partman.part_config; ERROR: permission denied for table part_config

citus is my admin account, i do not have any access to to the postgres account

attempting to grant permissions gives this

citus=> GRANT USAGE ON SCHEMA partman TO citus; GRANT citus=> GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA partman TO citus; ERROR: permission denied for table part_config
Mahesh Kurva 10,520 Reputation points Microsoft External Staff Moderator

2025-10-20T11:36:44.59+00:00

Hi Wolfjaw Studios

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.