Can't change the owner of a table in azure database for postgreSQL flexible server

Yannic Jörg 0

I can't change the owner of a table even though I am using the server admin.
The error message is 'must be able to SET ROLE "ROLE NAME REDACTED"'. Is there any way to give the server admin the needed permissions? Since it is a managed server, I can't promote the server admin to a super user.

Mallaiah Sangi 85 Reputation points Microsoft Vendor

2025-02-28T17:04:30.0933333+00:00
Hi Yannic Jörg

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

As we understand that, you need to change the owner of the existing table. In order to perform this activity, you should have super user access or requires membership in a specific role.

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

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

If the new owner role does not already exist, create it with superuser privileges. CREATE ROLE new_owner WITH LOGIN SUPERUSER PASSWORD 'password';

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;

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;

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.

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Can't change the owner of a table in azure database for postgreSQL flexible server

Your answer