Connecting Postgres

Add a Postgres source to op-data — fields, SSL modes, and least-privilege setup.

Postgres is the first-class source in op-data v1. This page covers the fields on the connection form, how credentials are stored, and the read-only user we recommend creating for op-data.

How credentials flow

When you save a connection, the op-data UI starts a Temporal workflow (storeCredsWorkflow) on your agent’s task queue. The agent validates the credentials, opens a short-lived test connection to your database, and writes the payload to its local Postgres state database. The op-data control plane never receives the password.

The control plane stores only:

• A connection ID.
• A display name.
• type: "postgres".
• Non-secret metadata for the UI (host, database name, SSL mode).
• Connection status (connected / error / pending).

See Security for the full boundary.

Connection fields

Field	Required	Notes
Name	yes	Free-form display label shown in the UI.
Host	yes	Hostname or IP reachable from the agent’s network. Not op-data’s.
Port	no	Defaults to 5432.
Database	yes	The database to connect to. You will add tables from this database to canonical models.
Username	yes	We recommend a dedicated read-only user — see below.
Password	yes	Stored on the agent only.
SSL mode	yes	One of disable or require (see below).

v1 exposes a fields credential mode through the UI. A typed dsn mode also exists in the schema for programmatic use but is not yet wired to the form.

SSL modes

op-data supports two modes in v1:

• require — the agent refuses to connect unless the server negotiates TLS. Use this for any database reachable over a network you do not fully control (managed Postgres, VPN-crossed links, anything on the public internet).
• disable — plaintext. Acceptable only for databases the agent reaches over a trusted link (same VPC, private subnet, localhost).

There is no verify-ca / verify-full option in v1. If you need certificate pinning, let us know.

Least-privilege user

We recommend a dedicated Postgres role with read-only access to the schemas op-data needs. This keeps canonical sync and preview queries constrained and auditable.

-- one-time setup
create role op_data_reader with login password '***';

grant connect on database analytics to op_data_reader;
grant usage on schema public to op_data_reader;

grant select on all tables in schema public to op_data_reader;
alter default privileges in schema public
  grant select on tables to op_data_reader;

Repeat the usage / select grants for each schema you want op-data to see. Do not grant CREATE, INSERT, UPDATE, DELETE, or TRUNCATE.

Preview queries

From a connection detail page, Preview query runs a single SQL statement against your database via the agent. The result is tabular and returned to the UI for rendering.

Constraints enforced on the agent:

• SELECT / WITH only. Any other statement type is rejected.
• Single statement. ; chains are rejected.
• 100 row cap. The agent wraps your query in select * from (<your sql>) as preview_query limit 100.
• 10 second timeout.
• Per-cell text truncation at 1024 characters; values are sanitized before leaving the agent.
• Truncation flag. If you hit the row cap, the response sets truncated: true so the UI can surface it.

This is the only path in op-data where row data leaves your perimeter. All other flows (canonical sync, association resolution, loading to destinations) return metadata — row counts, byte counts, schema fingerprints — never source rows.

Network requirements

The agent must reach the database host over the network. Common patterns:

• Agent in the same VPC as the database. Usually zero config; just make sure the database’s security group allows traffic from the agent.
• Agent on a bastion / jumphost. Run the agent on a host that already has network access to the database.
• Agent behind a NAT gateway. Whitelist the NAT’s egress IP in the database firewall.

op-data’s control plane never connects to your database directly, so you do not need to allowlist any of our IPs.

Rotating credentials

Edit the connection in the UI and save — the agent runs testCredentials against the new values before upserting the stored payload. The old password is overwritten on success. If the test fails, the existing credentials remain in place.

To revoke access entirely, delete the connection from the UI, then drop the role on the database server.

Troubleshooting

connection refused or timeout. The agent could not reach the host. Check the agent’s network path to the database — not op-data’s.

password authentication failed. The username or password is wrong, or the role is missing LOGIN. Test from the agent’s host with psql using the same credentials.

permission denied for relation ... during preview. The role is missing SELECT on the target table. Re-run the grants above, including the alter default privileges statement for any new tables.

Invalid Postgres credentials. The stored payload is missing or malformed. Re-save the connection from the UI.