Resolving Permission Denied Errors for pg_authid During PostgreSQL Migration to Azure Flexible Server

Question

How can I resolve permission denied errors related to pg_authid and other restricted tables when migrating from a single server to an Azure PostgreSQL Flexible Server?

PS - Based on common issues that we have seen from customers and other sources, we are posting these questions to help the Azure community

Answer 1

Greeting!

When migrating from a single server to an Azure PostgreSQL Flexible Server, you might encounter permission denied errors related to restricted tables like pg_authid. This happens because the Flexible Server restricts access to certain system tables (e.g., pg_config, pg_authid), unlike the single server where such permissions might be granted. To resolve this issue, follow these steps:

 

1. Revoke access to the users for the restricted tables.
2. Recreate the roles and users on the target server manually after the migration.

 

However, if your single server is a production server and you cannot change the roles directly, you should:

1. Create a latest restore of the single server.
2. Perform the migration on this restored version by revoking access and recreating roles as needed.

 

This approach ensures that the migration can proceed without encountering permission issues. For more detailed steps, refer to the following documentation:

 

• [Migration of users/roles, ownerships, and privileges - Azure Database for PostgreSQL - Flexible Server]( Migration service - Migration of users/roles, ownerships, and privileges - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn)
• [Backup and restore - Azure Database for PostgreSQL - Flexible Server]( Backup and restore - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn)

 

Hope this helps. If you have any follow-up questions, please let me know. I would be happy to help.

 

Please do not forget to ""up-vote" wherever the information provided helps you, as this can be beneficial to other community members.

Regards

Geetha