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: Created/Upgraded an Azure PostgreSQL Flexible Server instance. Configured an Azure AD admin user as per the guide. 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

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.

Issues with Assigning AD Admin Account on Azure PostgreSQL Flexible Servers

Serpa 151

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:

Created/Upgraded an Azure PostgreSQL Flexible Server instance.
Configured an Azure AD admin user as per the guide.
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

Serpa 151 Reputation points

2024-07-24T08:51:01.6333333+00:00
Hi Shashidhar-MSFT,

Thank you for your response. It seems there might be a misunderstanding about the issue we're facing.

Please let me clarify:

Instance Type: We are indeed using Azure PostgreSQL Flexible Servers, not Single Servers.

Upgrade Issue: Our main problem stems from the upgrade process from PostgreSQL 14 to 16. During this upgrade, we lost admin privileges over previously created roles because these roles did not inherit the ADMIN option. As a result, we can no longer manage these roles effectively.

New Instance Issue: In newly created PostgreSQL 16 instances, the AD admin user lacks the ADMIN option over azure_pg_admin, limiting our ability to assign admin privileges to other users. Only the username + password admin user has the ADMIN option over azure_pg_admin.

The links provided refer to general documentation on role creation and administration, which we have already followed. The specific issue here is related to the loss of control over roles due to the lack of ADMIN option post-upgrade and the insufficient privileges of AD admin users in new instances.

To address the problem:

Is there a way to restore or grant the ADMIN option to roles that were created before the upgrade?

How can we ensure that AD admin users have the necessary ADMIN privileges over azure_pg_admin in new PostgreSQL 16 instances?

This problem is critical as it impacts our ability to manage roles and permissions effectively. Any detailed guidance or workaround would be highly appreciated.

Thank you for your assistance.

Best regards,

Serpa
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
ShaktiSingh-MSFT 14,406 Reputation points Microsoft Employee

2024-07-25T05:16:46.05+00:00

Hi Serpa •,

Thanks for the details.

Let me check internally and get back to you.

Thanks.
ShaktiSingh-MSFT 14,406 Reputation points Microsoft Employee

2024-07-25T07:18:42.43+00:00
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
Serpa 151 Reputation points

2024-07-25T14:03:02.0866667+00:00
Hi @ShaktiSingh-MSFT,

I appreciate your response, but it seems my core concerns weren't fully addressed.

Here's a more detailed explanation of our issue:

We had a PostgreSQL 14 database that we upgraded to version 16. In this database, we have two types of admin users:

A username + password admin named azureuser

An AD admin named psql_shared_staging_admin

We also have multiple service accounts authenticated with username + password, used by applications to access the databases. These service accounts were created by the psql_shared_staging_admin role.

Issue Before Upgrade:

In PostgreSQL 14, when we created roles, the creator role (psql_shared_staging_admin) was automatically granted the INHERIT and SET options over the created roles. For example:

CREATE ROLE data_service_account_staging;

This would result in:

psql_shared_staging_admin | data_service_account_staging | INHERIT, SET | azuresu

We could then perform actions such as:

GRANT data_service_account_staging TO azureuser;

Issue After Upgrade:

Post-upgrade to PostgreSQL 16, we can no longer perform these actions:

ERROR: permission denied to grant role "data_service_account_staging" DETAIL: Only roles with the ADMIN option on role "data_service_account_staging" may grant this role.

The root cause, as stated in the PostgreSQL 16 release notes, is:

Previously, roles with CREATEROLE privileges could change many aspects of any non-superuser role. Such changes, including adding members, now require the role requesting the change to have ADMIN OPTION permission.

Specific Problems:

Upgraded Roles: We lost the ability to manage (grant, delete, update) roles that were created in version 14 because they do not have the ADMIN option assigned to the psql_shared_staging_admin.

New Roles: When creating new roles after the upgrade, the psql_shared_staging_admin is automatically granted the ADMIN option, and we can manage these roles without issues.

Example of a new role creation post-upgrade:

CREATE ROLE smt_service_account_staging;

Resulting in:

psql_shared_staging_admin | smt_service_account_staging | ADMIN | azuresu

Conclusion:

The upgrade process should have ensured that existing roles were granted the ADMIN option to the relevant admin users to maintain manageability. Currently, it looks like we need access to a super user account to resolve this and restore full management capabilities over our roles.

Can you confirm if this is the correct understanding and advise on how we can proceed to regain control over our previously created roles?

Thank you for your attention to this matter.

Best regards, Serpa

1 answer

ShaktiSingh-MSFT 14,406 Reputation points Microsoft Employee

2024-07-24T05:57:42.1933333+00:00

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Issues with Assigning AD Admin Account on Azure PostgreSQL Flexible Servers

Issue Before Upgrade:

Issue After Upgrade:

Specific Problems:

Conclusion:

1 answer