Share via

Postgre-SQL Flexible Server - pg_cron permissions and alternative

Paul Hernandez 631 Reputation points Microsoft Employee
2024-07-04T07:36:43.75+00:00

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

Azure Database for PostgreSQL

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaktiSingh-MSFT 14,376 Reputation points Microsoft Employee
    2024-07-11T09:05:34.2633333+00:00

    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

    0 comments