Search code examples
postgresqlazurepg-cron

Azure PostgreSQL Flexible Server Cron Job Permission Denied

I am trying to create and update a cron job on a Azure PostgreSQL Flex server. I have pg_cron installed and I can create the job. But I am unable to update it.

select cron.schedule('0 9 * * 7', 'refresh materialized view sample_mv;');

The above works perfectly, I can see the job scheduled in the job table. But when I try to run the below update statement, it fails with a "ERROR: permission denied for table job".

update cron.job set database = 'newdb' where jobid='3';

Any advice or guidance would be greatly appreciated!

Solution

  • In Azure Database for PostgreSQL Flexible Server, if you try to update the cron.job you will get an error as it requires superuser privileges. However, you can try to use a workaround to make pg_cron jobs work on other databases and manage to do it this way:

    select cron.alter_job(job_id:=<your_job_id>,database:='<your_db_name>');