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 ahostsslrule.
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
5432), database, username and password. If you use a private CA, paste its certificate.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 type | Dataset type |
|---|---|
smallint, integer, real, double precision | number |
bigint, numeric | decimal (exact, as text) |
boolean | boolean |
date | date |
timestamp, timestamptz | datetime (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
| Limit | Value |
|---|---|
| Rows per read | 50,000 |
| Data per read | 5 MiB |
| Fields | 100 |
| Statement timeout | 25 s server-side, 30 s client deadline |
| Connect timeout | 10 s |
| Verification statements | 8 s |
| Relations listed / selected | 500 / 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
| Code | Cause | What to do |
|---|---|---|
POSTGRES_DNS | The host does not resolve | Check the spelling, and use the public hostname |
POSTGRES_NETWORK_POLICY | The host resolves to a private or reserved address (403) | Use the public hostname, or ask your administrator to allow it |
POSTGRES_TCP | No connection to host:port (503) | Check the port and firewall, and allow the egress IPs |
POSTGRES_TLS | The server does not accept TLS, or the certificate does not verify | Enable SSL, paste the CA, and check that the certificate names the host |
POSTGRES_AUTH | Wrong password, a pg_hba.conf rejection, or the database does not exist | Check the credentials, and allow the user over hostssl from the egress IPs |
POSTGRES_PERMISSION | The role cannot read a selected relation, or the relation does not exist | Grant USAGE on the schema and SELECT on the relation, or deselect it |
INCOMPLETE_DATA | The relation has more than 50,000 rows, or exceeds 5 MiB | Select a bounded view |
TIMEZONE_REQUIRED | A filtered read includes a timestamp without time zone column | Use a view with timestamptz |
PROVIDER_UNAVAILABLE | Verification, metadata or the read failed for another reason (details.stage: verify, metadata or read) | Retry later, then check connection health |
PERMISSION_DENIED | The relation is no longer selected | Select it again, or use a selected relation |