Azure Database for PostgreSQL flexible server - Cannot grant permission to pg_cron schedule to roles

Andy Clapham 26 Reputation points
2022-12-13T15:29:15.02+00:00

Azure Database for PostgreSQL flexible server -
I'm trying to set up pg_cron so the relevant role can schedule jobs.

I'm logged in with an account with azure_pg_admin role.

I can use cron under this role, e.g. setting up the housekeeping cron works fine:

SELECT cron.schedule('0 0 * * *', $$DELETE   
    FROM cron.job_run_details   
    WHERE end_time < now() - interval '7 days'$$);  

However I would like to set up a cron job from another role.

I try granting execute on the cron.schedule(text,text) function

GRANT USAGE ON SCHEMA cron TO my_role;  
GRANT EXECUTE ON FUNCTION cron.schedule(text, text) TO my_role;  

and that doesn't error but outputs:

no privileges were granted for "cron"  
no privileges were granted for "schedule"  

And I can't schedule jobs under that role.

SELECT cron.schedule('My job', '0 16 * * *', 'call myschema.myfn();');  

errors with

SQL Error [42501]: ERROR: permission denied for schema cron  

Position: 8

Are there any limits or restrictions to doing this in Azure?

Azure Database for PostgreSQL
{count} votes

Accepted answer
  1. GeethaThatipatri-MSFT 28,932 Reputation points Microsoft Employee
    2022-12-20T17:30:08.53+00:00

    Hi, @Andy Clapham Got it, and thanks for the additional information,
    Unfortunately, the Cron schema is still off-limits for custom roles. However, any user with the azure_pg_admin role can schedule maintenance using cron functions. See addition to the docs - Extensions - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn

    Please let me know if this information is helpful.

    Regards
    Geetha


1 additional answer

Sort by: Most helpful
  1. R Aarthi 0 Reputation points
    2023-07-06T03:32:24.61+00:00

    How to enable pgcron in PgAdmin4 version 7, guide me