Connecting a SQL database(PostgreSQL) to Power BI

A common Power BI workflow exports a database to Excel and loads the file into the report. Each export re-guesses data types, depends on someone remembering to run it, and produces a file that is only as current as the last manual step. Connecting Power BI directly to the database removes that intermediate layer: the report reads from the source, refreshes on a schedule, and inherits the database's types and access controls.

This article documents the full procedure for connecting a managed cloud PostgreSQL database, hosted on Aiven, to Power BI. The connection dialog takes a few minutes. The configuration around it a non-default port, a mandatory SSL certificate, and a database name that differs from the PostgreSQL default accounts for most connection failures and is covered in detail below.

The connection path

flowchart LR
    A[Aiven console:<br/>connection details] --> B[Install CA certificate<br/>for SSL]
    B --> C[DBeaver:<br/>test + load data]
    C --> D[Power BI:<br/>connect]
    D --> E[Power Query:<br/>transform]
    E --> F[Load model +<br/>schedule refresh]

The procedure verifies the connection in a SQL client (DBeaver) before configuring Power BI. This isolates the failure domain: if DBeaver connects and Power BI does not, the problem is in Power BI's configuration rather than the credentials, SSL setup, or network path.

Step 1 - Retrieve the connection details from Aiven

All required values appear on the service's Overview page, under Connection information.

Collect six items: host, port, database, username, password, and the CA certificate. Three of these differ from PostgreSQL defaults and are the most frequent cause of connection errors:

• Port. Aiven assigns a per-service port (12928 in this example), not the PostgreSQL default of 5432. Any client left on 5432 will fail to connect.
• Database. Aiven provisions a database named defaultdb, not postgres.
• SSL. The service requires SSL (sslmode=require) and rejects unencrypted connections. The CA certificate download on this page is required for the steps that follow.

On the free tier, a service powers off when idle. If the first connection attempt times out, confirm the service state in the console and start it before further diagnosis.

Step 2 - Configure SSL

The CA certificate allows the client to verify the server's identity confirmation that the connection terminates at the intended database rather than an impostor. Download the ca.pem file from the Aiven console. require provides encryption only.

For clients that read trust from the Windows certificate store, and to satisfy the stricter SSL modes system-wide, import ca.pem into Trusted Root Certification Authorities:

1. Press Win + R, enter certlm.msc, and confirm.
2. Expand Trusted Root Certification Authorities, right-click Certificates, and select All Tasks → Import.
3. In the wizard, select the downloaded ca.pem (set the file filter to All Files if it is not listed) and keep the store as Trusted Root Certification Authorities.

Step 3 - Verify the connection in a SQL client

In DBeaver, select Database → New Database Connection → PostgreSQL and enter the Aiven host, port 12928, database defaultdb, user avnadmin, and the password. On the SSL tab, enable SSL, set the CA certificate field to the downloaded ca.pem, and set the SSL mode to require or higher. Omitting the SSL configuration causes the connection to fail at the handshake.

Select Test Connection:

A successful test confirms that the host, port, credentials, and SSL configuration are correct; Power BI will use the same values. The round-trip time shown (approximately six seconds) reflects the cloud connection and any spin-up from a powered-off state on the free tier.

If the source data originates as a file rather than an existing table, load it into the database at this stage. In this example a CSV is imported via right-click the public schema → Import Data. Loading into PostgreSQL first gives the data a schema, makes it queryable by other tools, and allows Power BI to fold query steps back to the source.

Step 4 - Connect Power BI to the database

In Power BI Desktop, select Get Data → Database → PostgreSQL database. The first dialog requires the server, the database, and the connectivity mode.

• Server - the Aiven host including the port: pg-xxxx.aivencloud.com:12928. Without the port, the connector attempts 5432 and fails. This is the most common configuration error at this step.
• Database defaultdb.
• Data Connectivity mode Import for most reports. Import copies a snapshot into Power BI's in-memory engine, which gives the fastest queries and the full feature set. DirectQuery leaves the data in the source and issues a query for each visual; it keeps data current, but its performance depends on the source and it disables some Power Query and DAX features. Reserve DirectQuery for real-time requirements or datasets too large to import.

