Postgres 16 - 2BP01: role "PROD-infra-dt-sp cannot be dropped because some objects depend on it

Dhivyagrace Godwin 0

When trying to delete the SP role after creating database and schemas, it throws error. However the same command works fine in postgres15. How do we resolve this?

az postgres flexible-server ad-admin delete \
 --yes \
 --resource-group ${POSTGRES_SERVER_RESOURCE_GROUP} \
 --server-name ${POSTGRES_SERVER_NAME} \
 --object-id ${DB_PROVISIONING_SERVICE_PRINCIPAL_OBJECT_ID}

ERROR: (AadAuthPrincipalDropFailed) Failed to drop Azure AD Principal. Reason - 2BP01: role "PROD-infra-dt-sp" cannot be dropped because some objects depend on it.

Dhivyagrace Godwin 0 Reputation points

2025-02-18T23:53:33.68+00:00

Hi @Sai Raghunadh M
Thanks for the response. I tried to reassign the owner but that failed as well.
REASSIGN owned by "PROD-infra-dt-sp" to gmdba;

Detail: Only roles with privileges of role "PROD-infra-dt-sp" may reassign objects owned by it. [Failed SQL: (0) REASSIGN owned by "PROD-infra-dt-sp" to gmdba]
Sai Raghunadh M 2,640 Reputation points Microsoft Vendor

2025-02-19T03:08:12.6933333+00:00

Hi @Dhivyagrace Godwin

Thanks for getting back to me.

Multiple Microsoft Entra ID principals (a user, service principal, or managed identity) can be configured as Microsoft Entra ID administrator for an Azure Cosmos DB for PostgreSQL cluster at any time.

If a Microsoft Entra ID principal is deleted from Microsoft Entra ID service, it still remains as a PostgreSQL role on the cluster, but it's no longer able to acquire new access token. In this case, although the matching role still exists in the Postgres database it's unable to authenticate to the cluster nodes. Database administrators need to transfer ownership and drop such roles manually.

Please go through this Documentation that might help you:

https://learn.microsoft.com/en-us/azure/cosmos-db/postgresql/concepts-authentication?source=recommendations#other-considerations

Hope this helps. Do let us know if you any further queries.
Vishal Anand 0 Reputation points

2025-02-19T15:48:48.4833333+00:00

Hi @Sai Raghunadh M - Checked If a Microsoft Entra ID principal is deleted from Microsoft Entra ID service, this role is also deleted from the PostgreSQL. We never have to manually drop is from the database.
Dhivyagrace Godwin 0 Reputation points

2025-02-19T16:22:21.65+00:00

We also tried to revoke all privileges from this ID and tried to delete after that. But it still fails with same error. Can we setup a call tomorrow?
Sai Raghunadh M 2,640 Reputation points Microsoft Vendor

2025-02-20T19:08:41.86+00:00

Hi @Dhivyagrace Godwin

The error occurs because the role has dependent objects. You cannot drop it before moving these objects to another user. You need to identify the objects dependent on the role and move them before dropping the role.

Ensure to check which objects are dependent on the role and drop them accordingly.

1 answer

Sai Raghunadh M 2,640 Reputation points Microsoft Vendor

2025-02-18T23:33:39.4366667+00:00

Hi @Dhivyagrace Godwin

Thanks for the Question and using Microsoft Q&A

As per my understanding, you are facing error, when you are trying to delete the SP role after creating database and schemas.

As roles can own database objects and can hold privileges to access other objects, dropping a role is often not just a matter of a quick [DROP ROLE] Any objects owned by the role must first be dropped or reassigned to other owners; and any permissions granted to the role must be revoked.

Please refer to this Documentation that might help you: https://www.postgresql.org/docs/16/role-removal.html

Hope this helps. Do let us know if you any further queries.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Postgres 16 - 2BP01: role "PROD-infra-dt-sp cannot be dropped because some objects depend on it

1 answer

Your answer