Learning from Expertise #3: Why cannot remove AD Admin or set a new one on Azure PostgreSQL Server?!

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Overview:

We see customers were not able to delete the AD Admin user in Azure database for PostgreSQL, and after triggering the operation , the request keep running forever without any error message or it fails with error like:

'The user name conflict with an existing database user name' is not a valid name because it contains invalid characters.

Solution:

In this scenario, most properly the operation stuck because there is a dependent objects on the AAD role, so you will need to remove these dependencies first , so log in with your server admin user and check the object dependency using the following query:

Note:-

-- make sure to run this query against all databases.

-- Replace ####### with AAD admin User or group.

WITH myconstants (MyUser, ExcludeUser) AS ( values ('########', 'azure_superuser') ) SELECT n.nspname AS schema_name, c.relname AS rel_name, c.relkind AS rel_kind, pg_get_userbyid(c.relowner) AS owner_name FROM myconstants, pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE pg_get_userbyid(c.relowner) NOT LIKE concat('%',ExcludeUser,'%') AND pg_get_userbyid(c.relowner) LIKE concat('%',MyUser,'%') UNION ALL SELECT n.nspname AS schema_name, p.proname, 'p', pg_get_userbyid(p.proowner) FROM myconstants, pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE pg_get_userbyid(p.proowner) NOT LIKE concat ('%',ExcludeUser,'%') AND pg_get_userbyid(p.proowner) LIKE concat('%',MyUser,'%')

To mitigate this issue, you will need to change the ownership of these object to other new/existing user and retry the operation:

REASSIGN OWNED BY "#######" TO "local database user"; DROP OWNED BY "#######";

For more details, please check out PostgreSQL documentation:

PostgreSQL: Documentation: 11: REASSIGN OWNED

In case, after you cleaned the dependences to AAD Admin and you want to set the AD Admin again but it fails again with error

'The user name conflict with an existing database user name' is not a valid name because it contains invalid characters.

As a workaround, you can assign the AAD admin role to another AAD admin user and then you will be able to do the override. by then you should be able to reset the AAD admin once again.

I hope you find this article helpful. If you have any feedback please do not hesitate to provide it in the comment section below.

Ahmed S. Mazrouh

Leave a Reply Cancel reply