Azure PostgreSQL flexible server: Unable to transfer ownership of table

Question

We are using posgres 16.0.

When trying to transfer ownership of a table with

ALTER TABLE schema.table OWNER TO "new_role"

We get error: must be able to SET ROLE "new_role"

We are logged in as azure_pg_admin user and have confirmed this with

SELECT current_user

Answer 1

@Peter Koller 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. For example, they can now change the CREATEDB, REPLICATION, and BYPASSRLS properties only if they also have those permissions.

https://www.postgresql.org/docs/16/release-16.html

Please follow the below steps here

Regards

Geetha

Answer 2

The error implies you cannot SET the new role using the server admin.

The server admin is granted the new role with ADMIN option only on Flexible servers, but not SET.

So, try:

GRANT 

And then try to alter ownership of objects.

You can then revert this:

REVOKE new_role FROM server_admin_user; 

And you would still retain the ADMIN option as it is granted to you by azuresu super user.

You can easily check these things in psql using \du and \drg.