How To Fix No Rows Returned When Querying job_run_details

You probably didn't realize pg_cron set row level permissions on the cron schema.Sep 11. 2024

There was only one reddit post from 2021 that mentioned this exact issue but it had no good answers. LLMs hallucinated so they were no use.

Solution

If it were an access issue, the user would not be able to see the table at all. The user being able to see the table but with no rows indicates it's a problem with the row-level-security policies. The pg_cron extension creates RLS policies by default on who can view rows within cron.job and cron.job_run_details. You can view them like this:

select * from pg_policies where tablename='job_run_details';

My qual column looked like this:

(username = CURRENT_USER)

Clearly, the cron was created with one user, but was trying to be accessed by another user (a read-only or analytics user, perhaps). We can alter the policy to include this other user as well. Make sure to alter the value under the policyname column:

ALTER POLICY cron_job_policy ON cron.job USING (username = CURRENT_USER OR current_user = '<my_user>');

Problem Context

We run our postgres DB via AWS RDS. Each of our clients has their own materialized view which gets recomputed CONCURRENTLY every hour via a cron. To minimize dependencies, we are using the pg_cron extension to run these jobs on the db itself. Although an admin user created the job, we still need the data team to see when the last time a materialized was updated, hence needing select access to all rows on those tables.