How to Connect PostgreSQL to Power BI Using Local PostgreSQL and Aiven

Power BI is one of the leading business intelligence tools for analyzing and visualizing data. It provides multiple ways to load data, including direct connections to databases. This article explains how to connect Power BI to both a local PostgreSQL database and a cloud-based PostgreSQL database hosted on Aiven, a fully managed data platform for open-source databases.

Connecting to a Local PostgreSQL Database

Before connecting Power BI to your local database, ensure that:

• PostgreSQL is installed and running on your machine.
• You can successfully connect to the database using a tool like DBeaver.

Step 1: Note down connection details

1. Open DBeaver.
2. Right-click your database connection and select Edit Connection.
3. Note the following details:

Setting	Example
Host	localhost
Port	5432
Database Name	postgres
Username	postgres
Password	your_password

• You will use these details in Power BI.

Step 2: Connect Power BI

• Open Power BI Desktop and click blank report.
• Navigate to Home → Get Data → PostgreSQL database.

• Enter the server (localhost) and database name (postgres) and click OK.

• Enter your authentication credentials (username and password) and click Connect.

• In the navigator pane, select the tables you wish to import (e.g., patients, appointments) and click Load.

You have now successfully made the connection.

Connecting to Aiven PostgreSQL

Aiven is a cloud-based data platform that provides fully managed open-source databases, streaming systems, and analytics services. Unlike a local database, connecting to Aiven requires additional steps because of network and security configurations.

Prerequisites

Before connecting, ensure you have:

• An active Aiven PostgreSQL service.
• Connection details: host, port, database name, username, password.
• The SSL certificate bundle (ca.pem) downloaded from Aiven and installed. ( Steps on how to do this are described below)
• Power BI Desktop installed.
• Optional: DBeaver for testing the connection.

Step 1: Retrieve Aiven Connection Details

1. Log into your Aiven dashboard.
2. Select your PostgreSQL service.
3. Navigate to Connection Information and copy the host, port, database name, username, and password.

Step 2: Test Connection (Optional but Recommended)

1. Open DBeaver.
2. Click New Connection → PostgreSQL.
3. Enter the connection details:

Setting	Example
Host	Aiven host
Port	Aiven port
Database Name	Database Name
Username	Aiven user
Password	Aiven password

• Click Test Connection. If successful, your settings are correct.

Step 3: Connecting to power BI

For the connection to work, you need to add the Aiven CA certificate to the Trusted Root Certification Authorities store on Windows so Power BI Desktop can connect successfully.

How to Add the Aiven CA Certificate to Trusted Root Certification Authorities (Windows)

1: Download the CA Certificate from Aiven

• Log in to Aiven Console
• Go to your PostgreSQL service
• Under Connection Info, download the CA Certificate (ca.pem)

• Save it somewhere easy to find (e.g. Desktop or Downloads).

2: Rename the File (optional but helps)

• Right-click the file → Rename
• Change from: ca.pem to: aiven-ca.cer

3: Install the Certificate

• Double-click the aiven-ca.cer file
• Click Install Certificate...

• Select Local Machine (important). If asked, click Yes for Admin rights

• Choose: Place all certificates in the following store

• Click Browse..., select: Trusted Root Certification Authorities

• Click Next → Finish

You should see: "The import was successful."

After adding the CA certificate to the Trusted Root store, Power BI Desktop will trust the connection to Aiven PostgreSQL and stop throwing SSL validation errors

Step 4: Connect Power BI

• Open Power BI Desktop.
• Open a new or existing report.
• Navigate to Home → Get Data → PostgreSQL database.

• Enter the Aiven host and port in the server field (host:port) and the database name and click Connect.

• Enter your authentication credentials (username and password) and click Connect.

• In the navigator pane, select the tables you wish to import (e.g., patients, appointments) and click Load.

By following these steps, you can seamlessly connect Power BI to both local and cloud PostgreSQL databases for analytics and visualization.