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

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