run_maintenance function fails when trying to execute as user other than schema owner or postgres user

[hrawulwa]

I need to be able to run partition maintenance as user other than schema owner.
However it fails with error must be owner of the table.
In this example, I'm calling the function as pgpoc_app user. The tables are owned by pgpoc schema.

pgpoc1d01=> SELECT partman.run_maintenance();
ERROR: must be owner of table part_test_daily
CONTEXT: SQL statement "ALTER TABLE pgpoc.part_test_daily ATTACH PARTITION pgpoc.part_test_daily_p2023_05_11 FOR VALUES FROM ('2023-05-11 00:00:00+00') TO ('2023-05-12 00:00:00+00')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 249 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 284 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 511 at RAISE

As per partman documentation, I granted all the privileges to pgpoc_app user.

CREATE ROLE partman WITH LOGIN;
GRANT ALL ON SCHEMA partman TO partman;
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman;
GRANT ALL ON SCHEMA pgpoc TO partman;
-- Finally grant partman role to pgpoc_app user
GRANT partman TO pgpoc_app;

I ensured that user pgpoc_app has all privileges on the schema owner which owns the tables.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA pgpoc to pgpoc_app;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES ON ALL TABLES IN SCHEMA pgpoc to pgpoc_app;
GRANT CREATE on database pgpoc to pgpoc_app;
GRANT ALL ON SCHEMA pgpoc TO pgpoc_app;

What could be the problem here?

Thanks
Hari

[keithf4]

Many ALTER TABLE statements require being the owner of the object in order to run that statement. So you will have to make sure that you make the role that will be running maintenance a member of the role group that owns the table. Assuming pgpoc_app is the role running maintenance...

GRANT table_owner TO pgpoc_app;

[hrawulwa]

Thank you.

[keithf4]

If you have a chance, please let me know if this worked for you and so that anyone else coming across this issue knows that this is a workable solution. Thank you!