Postgre-SQL Flexible Server - pg_cron permissions and alternative

Question

Hi everyone, 

 a customer is using Azure PGSQL Flexible server to provide database managed services for its internal customers.

 The internal customers are used to work on-prem with job schedulers like pg__cron or pg__timetable.

 After migrating their PostgreSQL Databases to Azure Flexible Server, they want to provide the same functionalities to the end customers. Since only pg_cron is available as Flexible Server extensions, they are trying to configure it.

 Now they have a problem with the role required to create a job, which is azure_pg_admin. This role is too elevated to be assigned to he end customers and they want to know if there is any alternative for that?

 My questions:

• Any idea on how to configure the roles to let end customers create pg_cron jobs without getting the azure_pg_admin role?
• Will pg_timetable or pgagent at some point available in Flexible Server?
• Is there a Azure Cloud Native alternative they can offer to the end customer, something with Azure Automation or Azure DevOps?

 Thanks in advance, 

Paul

Answer 1

Hi Paul Hernandez,

We have got the below reply:

The approach involves creating stored procedures or functions with the SECURITY DEFINER attribute. These functions can leverage the cron.* functions to schedule jobs. By granting execute permissions on the newly created procedure/function to the target user, you can enable them to trigger these scheduled tasks with no need to have azure_pg_admin, for example:

 

 

CREATE OR REPLACE FUNCTION my_secure_schedule(schedule text, command text) 

RETURNS bigint 

LANGUAGE plpgsql 

SECURITY DEFINER 

AS $$ 

DECLARE 

    job_id bigint; 

BEGIN 

    -- Call the cron.schedule function 

    job_id := cron.schedule(schedule, command); 

      

    -- Return the job ID 

    RETURN job_id; 

END; 

$$; 

 

And after that you can grant -- GRANT EXECUTE ON FUNCTION my_secure_schedule(text, text) TO myuser;

 

Now myuser can run my_secure_schedule, and that function if you can see the definition shared before can schedule jobs to run.  He can do the rest for cron functions with the same way based on his needs.

Hope this helps. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Thanks