SQL Direct Access (Growth+)

Connect to the portfolIQ Star Schema via standard PostgreSQL — read-only access for psql, DBeaver, Power BI, Tableau, Metabase, and any ODBC/SQL client.

SQL Direct Access (Growth+)

Direct read-only PostgreSQL access to the portfolIQ Star Schema (21 views: 7 dimensions + 10 facts + 4 satellites). Available on Growth and Enterprise plans (scope star:agg).

Standard PostgreSQL protocol. portfolIQ does not ship certified native connectors for Power BI, Tableau, or other BI tools. All BI tool integrations use the standard PostgreSQL connector (ODBC/JDBC) available in each tool.

Read-only access. Constraints enforced per credential: statement_timeout = 5s, idle_in_transaction_session_timeout = 30s, connection limit 5.

Prerequisites

• Growth or Enterprise plan (scope star:agg required)
• An active API key — see Authentication

Step 1 — Provision your credentials

Send a POST to /v1/billing/sql-credentials with your API key. The plaintext password is returned exactly once and cannot be retrieved again.

curl -X POST https://api.portfoliq.io/v1/billing/sql-credentials \
  -H "X-API-Key: YOUR_API_KEY"

Response (201 — save immediately):

{
  "host": "db.portfoliq.io",
  "port": 5433,
  "database": "portfoliq",
  "schema": "star_public",
  "username": "sql_user_a3b7f2c1",
  "password": "AbCdEf1234GhIjKl5678MnOpQr90StUv",
  "expires_at": null,
  "note": "Save this password — it cannot be retrieved later"
}

Save your password now. It cannot be retrieved after this response. If you lose it, revoke the credential and create a new one.

Step 2 — Connect

psql

# Connection string (recommended)
psql "postgresql://sql_user_a3b7f2c1:YOUR_PASSWORD@db.portfoliq.io:5433/portfoliq?sslmode=require"

# Or with individual flags
psql \
  -h db.portfoliq.io \
  -p 5433 \
  -U sql_user_a3b7f2c1 \
  -d portfoliq \
  --set=sslmode=require

Once connected, the search path is already set to star_public via your role. You can verify with:

SHOW search_path;
SELECT * FROM dim_asset LIMIT 5;

DBeaver

1. New Connection > PostgreSQL
2. Host: db.portfoliq.io — Port: 5433 — Database: portfoliq
3. Username: sql_user_a3b7f2c1 — Password: YOUR_PASSWORD
4. Go to the SSL tab — set SSL Mode to require
5. Click Test Connection — confirm success
6. In the SQL editor, the active schema is star_public (set via your role)

Power BI Desktop

1. Get Data > More… > search PostgreSQL Database > Connect
2. Server: db.portfoliq.io:5433 — Database: portfoliq
3. Data Connectivity mode: Import (recommended) or DirectQuery for volumes < 1 M rows
4. Expand Advanced options > add SET search_path = star_public in the SQL statement field, or select the star_public schema from the navigator
5. Credentials: select Database authentication > enter username and password
6. Accept the SSL prompt (TLS is required)

Power Query tip: Filter by asset_id or date_day before loading to stay within the 5-second statement timeout.

Tableau Desktop

1. Tableau → Connect → To a Server → PostgreSQL
2. Server: db.portfoliq.io — Port: 5433 — Database: portfoliq
3. Username: sql_user_xxxxxxxx — Password: from credentials (Step 1)
4. Initial SQL (optional): SET search_path = star_public, public;
5. Require SSL: enable — TLS is required, non-TLS connections are rejected
6. Click Sign In

Tableau tip: statement_timeout = 5s means full table scans will be cancelled. Always filter by date range or asset_id in your Tableau data source filters before publishing.

Metabase

1. Metabase admin → Databases → Add database → PostgreSQL
2. Host: db.portfoliq.io — Port: 5433 — Database: portfoliq
3. Username: sql_user_xxxxxxxx — Password: from credentials (Step 1)
4. Schemas to sync: select star_public only — do not sync marts, dv, or raw
5. Use a secure connection (SSL): enable
6. Click Save — Metabase will sync the schema and make all 21 views available for browsing

Metabase tip: Read-only access is already enforced at the PostgreSQL role level — no additional configuration needed in Metabase permissions.

Lightdash

1. Lightdash project setup → Connect warehouse → PostgreSQL
2. Host: db.portfoliq.io — Port: 5433 — Database: portfoliq — Schema: star_public
3. Username: sql_user_xxxxxxxx — Password: from credentials (Step 1)
4. SSL: require
5. dbt integration: point Lightdash to a clone of the portfoliq-dbt package, or to your own dbt project that imports portfoliq-dbt via packages.yml — this gives you native field-level metadata, descriptions, and relationships directly in the Lightdash UI

Lightdash tip: Because portfoliq-dbt ships with description: blocks and relationships: tests, Lightdash picks up field documentation and join paths automatically once the dbt project is connected.

What you can query

21 views in schema star_public:

Limits

Optimize for the 5-second timeout: always filter by asset_id, date_day, or chain_id. Avoid full-table scans on fact tables.

Manage credentials

List your active credentials (no password returned):

curl https://api.portfoliq.io/v1/billing/sql-credentials \
  -H "X-API-Key: YOUR_API_KEY"

Revoke a credential:

curl -X DELETE https://api.portfoliq.io/v1/billing/sql-credentials/sql_user_a3b7f2c1 \
  -H "X-API-Key: YOUR_API_KEY"

A revoked PostgreSQL user is immediately dropped — existing connections are terminated.

Disclaimer

Data exposed via SQL access is for informational purposes only. Not financial advice. Not a fatwa. Methodology disclosed at portfoliq.io/methodology.