After Postgresql flexible migration from version 11 to version 16 admin user cannot change ownership of database

Question

After Postgresql flexible migration from version 11 to version 16 admin user cannot change ownership of database

Ryszard Sikora 0

Since the migration of the PostgreSQL Flexible version I am unable to ALTER ROLE and change database owner (ALTER DATABASE db OWNER TO new_role;)

When running this command:

ALTER DATABASE db1 OWNER TO new_role;

I am getting the following error:

ERROR: must be owner of database prefectdb SQL state: 42501

Moreover, I'm also unable to grant my admin user permissions to the old owner. When trying this command:
grant old_role to admin_user;

I am getting the following error:

ERROR: permission denied to grant role "prefect" Only roles with the ADMIN option on role "prefect" may grant this role. SQL state: 42501 Detail: Only roles with the ADMIN option on role "prefect" may grant this role.

The problem is that we cannot modify this role or change the owner of the database nor rollback to earlier version of PostgreSQL. Do you have solution for that problem?

Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-03-20T09:18:53.8866667+00:00

Hi @Ryszard Sikora

Greetings!!

It seems like you're encountering a permissions issue after migrating PostgreSQL from version 11 to version 16. This is likely due to changes in how roles and permissions are handled in newer PostgreSQL versions.

Ensure that the role you're using has the necessary privileges. You can check the privileges of the roles using the \du command in psql.

Please refer to this thread link that might help you:

https://learn.microsoft.com/en-us/answers/questions/1695303/can-i-change-to-owner-of-a-database-in-azure-datab.

Please reach out to us if you need more information.
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-03-21T08:28:23.7233333+00:00

Hi @Ryszard Sikora

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.

Ryszard Sikora 0

Hello!

I'm using user which is a server admin account with the following permissions:

{pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,azure_pg_admin,azure_pg_admin}

 
```I've tried the Solution from the link, but it gives the following error at step 4:

> Only roles with the ADMIN option on role "prefect" may grant this role.

PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-03-24T12:00:42.0733333+00:00
Hi @Ryszard Sikora

The Superuser Role in PostgreSQL has unrestricted privileges, allowing full control over the database. Superusers can modify server-level configurations, manage all roles (including granting superuser privileges), and bypass security checks.

A superuser role is required to change database ownership or perform certain other high-level operations in PostgreSQL. Since the Azure Database for Postgres flexible server is a managed service by Microsoft and the superuser privileges are also managed by Microsoft, this is why you're encountering permission issues when trying to modify ownership of a database or perform other administrative tasks.

To resolve this, you can raise support ticket as below:

Then it will take you to possible solutions come back to support request. then go to next and give your details and create support ticket.
PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-03-25T03:30:36.76+00:00

Hi @Ryszard Sikora

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.
Ryszard Sikora 0 Reputation points

2025-03-25T07:10:57.8466667+00:00

We are unable to create a ticket with a Basic Support plan
Ryszard Sikora 0 Reputation points

2025-04-01T07:38:10.63+00:00

How should we fix this?
Ryszard Sikora 0 Reputation points

2025-04-04T11:52:59.2+00:00

I still haven't received any support from Microsoft...

2 answers

Your answer

Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-03-20T09:18:53.8866667+00:00

Hi @Ryszard Sikora

Greetings!!

It seems like you're encountering a permissions issue after migrating PostgreSQL from version 11 to version 16. This is likely due to changes in how roles and permissions are handled in newer PostgreSQL versions.

Ensure that the role you're using has the necessary privileges. You can check the privileges of the roles using the \du command in psql.

Please refer to this thread link that might help you:

https://learn.microsoft.com/en-us/answers/questions/1695303/can-i-change-to-owner-of-a-database-in-azure-datab.

Please reach out to us if you need more information.
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-03-21T08:28:23.7233333+00:00

Hi @Ryszard Sikora

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.
Ryszard Sikora 0 Reputation points

2025-03-21T13:44:53.4233333+00:00

Hello!

I'm using user which is a server admin account with the following permissions:

{pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,azure_pg_admin,azure_pg_admin} ```I've tried the Solution from the link, but it gives the following error at step 4: > Only roles with the ADMIN option on role "prefect" may grant this role.
PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-03-24T12:00:42.0733333+00:00

Hi @Ryszard Sikora

The Superuser Role in PostgreSQL has unrestricted privileges, allowing full control over the database. Superusers can modify server-level configurations, manage all roles (including granting superuser privileges), and bypass security checks.

A superuser role is required to change database ownership or perform certain other high-level operations in PostgreSQL. Since the Azure Database for Postgres flexible server is a managed service by Microsoft and the superuser privileges are also managed by Microsoft, this is why you're encountering permission issues when trying to modify ownership of a database or perform other administrative tasks.

To resolve this, you can raise support ticket as below:

Then it will take you to possible solutions come back to support request. then go to next and give your details and create support ticket.
PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-03-25T03:30:36.76+00:00

Hi @Ryszard Sikora

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.
Ryszard Sikora 0 Reputation points

2025-03-25T07:10:57.8466667+00:00

We are unable to create a ticket with a Basic Support plan
Ryszard Sikora 0 Reputation points

2025-04-01T07:38:10.63+00:00

How should we fix this?
Ryszard Sikora 0 Reputation points

2025-04-04T11:52:59.2+00:00

I still haven't received any support from Microsoft...

Answer 1

Ryszard Sikora 0

The support has fixed my problem

Answer 2

Hi @Ryszard Sikora

Current admin user is emtrailsadmin as it has ADMIN OPTION on azure_pg_admin.

To add another admin user: log in as an admin user (e.g., emtrailsadmin).

Execute below command:

GRANT azure_pg_admin TO new_admin_user WITH ADMIN OPTION;

To grant a member role to another role, the current logged in user must have admin option on the member role. So for example if you are trying to execute GRANT role_a TO role_b; and you are logged in as role_c, role_c MUST have been granted role_a WITH ADMIN OPTION.

Admin users can grant themselves any new role with ADMIN OPTION
To change ownership of the databases
- Log in as emtrailsadmin
- Ensure emtrailsadmin has ADMIN OPTION on the role you are changing the database ownership to. Can add this using GRANT role TO emtrailsadmin WITH ADMIN OPTION;
- Execute ALTER DATABASE database OWNER TO role;

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

After Postgresql flexible migration from version 11 to version 16 admin user cannot change ownership of database

2 answers

Your answer