Skip to main content
The operational database AEs pull from. Namespace: database.schema.table (one connection, one database; the first component is always the connected database). Cost paradigm: database load, expressed as database-seconds. Read-only against data, enforced in depth.

​
Authentication: discover, don’t ask

The engine discovers a connection at runtime and never prompts for or persists a password. Discovery order:
  1. postgres.service in .dex/config.yml, naming a pg_service.conf entry (PGSERVICEFILE, else ~/.pg_service.conf)
  2. DATABASE_URL (the twelve-factor URL most app stacks already export)
  3. the PG* environment (PGHOST/PGDATABASE/PGSERVICE and friends), resolved natively by libpq, including ~/.pgpass
  4. the committed non-secret postgres.host/port/dbname/user config target (password still supplied by PGPASSWORD or ~/.pgpass)
  5. the host of a type: postgres target in a discovered dbt profiles.yml
Only a coarse method (for example database_url:password or environment:external) is ever surfaced; the credential kind is password when one is inline and external for everything else (.pgpass, peer or trust auth, SSL client certificates), deliberately coarse. DSNs, identities, and passwords never cross the envelope (the sanitizer additionally strips user:pass@host URL fragments). Every discovery failure names all the fixes.

​
Config

Point dex at a read-only role (or better, a read replica) rather than a superuser on the primary; the documented grant shape is USAGE on source schemas plus SELECT on their tables, with write access only on the dedicated dev schema for the dbt role. scripts/postgres_seed.sql shows the shape (dex_ro and dbt_dev roles).

​
Cost model: load, not dollars

Postgres bills nothing, but dex is usually pointed at a production OLTP primary, and an unbounded scan there is a real cost: it evicts cache, holds back vacuum, and steals cycles from the application. So the guarded quantity is database-seconds, gated through the same strict handshake as every metered connector.
  • Free: connect test, explore inventory, all schema facts (pg_catalog lookups, no table scans), pg_stats reads, EXPLAIN estimation, and the free axes of maintain check (schema and volume read catalog metadata only).
  • Metered: profiling aggregates, explore query, relationship verification probes, distinct-count escalations, and transform build.
explore query and explore cluster bill an auto-profile of an object this connection has that the .dex/ cache cannot adjudicate, priced into the same handshake as the statements: see �3. Pass --no-auto-profile (or set auto_profile: false in .dex/config.yml) to be refused instead. Budgets (budget.ceiling, --budget, budget.session_ceiling) are database-seconds: the number you budget is the number the server enforces. Estimates are a heuristic, honestly labeled. Query estimates come from the genuinely free planner preflight (EXPLAIN (FORMAT JSON)), so an index-served lookup is not quoted as a full scan; plan cost translates to seconds through a deliberately conservative scan rate, and profile estimates come from relation sizes over the same rate. Every handshake payload carries estimate_quality: "heuristic". The budget is hard-enforced regardless of estimate quality. Before every metered statement the session’s statement_timeout is set to the remaining budget, so a wrong heuristic cannot overrun the ceiling: Postgres kills the statement and dex reports the over-ceiling refusal. Actual spend is wall-clock seconds per statement (a killed statement still bills what ran), recorded as billed_seconds in the ledger. Every session connects as application_name = 'dex' for attribution in pg_stat_activity. Budgets here are database-seconds; the handshake itself is in �0.

​
Read-only, enforced in depth

  • The session sets default_transaction_read_only = on before any statement, so the server itself refuses writes (SQLSTATE 25006) even if every other guard were bypassed. Connections are autocommit, so no idle-in-transaction session ever holds back vacuum on the primary.
  • Every data statement passes the SELECT-only guard parsed in the postgres dialect through one execution door (DML, DDL, COPY, and multi-statement forms all refuse).
  • The adapter issues no mutating statements: catalog SELECTs, EXPLAIN, and session SETs only.
  • The documented grant shape is a least-privilege read-only role; dbt dev builds write to dev_schema (default dbt_dev), which is refused as a source, and the generated profile renders one thread and an env_var password reference, never a value.

