postgresqlpg-cron

pg_cron - can not query cron.job after job is scheduled


I used a user - pgadmin, which is a member of rds_superuser, enter image description here

created a scheduled job in "postgres" database.

cron.schedule_in_database('my_cron', '*/30 * * * *', 'INSERT INTO test(id) VALUES (2);', 'mydb');

It inserts a record, into a table - "test" in a different database - "mydb", every 30 minutes. I can see the scheduled job is running, because records are being inserted into test table.

If I connect to postgres database using pgadmin user, I can query cron.job and cron.job_run_details and see the results.

But if I connect to postgres database using my_user, which is not a member of rds_superuser, a select query against cron.jon and cron.job_run_details, returns nothing.

I'm just wondering, what is the reason that the cron job is scheduled and running, but not every user in postgres database can view the data in cron.jon and cron.job_run_details?


Solution

  • The doc says

    For security, jobs are executed in the database in which the cron.schedule function is called with the same permissions as the current user. In addition, users are only able to see their own jobs in the cron.job table.