Permission Denied in table 'xxx' Issue After Migrating from Single Server to Flexible Server in Azure PostgreSQL

Waleed Asad 5

User's image

Hello Microsoft Support Team,

I am reaching out to request assistance with an issue I encountered after migrating my PostgreSQL database from Single Server to Flexible Server in Azure. After completing the migration, I am unable to execute queries, as I receive a "Permission Denied" error when trying to interact with the tables in the database.

The PostgreSQL flexible database is on v11.

Problem Description:

After migrating the database from Single Server to Flexible Server, I am encountering Permission Denied errors when running queries.

Actions Taken So Far:

I am able to connect to my flexible server.
Checked that no network or firewall rules are blocking access to the database.
Compared server parameters in single and flexible server.

Despite these actions, the issue persists, and I am unable to determine the exact cause of the permission denial.

Please let me know if you require any additional information from my end to investigate this issue further.

Thank you for your assistance, and I look forward to your prompt response.

Best,

Sai Raghunadh M 800 Reputation points Microsoft Vendor

2024-10-21T18:21:42.1633333+00:00

Hi @Waleed Asad,

Thanks for the question and using MS Q&A platform.

Are you running query for pg_catalog tables?
Waleed Asad 5 Reputation points

2024-10-22T07:50:54.2366667+00:00

Hi, I am running a query to get all the records in table e.g. Orders inside my database (i.e. catalog) but I get permission denied issue.
Sai Raghunadh M 800 Reputation points Microsoft Vendor

2024-10-22T11:40:22.83+00:00
Hi @Waleed Asad,

The pg_catalog schema is where PostgreSQL stores important metadata. Permissions vary between single server and flexible server environments:

In a flexible server environment, permissions for certain tables and views are restricted so that only superusers can query them.

Granting unrestricted access to system tables and views in the pg_catalog schema can lead to unauthorized modifications, accidental deletions, or even security breaches. Restricted access reduces the risk of unintended changes or data exposure.

For more information, please go through this documentation: pg_catalog schema permissions.

I hope this information helps, please do let us know if you have any Queries.
Waleed Asad 5 Reputation points

2024-10-22T12:16:06.4133333+00:00
Hi @Sai Raghunadh M ,

According to the documentation it says:

The migration service in Azure Database for PostgreSQL provides the following built-in capabilities for Azure Database for PostgreSQL - Single Server as the source and data migration:

Migrates user roles from your source server to the target server.

Migrates ownership of all database objects from your source server to the target server.

Migrates permissions of database objects like GRANT and REVOKE from your source server to the target server.

I have migrated everything from single (source) database including 'postgres' to flexible server (target) so I should be able to run queries on my flexible server but I cannot due to permission denied.

How do I fix this in my flexible server?

Previously Microsoft did the the migration automatically for one of my single to flexible server and I had not faced any issues with permission denied, I only faced it now when I am doing it manually.

Thanks
Waleed Asad 5 Reputation points

2024-10-22T17:19:09.7233333+00:00
Hi @Sai Raghunadh M ,

Once the flexible server connection string is added to the azure app service, I am facing the error above with the remote certificate.

Actions Taken So Far:

I am able to connect to my flexible server locally without any issue and able to run queries now.

Checked that no network or firewall rules are blocking access to the database.

Compared server parameters in single and flexible server and updated them.

Can you guide on to fixing it?

Thanks
Sai Raghunadh M 800 Reputation points Microsoft Vendor

2024-10-22T17:40:29.65+00:00

Hi @Waleed Asad,Did you used the correct host name? the host name must match the name on the certificate.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Waleed Asad 5 Reputation points

2024-10-22T18:00:51.8833333+00:00

Hi @Sai Raghunadh M ,I have not created any certificates or added anything in the azure app services. Can you please provide where this is mentioned in the post migration.

Please note that post migration I'd have expected the flexible sever work normally once the connection string is added in the azure app service.

This is an e.g. connection string I am using which is using my flexible server.

Server=xxxxflexible.postgres.database.azure.com;Port=5432;Database=xxxx;User Id=xxxxx;Password=xxxx;Ssl Mode=Require;

Thanks
Sai Raghunadh M 800 Reputation points Microsoft Vendor

2024-10-22T18:14:32.12+00:00

Hi @Waleed Asad,

By default, secured connectivity between the client and the server is enforced.

please go through this document for additional information: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-connect-tls-ssl.

I hope this information helps, please do let us know if you have any Queries.
Waleed Asad 5 Reputation points

2024-10-22T18:48:19.24+00:00

Hi @Sai Raghunadh M ,

The issue is something else as that didn't work, I need to keep ssl enabled for secured connection.

Can you check my database in my resource group on your end to ensure there are no errors as I am still facing the remote certificate issue in azure.

There was a postgresql database migrated by Microsoft into flexible server and that seems to work fine with my azure app service and it has the same server parameters compared to the other new flexible server which is not working.

Thank
Sai Raghunadh M 800 Reputation points Microsoft Vendor

2024-10-24T17:46:59.98+00:00

Hi @Waleed Asad,

Is your issue resolved?
Sai Raghunadh M 800 Reputation points Microsoft Vendor

2024-10-25T19:28:41.7666667+00:00

Hi @Waleed Asad,

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.

Share via

Permission Denied in table 'xxx' Issue After Migrating from Single Server to Flexible Server in Azure PostgreSQL

Your answer