PostgreSQL

DSN

warehouse:
  driver: postgres
  dsn: postgres://reader:${WAREHOUSE_PASSWORD}@warehouse:5432/analytics?sslmode=require
  allowed_users: [analysts]
  timeout: 30s

Standard libpq URI format. Common parameters:

Parameter	Meaning
sslmode	disable, require (default for prod), verify-full
application_name	shows up in pg_stat_activity; defaults to plotpress/<version>
connect_timeout	TCP connect timeout in seconds
search_path	overridable schema search path

Authentication

Use a dedicated read-only role with GRANT SELECT only on the views/tables PlotPress should reach:

CREATE ROLE plotpress_reader LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA reporting TO plotpress_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO plotpress_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
  GRANT SELECT ON TABLES TO plotpress_reader;

The DSN password reads from ${WAREHOUSE_PASSWORD} at boot.

Views (primary path)

A “view” is whatever SELECT * FROM <name> returns: a regular VIEW, a MATERIALIZED VIEW, or a regular table. Plot blocks reference it by name:

Plot.barY(data, { x: "month", y: "revenue" })

Parameters become Postgres bind parameters. PlotPress runs:

SELECT * FROM monthly_revenue WHERE year = $1

Views are the strongly preferred form: business logic stays in SQL the database team can review, version, and reuse.

Fallback: queries/*.sql

When a view doesn’t exist (yet) for the slice you need:

-- queries/monthly_revenue.sql
-- @param year int default 2026

select
    date_trunc('month', invoice_date) as month,
    sum(amount)                       as revenue,
    currency
from invoices
where extract(year from invoice_date) = :year
group by 1, 3
order by 1;

:name is rewritten to Postgres’s $N at execution. See Authoring → Queries as fallback for the full convention.

Caveats

• Read-only enforcement. read_only: true (default) rejects anything that isn’t a SELECT or WITH ... SELECT at parser level, before it reaches Postgres.
• pg_typeof to debug column types. PlotPress maps Postgres types to JSON types; if a chart sees strings instead of numbers, check the view’s column type, not the dashboard.
• Pool tuning. Default max_open: 10 is enough for a few-user dashboard. Bump for high-fanout pages.
• Prepared statements. Used by default via pgx. Reset the pool with POST /admin/reset-pool/<connection> if a deploy changes a view’s signature.