Postgres connector

Extracts a Postgres table into canonical events with keyset pagination (never OFFSET): rows are ordered by (cursor_column, primary key) — or ctid when the table has no primary key — and fetched in pages of 50,000 rows through a server-side cursor, each page under a 5-minute statement_timeout. The session is read-only by construction (default_transaction_read_only = on): the connector never writes to your database — no temp tables, no DDL.

Prerequisites

• An API key with the admin scope.
• A network path from Tessera to the database: a reachable host with a public address, or a bastion with a public address for the SSH tunnel. See Allowed destinations.
• Permission to create a database role.

Allowed destinations

Tessera connects only to public internet addresses. Before any connection, it checks config.host (or, with a tunnel, config.tunnel.host):

• The host is one DNS name with at least two labels, or one IP address. Lists, socket paths, ports and spaces in the host are refused.
• Private and cluster names are refused: localhost, and names ending in .local, .internal, .svc or .arpa.
• Every address the name resolves to must be public. Private (RFC 1918), shared (100.64.0.0/10), loopback, link-local (including the cloud metadata address 169.254.169.254), unique-local and site-local IPv6, IPv4-mapped IPv6 and reserved addresses are refused. If one address is not public, the whole host is refused.
• The port is the default (5432 for the database, 22 for the bastion) or 1024–65535.

Tessera resolves the name once and connects to the address it checked. With ssl_mode: verify-full, the certificate is still verified against the name. A database on a private network stays reachable through an SSH tunnel: the bastion needs a public address, and the bastion resolves the database host inside your network.

A refused destination fails tcp_or_tunnel with failure: configuration. A host name that does not resolve gets the same refusal. PUT /mapping returns 400 invalid_argument, and a sync fails with extraction_failed. Both carry a field_violation naming the refused field:

Field	Refused when
config/host	the database host is malformed, private or cluster-internal, or does not resolve to public addresses
config/port	the database port is privileged and not 5432
config/tunnel/host	the bastion host is malformed, private or cluster-internal, or does not resolve to public addresses. Use a bastion with a public address; the database behind it can stay private.
config/tunnel/port	the bastion port is privileged and not 22

Create the credential (least privilege)

Create a dedicated reader that can connect, see the schema, and SELECT the one table — nothing else. No write privileges, no other tables, no superuser:

CREATE ROLE tessera_reader LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO tessera_reader;
GRANT USAGE ON SCHEMA public TO tessera_reader;
GRANT SELECT ON public.orders TO tessera_reader;

These are exactly the privileges the connector uses: CONNECT to open the session (which then sets default_transaction_read_only = on), schema USAGE to resolve the table, and SELECT for the keyset-paginated reads — catalog lookups (pg_index, pg_attribute) need nothing more. The role needs LOGIN, and pg_hba.conf must allow it from Tessera's address over TLS. The console's Postgres form renders this template with your database, schema, table and username filled in (the password is never part of it).

Tessera verifies this posture: the read_only connection check confirms via has_table_privilege that the user holds no INSERT/UPDATE/DELETE/TRUNCATE on the table — a user with write privileges reports status: "warn" (the test still passes, but use a read-only user).

Create the source

{
  "name": "orders",
  "kind": "postgres",
  "config": {
    "host": "db.internal.example.com",
    "port": 5432,
    "database": "app",
    "schema": "public",
    "table": "orders",
    "ssl_mode": "require"
  },
  "credentials": {
    "username": "tessera_reader",
    "password": "..."
  }
}

ssl_mode is require (default) or verify-full — plaintext connections are never allowed. Credentials are envelope-encrypted at rest and never readable through the API. The config object validates against the connector's registered schema:

{
  "host": "db.internal.example.com",
  "port": 5432,
  "database": "app",
  "schema": "public",
  "table": "orders",
  "ssl_mode": "require"
}

SSH tunnel

For databases reachable only through a bastion, set config.tunnel and add the SSH private key to the credentials. All database TCP traffic then goes through an SSH local forward (direct-tcpip) established with that key:

{
  "host": "db.internal.example.com",
  "port": 5432,
  "database": "app",
  "table": "orders",
  "tunnel": { "host": "bastion.example.com", "port": 22, "username": "tessera" }
}

Generate a dedicated key pair (e.g. ssh-keygen -t ed25519 -f tessera_tunnel -N ''), install the public half on the bastion user's authorized_keys, and paste the private half into credentials.tunnel_private_key. With a tunnel, prefer ssl_mode: require (verify-full would verify the certificate against the local forward address rather than the database host name).

Map columns to canonical events

The mapping declares entity / event type / timestamp / attribute columns — see the quickstart's worked mapping. Two Postgres-specific rules:

