Share via

PostgreSQL Flexible Server - pg_cron permissions issue

Developer treebyte 20 Reputation points
2025-09-09T14:11:08.7833333+00:00

Hi, I'm having issue trying to use pg_cron functionality on a PostgreSQL flexible server.
The extension was allowed/created correctly and I can see the cron schema on the standard 'postgres' database. The server parameter points to that database.

I am connecting to the 'postgres' database with the server admin user I created during the resource setup, which is a member of the azure_pg_admin role.

When I try to execute cron.schedule_in_database, I get an ERROR: "Permission denied for function schedule_in_database".

If I check the permission using:

SELECT has_function_privilege('postgres', 'cron.schedule_in_database(text, text, text, text, text, boolean)', 'EXECUTE');

I get a false, indicating that the user does not in fact have the priviledge.

I've tried 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA cron TO postgres;' but I get an ERROR 'permission denied for function alter_job'.

DB version is 16.9.

I've also tried to drop and re-create the extension with no success.
I've tried to create a different user but I get the same results.

The issue is very similar to this one: https://learn.microsoft.com/en-gb/answers/questions/2121382/postgre-sql-flexible-server-pg-cron-permissions-is

Thanks,
Mirko

Azure Database for PostgreSQL
Accepted answer
  1. Jerald Felix 5,875 Reputation points
    2025-09-09T15:48:28.91+00:00

    Hello Developer treebyte,

    Thank you for providing such a detailed breakdown of the issue, including the exact error message and the steps you've already taken. This is a classic and often frustrating permission problem when using extensions like pg_cron in a managed database environment like Azure PostgreSQL Flexible Server.

    The root of the problem is that even though your admin user is a member of the azure_pg_admin role, this role does not have full SUPERUSER privileges. The pg_cron extension and its functions are owned by an internal service account, not by your user. As a result, you don't have the permission to GRANT privileges on those functions, which is why your GRANT EXECUTE command failed. This is expected behavior in the managed service to maintain the platform's security and stability.

    However, your user should be able to schedule jobs. The permission denial you're seeing often points to a subtle issue with how the function is being called.

    Primary Suggestion: Use cron.schedule instead

    You are using the cron.schedule_in_database function. This function is specifically designed to schedule a job that will run against a different database from the one you are currently connected to. Since pg_cron's metadata is in the postgres database (as per your cron.database_name setting), this function adds another layer of security checks.

    If your intention is to run the scheduled job within the postgres database itself, you should use the simpler cron.schedule function.

    Please try scheduling your job with this syntax:

    sql
SELECT

    This function has a more straightforward permission path and is the standard way to schedule jobs that operate on the same database where pg_cron is configured.

    Workaround: Use a SECURITY DEFINER Function

    If the above still fails, or if you must run tasks that require elevated privileges, you can encapsulate your logic within a wrapper function and use the SECURITY DEFINER clause. This makes the function execute with the privileges of the user who created it (your admin user), which can bypass in-session permission issues.

    1. Create the wrapper function:

    sql
CREATE

    2. Schedule the wrapper function:

    sql
SELECT

    Final Step: Azure Support

    You've done excellent troubleshooting by dropping/re-creating the extension and trying different users. If neither of the solutions above work, it's very likely that there is an underlying issue with how the extension was provisioned on your specific server instance, especially given you are on a very new database version (16.9). The link you shared points to a similar problem, which often indicates a platform-level fix is needed.

    In this case, your best path forward would be to open a support ticket with Microsoft Azure. Their support engineers can investigate the backend configuration of your instance and resolve any permission inconsistencies that are beyond your control.

    I hope one of the suggestions helps you get unblocked.

    Best Regards,

    Jerald Felix

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.