alter_job should allow to change own jobs

[simonuwe]

For a user it is possible to schedule and unschedule a job, but it is not possible to alter own just scheduled jobs

For applications maintainance it is sometimes required to first stop all scheduler-jobs and after the maintainance activate them again. It would also be beneficial when a user can change the schedule/command of his own jobs. Currently this is only possible via user postgres or when the execute permission on cron.alter_job is granted to the user. But with the grant the user can change the database too even the user has no permission to execute schedule_in_database

##Idea: Allow all users the modify attribute schedule, command, active of their own jobs. Limit the the modification on database (like already username) to superuser and return something like

SQL-Error [XX000]: ERROR: must be superuser to alter database

Example:

> select cron.schedule('testjob','0,10,20,30,40,50 * * * * *','select 1;')

schedule|
--------+
       8|

1 row(s) fetched.

> commit

0 row(s) modified.


> select * from cron.job

jobid|schedule                  |command  |nodename |nodeport|database|username|active|jobname|
-----+--------------------------+---------+---------+--------+--------+--------+------+-------+
    8|0,10,20,30,40,50 * * * * *|select 1;|localhost|    5432|cron    |uwe     |true  |testjob|

1 row(s) fetched.

> select cron.alter_job(jobid, active=>false) from cron.job

SQL-Fehler [42501]: ERROR: permission denied for function alter_job

> commit

0 row(s) modified.


> select * from cron.job

jobid|schedule                  |command  |nodename |nodeport|database|username|active|jobname|
-----+--------------------------+---------+---------+--------+--------+--------+------+-------+
    8|0,10,20,30,40,50 * * * * *|select 1;|localhost|    5432|cron    |uwe     |true  |testjob|

1 row(s) fetched.


> select cron.unschedule('testjob')

unschedule|
----------+
true      |

1 row(s) fetched.

> commit

0 row(s) modified.

[andyatkinson]

+1 I just ran into this myself, and found it surprising that the owner that's created a job can't alter it. That seems like surprising behavior, and I wondered whether it was intentional or not.

Expected: I can alter my own jobs Actual: I get a permission denied error running cron.alter_job function

Usage: I wanted to change the cron expression for a job

SELECT cron.alter_job(job_id:=6,schedule:='* * * * *');
ERROR:  permission denied for function alter_job

Workaround: In a superuser session, I can change the cron expression attribute of the job, "on behalf" of the job owner

Thanks!