Incremental syncs. Declare a mapping cursor on a timestamptz, timestamp, date, smallint, integer or bigint column. Incremental syncs apply the strict predicate cursor_column > watermark, composed with keyset pagination; the watermark only advances when a sync commits. An index on the cursor column keeps pages fast.

Type map. Every mapped column is converted per this normative table (generated from the connector's type map — code and docs cannot drift). Mapping an unsupported column fails PUT /sources/{id}/mapping with the column and its type named.

Postgres type	Canonical type	Notes
smallint	integer	Exact integers.
integer	integer	Exact integers.
bigint	integer	Exact 64-bit integers.
numeric	number	Converted to float64 (values beyond float64 precision convert lossily). A column mapped as an entity or event id lands as an exact decimal instead (up to 38 digits before and after the point), so ids above 2^53 - 1 stay exact; a NaN, an infinity or a wider value is refused for its row only, counted in the sync's details (cast such a column to text in a view to keep its ids).
real	number	IEEE-754 floats.
double precision	number	IEEE-754 float64, as-is.
boolean	boolean	Booleans, as-is.
text	string	UTF-8 strings, as-is (≤ 4096 chars per 002 §2).
varchar	string	UTF-8 strings, as-is.
char	string	Blank-padded strings, as returned by Postgres.
uuid	string	Canonical lowercase-hex string form.
inet	string	Textual address form.
citext	string	UTF-8 strings, as-is.
enum	string	The enum label as a string (any CREATE TYPE … AS ENUM).
timestamptz	timestamp	Converted to RFC 3339 UTC.
timestamp	timestamp	Naive wall-clock time: the mapping's IANA timezone is required — without it, rows carrying a value are rejected.
date	timestamp	Midnight UTC of the date (YYYY-MM-DDT00:00:00Z).
text[]	array	Array of strings (≤ 64 items per 002 §2).
varchar[]	array	Array of strings (≤ 64 items).
smallint[]	array	Array of integers (≤ 64 items).
integer[]	array	Array of integers (≤ 64 items).
bigint[]	array	Array of integers (≤ 64 items).
numeric[]	array	Array of numbers (elements converted to float64).
json	unsupported	Cannot be mapped (flatten upstream); PUT /mapping fails naming the column.
jsonb	unsupported	Cannot be mapped (flatten upstream); PUT /mapping fails naming the column.
bytea	unsupported	Cannot be mapped; PUT /mapping fails naming the column.
tsvector	unsupported	Cannot be mapped; PUT /mapping fails naming the column.
interval	unsupported	Cannot be mapped; PUT /mapping fails naming the column.
money	unsupported	Cannot be mapped; PUT /mapping fails naming the column.
geometric	unsupported	point/line/lseg/box/path/polygon/circle cannot be mapped; PUT /mapping fails naming the column.

A numeric id value the exact decimal cannot carry lands, with the other such rows of its fetched page, after that page's other rows and in a landed part of its own. Row numbers in the rejected-rows report follow that landed order.

Date and naive-timestamp semantics

• date values become midnight UTC of that date (2026-02-01 → 2026-02-01T00:00:00Z).
• timestamp (without time zone) values are naive wall-clock times: the mapping's IANA timezone is required, and each value is converted from that zone to UTC. Without a timezone, rows carrying a value are rejected.
• If your dates carry local-date semantics, map them through a naive timestamp column and set the mapping timezone instead of relying on the midnight-UTC convention.

Test the connection and run the first sync

POST /sources/{id}/connection-diagnostics runs four checks in order, each tagged with the diagnostic stage it verifies:

Check	Stage	Verifies
tcp_or_tunnel	network	a TCP connection to the host, or the SSH tunnel to the bastion
authenticate	authentication	a TLS session with the stored username and password
table_readable	authorization	the table exists and the user holds SELECT on it
read_only	authorization	the user holds no write privilege (warn otherwise)

A failed check carries a typed failure cause and a detail naming the fix; later checks report not_attempted. Postgres's refusal decides the cause: a wrong password or unknown role is authentication; a missing CONNECT, LOGIN or pg_hba.conf entry, missing schema USAGE or missing SELECT is authorization; a missing database or table is not_found; an unreachable host, closed port, failed tunnel or refused TLS is network (a rejected tunnel key is authentication). Details never contain the password or the server's error text. The response's checked_at is the server time of the test, which is also recorded in the audit log as source.test_connection. POST /sources/{id}/test-connection runs the same checks and returns only their names, statuses and details. The full flow, as one runnable script (it needs a reachable database, so CI runs it against our hosted deployment rather than the local stack):

01-create-and-sync.sh

#!/usr/bin/env bash
# Postgres connector: create the source (vaulted read-only credentials),
# test the connection, first sync. Requires a reachable database (hosted
# runs only, not the local stack): set TESSERA_PG_HOST / TESSERA_PG_DB / TESSERA_PG_TABLE /
# TESSERA_PG_USER / TESSERA_PG_PASSWORD.
set -euo pipefail

: "${TESSERA_API_BASE:?e.g. https://api.staging.tessera.zone/v1}"
: "${TESSERA_API_KEY:?your tsk_test_… key}"
: "${TESSERA_PG_HOST:?database host}"
: "${TESSERA_PG_DB:?database name}"
: "${TESSERA_PG_TABLE:?table to extract}"
: "${TESSERA_PG_USER:?read-only user}"
: "${TESSERA_PG_PASSWORD:?password}"
AUTH=(-H "Authorization: Bearer $TESSERA_API_KEY")

SOURCE="$(curl -sS -X POST "$TESSERA_API_BASE/sources" "${AUTH[@]}" \
  -H "Content-Type: application/json" \
  -H "Idempotency-Key: docs-postgres-source" \
  -d '{
    "name": "orders-db",
    "kind": "postgres",
    "config": {
      "host": "'"$TESSERA_PG_HOST"'",
      "database": "'"$TESSERA_PG_DB"'",
      "table": "'"$TESSERA_PG_TABLE"'",
      "ssl_mode": "require"
    },
    "credentials": {
      "username": "'"$TESSERA_PG_USER"'",
      "password": "'"$TESSERA_PG_PASSWORD"'"
    }
  }')"
