Postgre-SQL Flexible Server - pg_cron permissions issue

Question

I am connecting to the postgres database with the psqladminun user who is a member of the azure_pg_admin role.

I run the following:

CREATE EXTENSION pg_cron;

After that I go to schedule a job:

SELECT cron.schedule_in_database('part_maint_TEST','0 * * * *', 'call partman.cron_maintain_partitions();', 'TEST');

I receive the following error:

ERROR: permission denied for function schedule_in_database

SQL state: 42501

I verified that psqladminun truly doesn't have privs by checking the following:

SELECT has_function_privilege('psqladminun', 'cron.schedule_in_database(text, text, text, text, text, boolean)', 'EXECUTE');

How is this possible?

Database is PG 16.4

I don't have this issue in other Flexible Server setups that are on PG14.

Thanks,

Justin

Answer

Thanks for posting your question in the Microsoft Q&A forum.

Double-check that psqladminun is indeed a member of azure_pg_admin:

SELECT pg_has_role('psqladminun', 'azure_pg_admin', 'MEMBER');

If the above check confirms membership, try granting explicit permissions to the azure_pg_admin role:

GRANT USAGE ON SCHEMA cron TO azure_pg_admin;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA cron TO azure_pg_admin;

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

1 answer