How to add pgaadauth in azure pg flexible servers it is always giving error,Also how to add pg_cron to a db

Question

CREATE EXTENSION IF NOT EXISTS pgaadauth;

ERROR: extension "pgaadauth" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL

HINT: to learn how to allow an extension or see the list of allowed extensions, please refer to https://go.microsoft.com/fwlink/?linkid=2281561

ALTER SYSTEM SET cron.database_name = 'postgres';

SELECT pg_reload_conf();

ERROR: permission denied to set parameter "cron.database_name"

ERROR: permission denied for function pg_reload_conf

Since only azure internally have superdadmin role

Answer 1

Hi @Aditya Singh,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, The error message indicates that the pgaadauth extension is not allow-listed for azure_pg_admin users. In Azure Database for PostgreSQL - Flexible Server, azure_pg_admin does not have superuser privileges, meaning some extensions and system configurations require Microsoft’s approval.

Azure requires allow-listing for extensions like pgaadauth. To check the allowed extensions, execute the below mentioned sql query. If pgaadauth is missing, you must explicitly request Microsoft to allow it. Raise a support ticket through Azure Portal.

SELECT * FROM pg_available_extensions;

Once approved, You can run:

 CREATE EXTENSION pgaadauth;

If it remains blocked, you cannot manually install it without Microsoft’s intervention.

To add the pg_cron extension and configure it for your database, follow these steps:

• Enable the Extension: First, you need to enable the pg_cron extension by modifying the server parameters.

1. Go to your PostgreSQL server in the Azure portal.
2. Click on the "Server parameters" blade.
3. Search for the shared_preload_libraries parameter and add pg_cron to it.

• Configure pg_cron: After enabling the extension, you can configure it to schedule jobs in your database.

1. From a PostgreSQL client, log in to the server.
2. Issue the following command to check if pg_cron is enabled:

  SHOW shared_preload_libraries;

You should see pg_cron in the output

• Set cron.database_name Parameter: To set the cron.database_name parameter, you need to have the appropriate permissions. Unfortunately, only the azure_superuser role has the necessary permissions to set this parameter and reload the configuration.If you encounter permission issues, you might need to contact Azure support for assistance.
• Schedule Jobs: Once pg_cron is enabled and configured, you can schedule jobs using the extension. For example, to schedule a job that runs every day at 10:00 AM, you can use the following command:

  SELECT cron.schedule('0 10 * * *', 'VACUUM');

This command schedules a VACUUM operation to run daily at 10:00 AM

Please refer to the below mentioned links for more information.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-configure-sign-in-azure-ad-authentication

https://learn.microsoft.com/en-us/azure/postgresql/extensions/concepts-extensions-considerations

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.