I have created a new flexible server at version 16 with 2 admins: Postgres admin called "adminuser" Microsoft Entra admin called "entraadmin" (visible at Security - Authentication on portal) My use case is to transfer ownership of a database from entraadmin to adminuser. When running GRANT "adminuser" TO "entraadmin" I got an error: ERROR: permission denied to grant role "adminuser" DETAIL: Only roles with the ADMIN option on role "adminuser" may grant this role. The workaround that I got from related questions is that I need to create a user, grant that adminuser, and transfer ownership to that user. Is there a way to directly give "entraadmin" ADMIN option on role "adminuser"? Thank you for your help in advance!

@Yichen Tian "Apologies for the inconvenience. I have checked internally and confirmed that we are aware of the issue. Our team is actively working on a fix, but we do not have an estimated time of resolution yet." Here is the workaround by having the postgresql adminuser reassign it's ownerships to a new role created by it with ADMIN option and then grant this new role to Entra users. It's quite a bit of juggling especially if you have many tables/databases on the server. I hope the workaround should help you. Regards Geetha

Ability to grant admin user ADMIN option in Azure PostgreSQL Flexible Server version 16

Accepted answer

GeethaThatipatri-MSFT 29,477 Reputation points Microsoft Employee

2024-06-28T15:11:44.5266667+00:00

@Yichen Tian "Apologies for the inconvenience. I have checked internally and confirmed that we are aware of the issue. Our team is actively working on a fix, but we do not have an estimated time of resolution yet."

Here is the workaround by having the postgresql adminuser reassign it's ownerships to a new role created by it with ADMIN option and then grant this new role to Entra users. It's quite a bit of juggling especially if you have many tables/databases on the server.

I hope the workaround should help you.

Regards

Geetha
Please sign in to rate this answer.
Yichen Tian 20 Reputation points

2024-06-28T15:26:20.24+00:00

Hi

Thanks for your reply!

However it looks like you have missed the workaround implementation? Would you mind showing the exact SQL commands for that?

Thank you and looking forward to your reply!

GeethaThatipatri-MSFT 29,477 Reputation points Microsoft Employee

2024-07-01T15:57:34.9133333+00:00

@Yichen Tian we have no exactl SQL commands because it's dependent on your situation. But in a nutshell:

Create a new role with the PSQL admin using password auth with Admin option enabled

Reassign ownership of all dbs and tables to this new role

Make all existing roles/user that need admin/write access inherit from this new role

After that any entra role or entra admin role should be able to manage that new role and set inheritance too

Regards

Geetha

GeethaThatipatri-MSFT 29,477 Reputation points Microsoft Employee

2024-07-16T23:33:33.15+00:00

@Yichen Tian If this answers your question, please consider accepting the solution by hitting the Accept as Solution ‌‌as it helps the community look for answers to similar questions.

Regards

Geetha

Serpa 161 Reputation points

2024-07-22T14:32:33.6266667+00:00

Hey there, has this been fixed?

I'm finding very troublesome to configure the users hierarchy correctly because my admins don't have privileges for granting roles.

I can't understand the workaround very well.

I have AD authentication and password authentication enabled in my instance and I can't grant admin roles to anyone.

GeethaThatipatri-MSFT 29,477 Reputation points Microsoft Employee

2024-07-29T13:29:47.8633333+00:00

@Serpa Grant the user role with CREATEROLE attribute azure_pg_admin with ADMIN OPTION. This operation restores the ability to manage database roles and perform necessary operations.

Regards

Geetha

Roman Bange 5 Reputation points

2024-08-15T06:51:43.37+00:00

This is not helpful at all Geetha. Why can't you just provide a snippet as an example.

Also why was this not covered in the Upgrade process of the flexible server instance? There are likely a lot of users affected by this.

Inna Yurgensone (external) 0 Reputation points

2024-08-15T14:35:17.5933333+00:00

Hi Geetha,

I have tried to apply suggested workaround:

grant azure_pg_admin to myuser with admin option;

It does not work. Any other ideas?

Roman Bange 5 Reputation points

2024-08-15T14:42:07.81+00:00

I would like to note that this is the current permission state for an existing instance upgraded from 15 to 16.

Therefore I cannot do anything to fix the broken upgrade. In a newly created instance at least the db admin user has admin access here.

Serpa 161 Reputation points

2024-08-16T14:48:14.4733333+00:00

Hey guys,

We followed up with azure's support and they were able to fix the permission issues in our databases.

Forget what Geetha is saying, you need to have super user permissions in order to fix the cluster, and only azure's product group team has that.

You will need to escalate in the support levels and explain your issues multiple times, but at the end they fixed it for us.
Sign in to comment

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

Share via

Ability to grant admin user ADMIN option in Azure PostgreSQL Flexible Server version 16

0 additional answers

Your answer