The Ultimate Step-by-Step Guide: Connecting Power BI to Cloud-Based & Local PostgreSQL

Introduction

Whether your data is securely stored in a file on your desktop or is being streamed from a cloud-based platform on Aiven, establishing a connection to Power BI is a fundamental skill for the modern data analyst. Connecting Power BI to a database shouldn't feel like guesswork. This article provides a comprehensive, step-by-step guide for linking Power BI Desktop to two distinct environments: a local PostgreSQL instance localhost:5432 and a cloud-hosted Aiven PostgreSQL deployment secured by an SSL ca.pem Certificate.

Connecting Power BI Desktop to a Locally Hosted PostgreSQL Database

Before opening Power BI, it's important to ensure that the local database is running and note the credentials.
The credentials used should be as follows;

• Host: localhost or 127.0.0.1
• Port: 5432
• Database Name: The specific name of your database for example postgres or your custom project database.
• Username: Usually postgres
• Password: The password you created when installing PostgreSQL on your laptop

Establishing the Connection in Power BI Desktop.

1. Launch Power BI Desktop.
2. On the Home ribbon tab, click Get Data > select More
3. Select Database from the left categories > click on PostgreSQL database > click Connect. As shown in the image below.

1. In the connection window, fill out the database credentials as follows:
2. Server: Type Host:Port, e.g., 127.0.0.1:5432
3. Database: Type the name of your local database e.g., postgres
4. Data Connectivity Mode: Select Import
5. Click OK as shown below.

Input your local database credentials

A credential window will pop up asking how you want to access the database.

• Select the Database tab on the left margin.

Enter your local credentials:

• User name: Type postgres (or your specific local username)
• Password: Type your local database password.
• Click Connect Because local connections do not typically use an SSL certificate, Power BI might show a warning message saying: The data source doesn't support an encrypted connection.
• Simply click OK or Run Unencrypted to bypass this.

Since the data is residing on your own laptop, it does not travel across the internet, making an unencrypted local stream safe.

A Navigator window will open showing all the schemas and tables inside your local database. You can now pick the dataset you wish and proceed to transform in Power Query.

Connecting Power BI Desktop to a Cloud Hosted PostgreSQL Database on Aiven.

Power BI Desktop utilizes the underlying Windows Certificate Store to validate encrypted database links. Because Aiven uses its own to secure cloud data, Windows will block the connection as "Untrusted" unless we add it to the laptop's root directory.

To do this, we must first download the CA certificate from the Aiven console.
Open your web browser, navigate to your Aiven Console, and click on your running PostgreSQL service.
Stay on the Overview page and scroll down to the Connection information section.
Locate the row labeled CA Certificate and click the Download button next to it.

This will save a file named ca.pem directly to the laptop's Downloads folder.

Install it into the Windows Trusted Root Authorities.

To make this certificate trusted by Power BI for all connections, you must install it at the local machine.

• In the search box, search for "Manage user certificates.
• In the left-hand folders console, expand Certificates.
• Right-click on the Trusted Root Certification Authorities folder > hover over All Tasks > click Import

The Certificate Import Wizard will open.

• Click Next → Browse to find your file.
• Crucial Step: In the file browser pop-up, change the file extension filter dropdown in the bottom right to All Files. Otherwise, your ca.pem file will stay hidden.
• Navigate to your Downloads folder, select ca.pem, and click Open.
• Click Next.
• Ensure the option Place all certificates in the following store is selected and if it explicitly targets the Trusted Root Certification Authorities store.
• Click Next.

Review the summary page and click Finish.

A pop-up confirming "The import was successful" will appear.
Close the console.

Establishing the Connection in Power BI Desktop

Now that your laptop recognizes Aiven as a trusted authority, you can complete the link securely.
Launch Power BI Desktop.

• On the top Home ribbon, click Get Data → select More → choose Database → select PostgreSQL database.
• Click Connect.

Input your parameters copied from your Aiven Console:

• Server: Enter your Host name followed by a colon and the port number (e.g.,pg-123a4b5c-my-project.aivencloud.com:12345)
• Database:Type defaultdb

Data Connectivity Mode

• Select Import.
• Click OK.

When the database authentication window pops up, click the Database tab on the left-side margin.
Input your database user details.

• User name: avnadmin
• Password: Paste your specific Aiven service password.
• Click Connect

A Navigator canvas window will appear displaying your database contents.

Simply check the box next to your loaded table (jcars in my case) and click Transform Data to start transforming it within Power Query

In Conclusion ...

At the end of the day, a data analyst's value isn't just in making pretty charts but in building robust, dependable data infrastructure. Moving your pipelines onto PostgreSQL and securing them via trusted CA root certificates proves you understand high-level data security and database operations.
And that’s a wrap! You now have a fully functional, end-to-end data pipeline running seamlessly from a local environment or an encrypted cloud database straight into Power BI. No more connection timeouts, no more hidden SSL certificate rejections, and no more headaches.