# PostgreSQL > The postgres source: profile fields, TLS and SSH bastions. PostgreSQL: the `postgres` source, from the `postgres` package. Declare the package once in `dependencies.yml`; DRE installs it on demand: dependencies.yml ```yaml 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) ## 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](https://getdre.com/docs/plugins/#tries-again)). `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: ```sql 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. ```yaml 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`](https://getdre.com/docs/plugin-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](https://getdre.com/docs/plugin-sftp/#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: ```yaml connections: warehouse: targets: dev: type: postgres host: localhost user: reporting database: analytics password: "{{ env_var('DRE_SECRET_PG_PASSWORD') }}" ``` Copy the [tutorial project’s](https://github.com/get-dre/dre/blob/v0.5.1/examples/tutorial/) report structure, declare `postgres`, and start with `select 1 as connection_check` before replacing the SQL with business queries. Run `dre validate --live ` to check SQL without executing it. Adapt the complete [monthly finance example](https://github.com/get-dre/dre/blob/v0.5.1/examples/monthly-finance/) 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](https://getdre.com/docs/reference-profiles/), [error codes](https://getdre.com/docs/reference-error-codes/), and [connection](https://getdre.com/docs/glossary/#connection). [Edit page](https://github.com/get-dre/dre/edit/master/docs/plugin-postgres.md) [Previous DuckDB](https://getdre.com/docs/plugin-duckdb/)[Next Databricks](https://getdre.com/docs/plugin-databricks/)