Cannot create azure_ai extension in Postgres Flexible Server 16.9

Hi @Nimrod Senhav
 
Azure PostgreSQL Flexible Server uses a special admin role called azure_pg_admin. This role has elevated privileges but cannot be modified or given extra roles, including those needed to manage specific extensions like azure_ai. To configure service settings such as endpoints and API keys for the azure_ai extension, the azure_ai_settings_manager role is required. This role is automatically granted to azure_pg_admin, but it cannot be altered or manually assigned, which results in the permission error you're encountering.

Allow extensions | Microsoft Learn

Here’s a step-by-step guide to properly install and configure the azure_ai extension:

Allowlist the Extension

Before installing, ensure azure_ai is added to the server’s allowlist.

• Go to Azure Portal → Your PostgreSQL Flexible Server → Server Parameters
• Find azure.extensions and add azure_ai to the list
• Save and restart the server

Verify Extension Availability

Run the following SQL to confirm the extension is available:

SHOW azure.extensions;

If azure_ai is listed, proceed to install it.

Install the Extension

Connect to your target database (not the default postgres database) and run:

CREATE EXTENSION IF NOT EXISTS azure_ai;

Generate vector embeddings with Azure OpenAI in Azure Database for PostgreSQL - Azure Database for PostgreSQL | Microsoft Learn

Configure the Extension

Use the built-in functions to set service endpoints and API keys:

SELECT azure_ai.set_setting('azure_openai.endpoint', 'https://your-openai-endpoint.azure.com');

SELECT azure_ai.set_setting('azure_openai.api_key', 'your-api-key');

These functions require the azure_ai_settings_manager role, which is already granted to azure_pg_admin by default. Generate vector embeddings with Azure OpenAI in Azure Database for PostgreSQL - Azure Database for PostgreSQL | Microsoft Learn

Key Pointers - You cannot alter or assign roles to azure_pg_admin. This is by design in Azure PostgreSQL Flexible Server.

Always connect using the server admin account created during provisioning.

If you still face issues, verify that you're connected to the correct database and not the default postgres database.

Extension usage considerations

Your patience and understanding are truly appreciated.

Thanks,
Vrishabh

Hi Nimrod Senhav,

We apologize for the delay in our response. Please let us know whether the proposed solution addressed your concern or if you require additional support. If you experienced any issues, kindly provide the details so we can assist promptly.

Thanks,
Vrishabh