Postgresql Database users lost permissions after azure migrated our server to azure database flexible server

Question

We have a PostgreSQL database server which has been working for over 3 years.

at one point between yesterday and today (22/10/24) azure made an automatic migration into azure database flexible server.

According to Q&A all the user information and credentials to access the server should've migrated to the new server, however, when we check access the users have no permissions and thus we can't do anything with the database.

All our applications have stopped working because of this.

This image shows our old database. (this one is still there but the host-name changed, because the original host-name was taken by the new flexible server)

This is the "new" flexible server which we can't use at all.

What can we do here to regain permissions for the new flexible server? or can we revert this operation?
Also, since both database servers are still up are we going to be charged double?

Answer 1

Hello Daniel Fajardo,

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

I understand that your PostgreSQL Database users lost permissions after azure migrated our server to azure database flexible server.

@Sai Raghunadh M, has given useful information about role migration in PostgreSQL, specifically related to the pg_catalog schema. Let now troubleshoot specific instructions to restoring access with more detailed steps not just a general guide.

During the migration from your old PostgreSQL server to the new Azure Database for PostgreSQL Flexible Server, user permissions might not have migrated correctly, which is why you're experiencing access issues.

1. Connect to the new flexible server using an admin account (such as the azure_superuser) and review the roles and privileges in the database. Execute the following query to list the roles and their privileges:

     SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin
     FROM pg_roles;

This will give you an idea of what roles currently exist and their permissions.

2. If you find that the necessary roles are missing privileges, you may need to manually reassign permissions. For example, to grant a role the necessary privileges, you can use:

     GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;

If your roles are not migrated, you might need to recreate them with the correct permissions.

3. About the double billing, since both the old and new servers are still up, you may be charged for both servers. It’s important to stop or delete the old server once you've verified that all data and permissions have successfully migrated to the new flexible server.
4. The documentation provided above by @Sai Raghunadh M, can help with understanding role migration and permissions management in Azure Database for PostgreSQL.
5. If the issue persists, contact Azure Support from your Azure Portal with specific error code and information.

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.