citusdata / citusdata/pg_cron

alter_job should allow to change own jobs

Open
#258 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
3.9k
Forks
260
Avg merge
5d 5h
Merged PRs (30d)
1

Description

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.

```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.