Can I change to owner of a database in Azure Database for PostgreSQL flexible server?

Question

I have a database created with the migration tool in the Azure Database for PostgreSQL flexible server. The migration tool sets the database owner as the server admin, and I want to change the owner to a different role. How do I do this? ALTER DATABASE xxx OWNER TO xxx is failing, reassigning the owner in PGAdmin4 is failing, and I cannot see a way to do this in the Azure portal.

Error: must be able to SET ROLE xxx

Answer 1

Hello Kristine Kisman,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Problem

I understand that you are having issue in changing the database owner from the server admin to a different role (Owner) in Azure Database for PostgreSQL flexible server.

Solution

To change the owner of a database in Azure Database for PostgreSQL flexible server, you will need to do the followings:

1. Use a PostgreSQL client like psql or a graphical tool like PGAdmin4 to connect to your PostgreSQL server with a user that has sufficient privileges, such as the server admin. psql -h your_server.postgres.database.azure.com -U server_admin -d postgres
2. If the new owner role does not already exist, create it with superuser privileges. CREATE ROLE new_owner WITH LOGIN SUPERUSER PASSWORD 'password';
3. If you don't want to create a superuser, ensure the new role has the required permissions or you can grant necessary permissions (if not using a superuser). ALTER ROLE new_owner WITH CREATEDB;
4. While connected as the current database owner (which is likely the server admin), execute the following command to change the Database Owner. ALTER DATABASE your_database_name OWNER TO new_owner;

If you encounter the error must be able to SET ROLE xxx, it indicates that the user you're using to change the ownership does not have the necessary privileges. Ensure you're connected as a superuser or a user with the necessary permissions to change database ownership.

Reality

For reality, it seems to be no direct option to change the owner via the Azure portal, if you've been using Azure Portal for the purpose. So, if you still face issues like above ensure the new_owner role is correctly configured with all required permissions and is able to login. Then the next is an Alternative Method.

If the above steps do not resolve the issue, you can try to:

• Create a New Database with the Desired Owner.
• Use pg_dump and pg_restore to copy the data from the old database to the new database.

Accept Answer

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam

Answer 2

Hello !

I finally manage to change the owner of my database in Azure Database for PostgreSQL flexible server, Postgres 16.3.

Just saying that it is really annoying how Azure gets in the way preventing you to perform any superadmin-like operation.

In my set up I had one database in my server called db1 which was owned by original_owner. I wanted to run something like ALTER DATABASE db1 OWNER TO new_owner but obviously Azure was in the way.

Note that in my case original_owner and new_owner were two admin accounts (Entra accounts to be precise, we disabled password login entirely on the server). By admin, I don't mean Postgres admin, but simply admin listed in the Azure portal Security->Authentication.

Here is my solution:

• Open a console(1) and log in with PGPASSWORD=$TOKEN psql -h myhost.postgres.database.azure.com -U new_owner -d db1
• Open another console(2) and log in with PGPASSWORD=$TOKEN psql -h myhost.postgres.database.azure.com -U original_owner -d db1
• In (1) CREATE ROLE temp_role WITH NOLOGIN ADMIN original_owner ROLE new_owner
• In (2) ALTER DATABASE db1 OWNER TO temp_role
• In (1) ALTER DATABASE db1 OWNER TO new_owner
• In (1) DROP ROLE temp_role