Issues with Assigning AD Admin Account on Azure PostgreSQL Flexible Servers

Question

Hello,

We are encountering multiple issues with assigning other admins using an Azure AD admin account on Azure PostgreSQL Flexible Servers.

Case 1: Upgraded Instances We recently upgraded our PostgreSQL instances from version 14 to 15, and then to 16. After the upgrade, we observed that previously created roles were not granted with the ADMIN option to the grantor. Due to role changes in PostgreSQL, the parent role now needs to have the ADMIN option to grant roles to other users. Consequently, we are no longer able to manage previously created roles because we never had the ADMIN option granted initially. This is valid for the AD and username + password admins as well.

Case 2: New Instances In newly created PostgreSQL 16 instances, the Azure AD admin user is unable to assign admin privileges. Specifically:

• Only the username + password admin has the ADMIN option over azure_pg_admin.
• The AD admin user configured on these instances does not have the ADMIN option over azure_pg_admin, and therefore cannot attribute admin privileges to other users.

Here are the steps we followed for both cases:

1. Created/Upgraded an Azure PostgreSQL Flexible Server instance.
2. Configured an Azure AD admin user as per the guide.
3. Tried to assign other users as admins using the Azure AD admin account (we followed this tutorial)

Despite following these steps, the AD admin user is unable to make other users admin in both upgraded and new instances. This issue seems replicable with any new instance.

Is there any known bug or workaround for these issues? Any guidance would be greatly appreciated.

Thanks,

Serpa

Answer 1

Hi Serpa •,

Welcome to Microsoft Q&A forum.

As I understand, you are facing issue in Assigning AD Admin Account on Azure PostgreSQL Flexible Servers.

As per the tutorial link shared and followed by you, it belongs to Single Server and it looks like the question is for Flexible Server.

I would recommend you to follow the MS Official documentation for Azure Database for PostgreSQL Flexible server as below and let us know if you still face issue:

https://learn.microsoft.com/en-us/azure/postgresql/single-server/how-to-create-users#how-to-create-additional-admin-users-in-azure-database-for-postgresql

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/flexible-server/how-to-create-users

Let us know your finding.

Thanks.

Answer 2

Hi Serpa •,

This could be because the source server has AAD roles present on it which might not followed the pre-requisites of creating the roles on the target server before the migration.

To create the AAD roles on the target server, customer will have to create the roles on target server using this link. https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-manage-azure-ad-users .

There will be a log line present in logs which will also tell how many AAD roles are present on source server. Log line looks like: AAD Role ----------- is not present on target server.

Query used to figure AAD roles on source server is below.

If there is a mismatch between number of AAD roles reported by the query and actually present on the server.

SELECT
                                  r.rolname
                                FROM
                                  pg_roles r
                                  JOIN pg_auth_members am ON r.oid = am.member
                                  JOIN pg_roles m ON am.roleid = m.oid
                                WHERE
                                  m.rolname IN (
                                    'azure_ad_admin',
                                    'azure_ad_user',
                                    'azure_ad_mfa'
                                  );

Let us know if this helps.

Awaiting your reply.

Thanks