On the authentication screen, select Database on the left panel, enter the user (avnadmin) and password, and connect. A certificate trust error at this point indicates the CA certificate from Step 2 is not installed in the Windows Trusted Root store, which is where Power BI reads machine trust.

The PostgreSQL connector requires the Npgsql data provider. Recent Power BI Desktop builds include it. If a scheduled refresh succeeds in Desktop but fails in the Service, the provider is often missing on the gateway machine.

Step 5 - Transform the data in Power Query

Selecting Transform Data opens the Power Query Editor. A direct database connection guarantees a schema, not clean data; the transformation work still applies.

The imported jcars table (32 columns, 199+ rows) contains inconsistent data typical of accumulated source records. Order Date mixes formats (Aug 29, 2025, 2026/01/19, 26-Mar-2025). Delivery Date combines valid dates with invalid entries (46066, not sure). Customer Type contains unnormalized values (Ngo, Corporate, individual, GOVERNMENT, retail). At least one record holds an out-of-range date (2026-13-04).

The corresponding transformations:

• Remove columns not required by the report using Choose Columns to reduce the load.
• Filter rows to exclude test records and restrict the data to the reporting period.
• Change data types - set Order ID to text, parse the dates with Change Type → Using Locale to handle the mixed formats, and set amounts to a numeric type. Invalid dates surface as errors to be handled explicitly.
• Standardize values - merge a mapping table to consolidate Customer Type variants so that government, Government, and GOVERNMENT resolve to a single value.

Apply foldable steps (column selection, row filters, type changes) first so they push down to PostgreSQL as SQL and reduce the volume transferred. Confirm folding with right-click → View Native Query. Non-foldable custom M should follow.

After Close & Apply, the model work is independent of the source: build the star schema, add a date table, and define measures as DAX rather than calculated columns.

Total Orders   = COUNTROWS ( jcars )
Distinct Custs = DISTINCTCOUNT ( jcars[Customer Name] )
Orders YTD     = TOTALYTD ( [Total Orders], 'Date'[Date] )

Scheduled refresh

An Import model holds a snapshot and requires refreshing to stay current. Because Aiven is a cloud source reachable over the public internet, scheduled refresh from the Power BI Service generally connects directly and does not require an on-premises data gateway, subject to Aiven's IP allowlist. A gateway is needed only when the database resides in a private network the Service cannot reach; where one is used, install the Npgsql provider on it. For large, growing tables, configure incremental refresh so the date filter folds to the database and only recent rows reload.

Troubleshooting

The following table maps observed errors to their common causes. Where Power BI fails, reproducing the same connection in DBeaver identifies whether the fault lies in the credentials, SSL, or network (DBeaver also fails) or in the Power BI configuration (DBeaver succeeds).

Symptom	Common cause	Resolution
Connection timeout	Service powered off, wrong port, or firewall	Start the service; use port 12928; allow the port outbound
SSL handshake failure	SSL disabled or wrong mode	Enable SSL; set sslmode=require or higher; update the driver
Certificate verify failed	CA certificate not installed	Import ca.pem into Trusted Root
Authentication failed	Incorrect or expired password	Re-copy from Aiven; re-enter in the data-source settings
Connection refused	Wrong port or database, or IP not allowlisted	Correct the port and defaultdb; add the client IP to the allowlist
Succeeds in Desktop, fails in Service	Gateway missing the Npgsql provider, or stale stored credentials	Install the provider on the gateway; update stored credentials

Security

• Least privilege. Report with a read-only database user (GRANT SELECT) rather than the avnadmin account, so that a compromised reporting credential cannot modify or drop data.
• Service accounts. For scheduled refresh, use a team-owned service account rather than a personal login, so refreshes continue when staff change and the account's scope stays limited to reporting.
• Encryption in transit. Keep SSL enabled (require, or verify-ca/verify-full for certificate validation) on every connection. Configuring security after reports are in production is more difficult than establishing it at the outset.

Key configuration points

• Port is service-specific (12928), not 5432; database is defaultdb, not postgres.
• SSL is mandatory; download and install the CA certificate.
• Verify the connection in DBeaver before configuring Power BI.
• Enter the Power BI server as host:port, or the connector defaults to 5432 and fails.
• A database connection provides a schema, not clean data; Power Query transformations still apply.
• Use a read-only reporting user and keep SSL enabled from the initial setup.