Skip to content

plugin-postgres.md

Latest commit

 

History

History
145 lines (120 loc) · 6.75 KB

plugin-postgres.md

File metadata and controls

145 lines (120 loc) · 6.75 KB
Raw
title PostgreSQL
description The postgres source: profile fields, TLS and SSH bastions.
section plugins
position 5

PostgreSQL

PostgreSQL: the postgres source, from the postgres package. Declare the package once in dependencies.yml; DRE installs it on demand:

# dependencies.yml
plugins:
  - postgres

Profile fields

Set in profiles.yml, under connections:, in a target with type: postgres.

Field Default Notes
host localhost Server host name.
port 5432 Server port.
user (required) User name.
password Password. Secret: use env_var().
database Database name.
dbname Libpq's name for database
sslmode prefer Disable, prefer, require, verify-ca or verify-full. One of disable, prefer, require, verify-ca, verify-full.
sslrootcert CA certificate for verify-ca and verify-full. A file path; ~/ is your home directory.
schema Schema to put first on the search path.
role SET ROLE after connecting.
ssh Reach the server through an SSH bastion (a block of settings) Secret: use env_var().
connect_timeout 30s How long to wait for a connection (30s, 2m, or seconds) A duration (30s, 2m) or seconds.
retries 3 How many times to try again after a temporary error (0: never)
retry_wait The first wait before trying again (30s), doubling each try; a server's Retry-After wins. A duration (30s, 2m) or seconds.
max_retry_wait 10m The longest wait between tries (10m) A duration (30s, 2m) or seconds.

Notes

Field Notes
host, port Default localhost, 5432.
user, password
database (or dbname)
sslmode disable, prefer (default), require, verify-ca, verify-full, with libpq's meanings.
sslrootcert CA certificate for verify-ca / verify-full. A leading ~/ is your home directory.
connect_timeout How long to wait for a connection: a duration (30s, 2m) or seconds. Default 30s (before 0.4, no limit). Queries themselves have no time limit; the run's timeout is the backstop.
retries How many times to try connecting again after a temporary failure (the server unreachable, starting up, or dropping the connection). Default 3; a query is never tried again (see Tries again).
retry_wait The first wait before trying to connect again (30s), doubling each try. Default about 1s, then 4s, then 16s.
max_retry_wait The longest wait between tries. Default 10m.
schema Put first on the search path.
role SET ROLE after connecting.
ssh Reach the server through an SSH bastion: a block of settings, below.

Capabilities: sessions, read_only, check (via EXPLAIN). numeric(p,s) becomes a decimal column. An unconstrained numeric becomes exact text, so cast it (::numeric(18,2)) when you want a typed column. Types DRE can't map ask for a cast, e.g. interval_col::text.

Postgres drops numeric(p,s)'s precision and scale in VALUES lists and across UNION, so their numbers arrive as plain numeric, i.e. text. Cast in the outer query:

select code, amount::numeric(18,2) as amount
from (values ('a', 1.50), ('b', 2.25)) as t(code, amount)

Through an SSH bastion. When the database is only reachable from a jump host, add an ssh: block. host and port are then the database's address as the bastion sees it. dre opens the SSH session itself; there's no ssh -L to run and no local port to keep open.

connections:
  warehouse:
    targets:
      prod:
        type: postgres
        host: db.internal          # as seen from the bastion
        port: 5432
        user: reporting
        password: "{{ env_var('PG_PASSWORD') }}"
        database: analytics
        sslmode: verify-full       # still checks db.internal's certificate
        sslrootcert: ~/certs/ca.pem
        ssh:
          host: bastion.example.com
          username: deploy
          private_key_path: ~/.ssh/id_ed25519
          # or the key's text, e.g. a CI secret:
          # private_key: "{{ env_var('BASTION_SSH_KEY') }}"
          host_key_fingerprint: "SHA256:..."   # or known_hosts_path

The ssh: block takes the same settings as the sftp destination: host, port (22), username, and password, private_key_path or private_key (+ private_key_passphrase), or use_agent: true, and allow_rsa_keys (see RSA keys); the bastion's host key is checked against known_hosts_path (default ~/.ssh/known_hosts) or a pinned host_key_fingerprint, and an unknown or changed key is refused (there's no accept_unknown_host here). The error for an unknown key prints its fingerprint, ready to pin. connect_timeout covers the whole way, SSH included, and errors say which hop failed: the bastion, the bastion's connection to the database, or Postgres itself. Templates can't read the ssh block (it may hold a key), and dre init doesn't ask for it.

A connection error names the server (host:port/database). On macOS the plugin uses the system TLS stack: TLS 1.2 at most, and with verify-ca/verify-full a server certificate valid for more than 825 days is rejected (Apple's limit), so issue server certificates for 825 days or less.

Check the connection before porting a report

For a minimal direct connection, use:

connections:
  warehouse:
    targets:
      dev:
        type: postgres
        host: localhost
        user: reporting
        database: analytics
        password: "{{ env_var('DRE_SECRET_PG_PASSWORD') }}"

Copy the tutorial project's report structure, declare postgres, and start with select 1 as connection_check before replacing the SQL with business queries. Run dre validate --live <report> to check SQL without executing it. Adapt the complete monthly finance example after loading its sample data into Postgres; replace any DuckDB-specific SQL with PostgreSQL equivalents.

invalid-connection-setting means the profile shape needs fixing before connecting. connection-failed names the server: check host, port, credentials and TLS. query-failed on a relation checks database/schema names and the reporting role's permissions. See connection reference, error codes, and connection.

Previous: DuckDB · Next: Databricks