Azure Database for PostgreSQL flexible server -
I'm trying to set up pg_cron so the relevant role can schedule jobs.
I'm logged in with an account with azure_pg_admin role.
I can use cron under this role, e.g. setting up the housekeeping cron works fine:
SELECT cron.schedule('0 0 * * *', $$DELETE
FROM cron.job_run_details
WHERE end_time < now() - interval '7 days'$$);
However I would like to set up a cron job from another role.
I try granting execute on the cron.schedule(text,text) function
GRANT USAGE ON SCHEMA cron TO my_role;
GRANT EXECUTE ON FUNCTION cron.schedule(text, text) TO my_role;
and that doesn't error but outputs:
no privileges were granted for "cron"
no privileges were granted for "schedule"
And I can't schedule jobs under that role.
SELECT cron.schedule('My job', '0 16 * * *', 'call myschema.myfn();');
errors with
SQL Error [42501]: ERROR: permission denied for schema cron
Position: 8
Are there any limits or restrictions to doing this in Azure?