Dataflow connection to PostgreSQL

[a_kkk]

Hello everyone!

I am having trouble connecting our PostgreSQL database hosted on AWS to Power BI service dataflow generation 1 using encrypted connection. Our certificate is valid, and since db is on AWS, it should be trusted by Microsoft, right? 

Is there any workaround that will not require connection via gateway?

In case we upgrade to Fabric and use dataflow generation2, will that help to connect directly to database?

Thanks!

[Akash_Varuna]

Hi @a_kkk  Power BI Service Dataflow Gen 1 requires an on-premises gateway for encrypted connections to PostgreSQL, even if hosted on AWS with a valid certificate. As a workaround, use Azure Data Factory to sync data to Azure SQL or Blob Storage for direct connection. Upgrading to Microsoft Fabric (Dataflow Gen 2) supports more flexible, gateway-less connections to cloud-hosted databases. 

[a_kkk]

Thank you @Akash_Varuna . does this mean if I upgrade to Fabric, I'll be able to access directly AWS hosted postgres db from Dataflow Generation 2? 

[v-dineshya · Accepted solution]

Hi @a_kkk ,

Thank you for reaching out to the Microsoft Community Forum.

Thank you  @Akash_Varuna , for sharing your response. 

Hi @a_kkk , In addition to @Akash_Varuna  response, I am adding some more points.

Yes, upgrading to Microsoft Fabric and using Dataflow Generation 2 does improve support for direct, gateway-less connections to cloud-hosted databases like PostgreSQL on AWS.

1. Current Limitation (Dataflow Gen 1):
Power BI Dataflow Generation 1 requires an on-premises data gateway to connect to PostgreSQL, even if hosted on AWS with a valid SSL certificate.

Note: This is because Gen 1 is part of the legacy architecture and does not fully support cloud-native connections.

2. Upgrade to Microsoft Fabric (Dataflow Gen 2):
Dataflow Gen 2 is built on Microsoft Fabric and does not require a gateway for many cloud data sources.

For PostgreSQL: If your PostgreSQL instance is publicly accessible over the internet (with proper authentication and SSL setup), you can connect directly from Dataflow Gen 2. Microsoft Fabric leverages cloud-native connectors, and PostgreSQL is supported natively.

Gateway-less Connection: To connect to your AWS-hosted PostgreSQL instance from Fabric (Dataflow Gen 2):
Ensure the DB instance allows public access. Use SSL/TLS encryption. Whitelist IP ranges if your AWS security group restricts access. Use a PostgreSQL username/password or other supported credentials.

If you stick on Gen 1:

If you don't want to use a gateway or upgrade: Sync your AWS PostgreSQL data to a cloud-native store like: Azure SQL Database and Azure Blob Storage, Then connect Power BI Service directly to those, as they don’t need a gateway.

Please refer communtiy threads and Mircosoft articles.

Solved: Issue connecting Power BI postgreSQL (Amazon RDS) - Microsoft Fabric Community

Solved: Major problem: Can't connect to PostgreSQL in new ... - Microsoft Fabric Community

Solved: Issue with Power BI Online Connection to Azure Pos... - Microsoft Fabric Community

Solved: Connect Desktop Power BI to AWS RDS postgreSQL - Microsoft Fabric Community

Solved: Connecting to PostgreSQL server credential / certi... - Microsoft Fabric Community

Dataflows Limitations, restrictions and supported connectors and features - Power BI | Microsoft Lea...

Power Query PostgreSQL connector - Power Query | Microsoft Learn

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[a_kkk]

Thank you!