Azure Database for PostgreSQL
An Azure managed PostgreSQL database service for app development and deployment.
1,431 questions
Can't change the owner of a table in azure database for postgreSQL flexible server
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 22%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYJ%3C/text%3E%3C/svg%3E)
Yannic Jörg
5
Reputation points
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.
Azure Database for PostgreSQL
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(220.8, 47%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMS%3C/text%3E%3C/svg%3E)
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator2025-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
psqlor 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_ownerrole 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.
- Use a PostgreSQL client like
-
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator
2025-03-03T04:55:36.5866667+00:00 Hi Yannic Jörg
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
-
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator
2025-03-04T02:44:11.51+00:00 Hi Yannic Jörg
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment
Sign in to answer