Supabase postgres cron job not working special case

Question

I'm having the problem, while insering a row using rpc through the nodejs library, with user service_role.

i gave permission to all roles. Without exception.

And for service_role I even gave it all privileges in the whole database just for the sake of testing.

launching the cron from the dashboard editor. And after giving the permission to postgres as above. And as per this thread
It worked. The cron worked just right.

But then when i do it through service_role. From nodejs rpc insert. Which trigger a trigger procedure. Which run all right.

I made a command to ccheck which insert in a log table I created. In mean time, the job is added to the jobs table. And being active.

However no command run. (the mentioned user I gave him the privileges for cron. But then after I gave him even access to all. Nothing works.)

Any ideas or pointing.

Here my trigger function

The schedule command is temporal I took from the one that worked on the dashboard to make sure ..?

How can this be explained ? Any ideas or points ? Anything may be special about supabase ? May be the service_role user have some restriction ??? or . i'm just out of clues hhh

( an official ref: https://supabase.io/blog/2021/03/05/postgres-as-a-cron-server )

Answer 1

I solved my problem!

You can check that in those threads where i asked! And shared many elements!

https://github.com/supabase/supabase/issues/1703

https://github.com/supabase/supabase/discussions/479

how to check the cron job run, and log

From the official articles (article about cron job)

Check the cron.job_run_details! It contain every attempted command execution.

select * from cron.job_run_details;

Checking the table as mentioned on the article! i found out that the command execution is failing! Because the connection failed!

I made the user a super user temporally! But then it seems not the thing! And it's something that require Db Authentication config!

citusdata/pg_cron#66

I was inserting a row from nodejs (service_role)! Which trigger a function that schedule the cron! The cron schedule all right and work! Commands only fail!

I mostly tried everything! Then i considered creating a new user!

Solution

I ended up creating a total new user (role)! With it's password! And used that for the external connection! I gave the new user the necessary priveleges! And all worked well!

(Using service_role was problematic! Even by giving it super user privileges! it didn't work! And fail by a 'connection failed' error!