​
Profiling behavior

Profiling is deliberately light on the primary. The metered batch is one cheap single-pass scan per 50 columns: COUNT(*), per-column non-null counts, and min/max only for engine-cleared safe columns. Distinct counts come free from the planner’s own statistics (pg_stats.n_distinct, scaled by the exact row count when negative); the value-carrying statistics columns (most_common_vals, histogram_bounds) are never read. A statistics estimate is never a uniqueness verdict: near-unique keys escalate to an exact COUNT(DISTINCT) inside the confirmed budget through the standard escalation flow, and that scan also upgrades the row count from the reltuples estimate to an exact figure, so uniqueness proofs and grain verdicts compare against real rows. Freshness caveats, surfaced as data-quality notes rather than silently thinner numbers: a never-analyzed table has no row estimate and no distinct estimates (the note names ANALYZE as the fix), and maintain volume on unprofiled tables compares planner estimates whose accuracy tracks autovacuum’s ANALYZE cadence. json, jsonb, arrays, bytea, xml, tsvector, and the geometric and PostGIS types degrade to non-null counts. Tables above postgres.max_full_profile_bytes are profiled from a block sample (TABLESAMPLE SYSTEM), noted, with uniqueness not judged.

​
dbt builds

transform init renders a type: postgres dev profile from the discovered connection: host, port, user, and dbname from whichever source won discovery, schema pinned to the dedicated dev schema, one thread, and password: "{{ env_var('PGPASSWORD', '') }}" (the empty default keeps ~/.pgpass and peer auth working when no variable is set). dbt has no dry-run, so transform build cannot be estimated upfront; the ceiling and confirmation still bind, and the ceiling is injected into the dbt subprocess as PGOPTIONS="-c statement_timeout=<ceiling>s", the per-statement server-side cap (the maximum_bytes_billed analogue). Actual per-node execution time is summed into billed_seconds and recorded to the ledger. transform build --verify inherits that: its row counts cannot be priced upfront either, so they are gated after the build as a phase drawn against the reservation the build already holds, and their execution time is billed into the same settlement. Only a relation the warehouse keeps no row count for is counted at all, which is any view (dbt’s default materialization); a table’s count is free catalog metadata, and a verdict resting on it is reported exact: false to say so. transform test --mutate prices its whole batch as one number and confirms it once, then runs one dbt invocation per mutant. Nothing is materialized: a mutant builds as an ephemeral model, so the dev namespace holds exactly the relations it held before. A budget that runs out partway stops the run, and the remaining mutants are reported not_run rather than the budget being exceeded. Survivors are not yet compared with the model on the dev data on this connector, so each reports equivalence.status: not_checked with the reason, and nothing is priced or run for it. --verify also reads postgres.dev_schema for the length of that one command, because dbt writes the relations it is judging there and that schema is refused as a source everywhere else. The schema need not exist yet: dbt creates it on a first build, and dex lists it after dbt has run. How that read is bounded is under Read scope in �2. transform init content-checks the dev schema (and, with --layered-schemas, the sibling staging_dev / intermediate_dev / marts_dev layer schemas) with one pg_class catalog lookup per schema, warning when one already holds tables or views; a missing schema stays silent, since dbt creates it.

​
Testing

Offline: a stateful fake connection (tests/fakes/postgres.py) that records statement ordering, serves catalog and pg_stats lookups from a table registry, answers EXPLAIN with size-derived plan costs, simulates timing on an injected clock, and enforces statement_timeout with psycopg’s real QueryCanceled. Live: an env-gated integration suite (DEX_TEST_PG_DSN, plus DEX_TEST_PG_DEV_PASSWORD for builds) against the seeded database from scripts/postgres_seed.sql; scripts/setup_postgres_dev.sh stands up the same thing locally in Docker, and CI runs the suite against a free, keyless, digest-pinned postgres:16 service container with its capabilities dropped (kept in the integration workflow for pattern parity, never a merge gate). Source: references/postgres.md (edit on main). This page is generated; change the source, not the site.