Unable to CREATE EXTENSTION azure_storage in second database

Question

We have set up the azure_storage extension and activated it in our postgres database on flexible server. It works here as expected. We need to use the extension in a second database on the same server but when running CREATE EXTENSION azure_storage with the admin user on this second database we get error SQL Error [42501]: ERROR: permission denied to alter restricted role "azure_pg_admin"

Update: Turns out we get this error when trying to add ANY extension to ANY database.

Anyone know why?

Answer 1

Hi Peter,

Thank you for reaching on Microsoft Q&A!

Can you please check the following checks.

1. Role Permissions: Make sure that the user you are using has the necessary permissions. Only members of the azure_pg_admin role can create extensions. You can check if your user is a member of this role with the following query:

      SELECT * FROM pg_roles WHERE rolname = 'azure_pg_admin';
   

2. Allowlist the Extension: Ensure that the azure_storage extension (or any other extension you're trying to create) is allowlisted for your instance. You can check the allowlist with:

      SHOW azure.extensions;
   

3. Use CREATE EXTENSION Command: If your user is a member of azure_pg_admin and the extension is allowlisted, you can try using the command again:

      CREATE EXTENSION azure_storage;
   

   If you still get an error, consider using:

      SELECT create_extension('azure_storage');
   

4. Database Context: Make sure you are connected to the correct database context that you want to modify when running these commands.

If you still getting the same issue could you provide us the requested details over Private messaging, these details will help us to investiguate further on this.

Thanks!

Kalyani