SOURCE_ID="$(printf '%s' "$SOURCE" | python3 -c 'import json,sys; print(json.load(sys.stdin)["source_id"])')"

# Checks: tcp_or_tunnel, authenticate, table_readable, read_only (warns if
# the user holds write privileges — use a read-only role).
curl -sS -X POST "$TESSERA_API_BASE/sources/$SOURCE_ID/test-connection" "${AUTH[@]}" \
  | python3 -c '
import json, sys
result = json.load(sys.stdin)
print("test-connection:", result["status"])
for check in result["checks"]:
    print("  %s: %s" % (check["name"], check["status"]))
'

SYNC="$(curl -sS -X POST "$TESSERA_API_BASE/sources/$SOURCE_ID/syncs" "${AUTH[@]}" \
  -H "Content-Type: application/json" \
  -H "Idempotency-Key: docs-postgres-first-sync" \
  -d '{"mode": "full"}')"
SYNC_ID="$(printf '%s' "$SYNC" | python3 -c 'import json,sys; print(json.load(sys.stdin)["sync_id"])')"
for _ in $(seq 1 600); do
  STATUS="$(curl -sS "$TESSERA_API_BASE/syncs/$SYNC_ID" "${AUTH[@]}" \
    | python3 -c 'import json,sys; print(json.load(sys.stdin)["status"])')"
  if [ "$STATUS" = "succeeded" ] || [ "$STATUS" = "failed" ]; then break; fi
  sleep 1
done
echo "first sync: $STATUS"

Download connect/postgres/01-create-and-sync.sh — this exact file runs in CI on every change (hosts shown are for this environment).

Rotate the credential

The source holds the role's password, and the SSH tunnel private key when a tunnel is on. To rotate the password: set a new one on the role, or create a second role with the same grants, then PATCH /sources/{id} with credentials. The body replaces the stored credentials entirely, so repeat the username and, with a tunnel, the tunnel_private_key. Confirm POST /sources/{id}/connection-diagnostics passes before you retire the old password or role. A sync already running keeps the password it started with.

Troubleshooting

Symptom	Cause	Fix
tcp_or_tunnel fails with failure: network	host unreachable from Tessera	use the SSH tunnel or open the network path
tcp_or_tunnel fails with failure: configuration	the host, bastion or port is not an allowed destination	use a public DNS name or address, or reach a private database through a bastion
authenticate fails with failure: authentication	wrong password or unknown role	replace the credentials
authenticate fails with failure: authorization	no CONNECT, no LOGIN, or no pg_hba.conf entry for Tessera	grant CONNECT, allow the role to log in, add the pg_hba.conf rule
table_readable fails with failure: authorization	missing schema USAGE or table SELECT	run the two grants above
read_only: warn	the user holds write privileges	create the dedicated tessera_reader role above
PUT /mapping fails naming a column	unsupported type (jsonb, bytea, …)	flatten/cast upstream (a generated column works well)
pages slow / statement timeouts	no index on the cursor column	index it; keyset pagination then stays fast
rows rejected as invalid_timestamp	naive timestamp without timezone	set the mapping's IANA timezone
rows rejected as missing_entity_id although the id is filled	the id is not an identifier; the sync's details count it as entity_ids_refused with a reason	see the reasons
rows rejected (or participants dropped) with entity_ids_refused reason imprecise or non_finite on a numeric id column	an id with more than 38 digits before or after the point, or NaN/infinity	cast the column to text in a view and map that; NaN is no id