Data sitting inside a database doesn't create value on its own, rather it becomes useful the moment you can visualize and analyze it. Power BI is one of the most popular tools for doing exactly that, and PostgreSQL is one of the most common databases feeding it.
In this article I'll walk you through connecting Power BI Desktop to PostgreSQL in two scenarios: a local database on your own machine, and a cloud database hosted on Aiven, including the SSL certificate step.
Part 1 — Connecting to a Local PostgreSQL Database
A local database is the simplest case as no encryption certificate is needed.
Step 1 — Get Data
Open Power BI Desktop → Home → Get Data → More… → Database → PostgreSQL database → Connect.
Step 2 — Enter the connection details
-
Server:
localhost(default port5432) - Database: your database name Leave the connectivity mode as Import and click OK
Step 4 — Load.
The Navigator opens showing your schemas and tables. Tick the tables you need, preview them on the right, then click Load or Transform Data to clean first in Power Query.
Part 2 — Connecting PowerBI to a Cloud Database on Aiven
Aiven is a managed cloud database service. Since your data now travels across the public internet, Aiven enforces SSL so before Power BI can connect, it has to trust Aiven's certificate.
Step 1 — Grab your Aiven connection details
Log in to the Aiven console, open your PostgreSQL service, and on the Overview tab copy the connection information:
-
Host: e.g.
pg-yourproject-xxxx.aivencloud.com -
Port: e.g.
10394 -
Database:
defaultdb -
User:
avnadmin - Password: ‘unique’
- SSL Mode: Required
Step 2 — Download the CA certificate
Still on the Overview page, find Download CA Certificate and save the ca.pem file on somewhere easy to reach, e.g. C:\certs\ca.pem.
This certificate is what encrypts the tunnel between Aiven and Power BI, so that anyone intercepting the traffic can't read your credentials or data.
Step 3 — Trust the certificate on Windows
This is the step that fixes the "remote certificate is invalid" error.
- Press Windows + R, type
certmgr.msc, and press Enter.
- Expand Trusted Root Certification Authorities → Certificates.
- Right-click Certificates → All Tasks → Import.
Browse to your ca.pem (change the file-type filter to All Files if you can't see it), select it, and finish the wizard.
- You'll get an Import Successful message. Restart Power BI so it picks up the change.
Step 4 — Connect Power BI to Aiven
Back in Power BI: Get Data → PostgreSQL database.
-
Server: enter host and port together as
host:port, e.g.pg-yourproject-xxxx.aivencloud.com:10394 -
Database:
defaultdb
- Under Advanced options, add the connection parameter
sslmode=require(orsslmode=verify-caif you want full certificate verification).
Click OK, choose Database authentication, and enter your Aiven username and password.
Step 5 — Load your tables
Because Windows now trusts the Aiven certificate, the SSL handshake succeeds and the navigator opens with your tables. Select what you need and click Load or Transform Data.
Your cloud database is now connected and because Aiven works over encrypted SSL directly, scheduled refresh in the Power BI Service works without an on-premises gateway.
In conclusion, connecting Power BI to PostgreSQL is straightforward once the driver and for the Cloud, the SSL certificate are in place. Local databases connect with localhost and standard credentials; Aiven adds one trust step, then behaves the same. From there you can model your tables, write DAX, and build dashboards that always reflect the latest data.
Top comments (0)