Docs
Connections

PostgreSQL

Connect a PostgreSQL database over verified TLS with a read-only role, select relations and read them as datasets.

A PostgreSQL connection reads the tables and views you select through a read-only transaction, over TLS that is always verified. See Connections for the shared security model.

Before you start

  • The database must be reachable on a public hostname. Private and reserved addresses are refused.
  • The server must accept TLS, with a certificate that names that hostname. If your certificate comes from a private CA, have the CA certificate ready.
  • Allow the egress IP addresses shown in the form in your firewall and in pg_hba.conf. Use a hostssl rule.

Create a dedicated read-only role. The form shows SQL like this, filled in with your database name, ready to copy:

CREATE ROLE xentropy_reader LOGIN PASSWORD 'replace-with-a-strong-password';
GRANT CONNECT ON DATABASE your_database TO xentropy_reader;
GRANT USAGE ON SCHEMA public TO xentropy_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO xentropy_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO xentropy_reader;

Set up the connection

Open Connections, choose New connection, then PostgreSQL.
Name the connection. Optionally paste a connection string and choose Fill fields.
Enter the host, port (default 5432), database, username and password. If you use a private CA, paste its certificate.
Choose Test and list relations. Xentropy verifies DNS, network policy, TCP, TLS and the login, then lists the relations the role can read.
Select the relations that agents and datasets may read (up to 100), then choose Save connection.

The list includes tables, partitioned tables, views, materialized views and foreign tables in schemas where the role has USAGE and the relation grants SELECT. System schemas are excluded. Up to 500 relations are listed, and the response says when the list was truncated. Schema and table names can have up to 63 characters, including dots, spaces and other punctuation, but not a double quote or a control character. Relations with such names are skipped, and the response gives their count. Every name is quoted in the SQL Xentropy sends. When you type a name with other characters than letters, digits and _, put it in double quotes, for example public."sales.2024".

When you save, Xentropy probes every selected relation with a zero-row SELECT, so a missing grant is reported by name.

Type mapping

PostgreSQL typeDataset type
smallint, integer, real, double precisionnumber
bigint, numericdecimal (exact, as text)
booleanboolean
datedate
timestamp, timestamptzdatetime (ISO 8601, UTC)
Everything else (text, varchar, uuid, json, jsonb, interval, inet, arrays, …)string

Primary-key and unique constraints become the dataset's grain. A foreign key to another selected relation becomes a relationship that plans can join on.

Limits

LimitValue
Rows per read50,000
Data per read5 MiB
Fields100
Statement timeout25 s server-side, 30 s client deadline
Connect timeout10 s
Verification statements8 s
Relations listed / selected500 / 100

Filters on a dataset are pushed down to the database for comparisons (eq, neq, lt, lte, gt, gte), in and isNull. Text columns push down only eq, in and isNull. The dataset query is applied again after the read.

Errors

CodeCauseWhat to do
POSTGRES_DNSThe host does not resolveCheck the spelling, and use the public hostname
POSTGRES_NETWORK_POLICYThe host resolves to a private or reserved address (403)Use the public hostname, or ask your administrator to allow it
POSTGRES_TCPNo connection to host:port (503)Check the port and firewall, and allow the egress IPs
POSTGRES_TLSThe server does not accept TLS, or the certificate does not verifyEnable SSL, paste the CA, and check that the certificate names the host
POSTGRES_AUTHWrong password, a pg_hba.conf rejection, or the database does not existCheck the credentials, and allow the user over hostssl from the egress IPs
POSTGRES_PERMISSIONThe role cannot read a selected relation, or the relation does not existGrant USAGE on the schema and SELECT on the relation, or deselect it
INCOMPLETE_DATAThe relation has more than 50,000 rows, or exceeds 5 MiBSelect a bounded view
TIMEZONE_REQUIREDA filtered read includes a timestamp without time zone columnUse a view with timestamptz
PROVIDER_UNAVAILABLEVerification, metadata or the read failed for another reason (details.stage: verify, metadata or read)Retry later, then check connection health
PERMISSION_DENIEDThe relation is no longer selectedSelect it again, or use a selected relation

On this page