Permission issue in updating cron.job table in postgresql

Question

Permission issue in updating cron.job table in postgresql

Naveen J V

SSC-Addicted

Points: 451
More actions
December 17, 2024 at 12:21 pm

#4499558

I am using postgreSQL database for azure flexible server. I have enabled pg_cron extension on azure portal and created pg_cron extension on the database. I am able to see all the required tables/functions on the cron schema of postgres database.
I am able to insert a cron job using cron.schedule function, since this inserts by default to 'postgres' database, I would like to update this job to different database. I used the following command for updating
UPDATE cron.job SET database = 'news_db' WHERE jobid = 1;
But I am getting permission denied error. If anybody faced same issue, let me know how to resolve.
Note, since I am using postgreSQL database for azure flexible server, we don't have superuser access and credentials.
Thanks,
Naveen J V
December 18, 2024 at 6:02 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ryan Booz Old Hand Points: 350 More actions · Answer 1

Hey Naveen,

You can't just change the database name in the cron.jobs table, even if you did have the privilege to do so. If you need to setup the job again, you'll need to use the unschedule() function and then reschedule it with the schedule_in_database() function instead.

HTH,

Ryan

Naveen J V SSC-Addicted Points: 451 More actions · Answer 2

Thanks Ryan,

I tried schedule_in_database, but getting the following error:

ERROR: permission denied for function schedule_in_database

Regards,

Naveen J V

Ryan Booz Old Hand Points: 350 More actions · Answer 3

Gotcha. There should be nothing different about scheduling a regular job vs. one in a different database. Both functions have the same role privileges. My guess, then, is that you created the first job using a role that was a member of pg_azure_admin (I think that's the azure "superuser-like" role), but attempted to create the second job with a role that isn't a member.

Currently, pg_cron does require a role with elevated privilege (generally a superuser, but in a hosted environment, it's usually the superuser-like role that they provide) to create/alter the jobs using the functions. There are workarounds that entail creating a different set of functions, etc. - but I'm guessing that's not what you're looking to do.

Naveen J V SSC-Addicted Points: 451 More actions · Answer 4

Hey Ryan,

I used a role which is member of pg_azure_admin, still I am not able to create a job due to permission denied error.

Thanks,

Naveen J V

Ryan Booz Old Hand Points: 350 More actions · Answer 5

But you can/did create a job with the regular schedule() function?

Naveen J V SSC-Addicted Points: 451 More actions · Answer 6

yes, I can create with schedule() function, only problem is, it take postgres as default database, which I would like to change it to my database, hence I am trying to use schedule_in_database.

Thanks,

Naveen J V

Ryan Booz Old Hand Points: 350 More actions · Answer 7

That obviously shouldn't be happening. And, it looks like something in a recent deployment caused the permissions issue. I'd suggest reaching out to Azure support as mentioned in the issue below.

https://learn.microsoft.com/en-us/answers/questions/2121382/postgre-sql-flexible-server-pg-cron-permissions-is