How to Connect PostgreSQL to Power BI

Galaxy Glossary

How do I connect PostgreSQL to Power BI?

Use Power BI’s native PostgreSQL connector (or ODBC) to pull data, run SQL, and refresh reports.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why choose the native PostgreSQL connector?

The built-in connector ships with Power BI Desktop. It handles SSL, imports metadata, and supports DirectQuery or Import modes without extra drivers from v13 onward.

What prerequisites must be in place?

Enable TCP port 5432 on your firewall, install the latest Npgsql driver (if Desktop prompts for it), and create a read-only "powerbi_reader" role in your database.

How do I build the connection string?

Format: postgresql://user:password@host:port/database.Example: postgresql://powerbi_reader:s3cret@db.company.com:5432/ecommerce.

How do I connect in Power BI Desktop?

Home → Get Data → Database → PostgreSQL. Enter server (db.company.com:5432) and database (ecommerce). Select Import (for caching) or DirectQuery (for live queries). Click OK.

Can I pass a custom SQL statement?

Yes. In the connector dialog, expand Advanced Options and paste your SQL, e.g., SELECT id, name, price FROM Products WHERE stock < 10.Power BI will treat it as a single table.

How do I schedule refreshes in the Power BI Service?

Install an On-Premises Data Gateway on a machine that can reach PostgreSQL.In the Service, add a new data source, enter the same credentials, and enable scheduled refresh.

How do I secure credentials?

Create a limited role:

CREATE ROLE powerbi_reader LOGIN PASSWORD 's3cret';
GRANT CONNECT ON DATABASE ecommerce TO powerbi_reader;
GRANT USAGE ON SCHEMA public TO powerbi_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO powerbi_reader;

What performance tips matter most?

Use views to pre-aggregate, enable indices on join keys, and prefer Import mode for heavy dashboards.For DirectQuery, keep visuals under eight queries to avoid timeouts.

How do I troubleshoot connection errors?

1) Verify SSL = require vs disable in postgresql.conf. 2) Check pg_hba.conf allows the gateway’s IP. 3) Ensure Npgsql driver ≥ 5.0 is installed if using older Desktop builds.

.

Why How to Connect PostgreSQL to Power BI is important

How to Connect PostgreSQL to Power BI Example Usage


SELECT
    o.id            AS order_id,
    c.name          AS customer_name,
    o.order_date,
    SUM(oi.quantity * p.price) AS order_total
FROM Orders o
JOIN Customers  c ON c.id = o.customer_id
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products    p ON p.id = oi.product_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.id, c.name, o.order_date
ORDER BY o.order_date DESC;

How to Connect PostgreSQL to Power BI Syntax


Connection string syntax:
postgresql://<username>:<password>@<host>:<port>/<database>?sslmode=<disable|require|verify-full>

Example (ecommerce DB):
postgresql://powerbi_reader:s3cret@db.company.com:5432/ecommerce?sslmode=require

Required parameters:
  • host – PostgreSQL server (FQDN or IP)
  • port – default 5432
  • database – target DB (ecommerce)
  • username – least-privileged Power BI login
  • password – strong password
Optional parameters:
  • CommandTimeout – seconds before query aborts
  • SearchPath – comma-separated schemas
  • SSLMode – disable, allow, require, verify-ca, verify-full

Common Mistakes

Using a superuser account. This exposes write privileges and security risks. Fix by creating a read-only role with SELECT-only permissions.
Leaving DirectQuery as default for large tables. DirectQuery fires live SQL on every visual, causing slow reports. Switch to Import or pre-aggregate views.

Frequently Asked Questions (FAQs)

Do I need to install a driver?

Power BI Desktop 2022 and later bundle Npgsql. Older versions prompt you to download it once. The Service requires only the data gateway.

Can I parameterize SQL in Power BI?

Yes. Define Power Query parameters (Home → Manage Parameters) and reference them in your SQL using string concatenation before loading the data.

Is SSL mandatory?

Microsoft recommends SSL. Set sslmode=require in the connection string or enable “Encrypt connection” in the connector dialog.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie