Skip to content
Documentation menu

PostgreSQL

Connecting PostgreSQL databases: connection and TLS, a read-only role, dialect notes, set-returning functions, data types.

Prynt reads PostgreSQL databases with the Npgsql driver, through plain queries and set-returning functions. This guide covers what is specific to PostgreSQL; the general concepts are in Data sources and Datasets.

Connect a PostgreSQL database

Hoststringrequired
With a gateway, as the gateway sees it; direct, a public DNS name (managed services such as Azure Database, Amazon RDS, Google Cloud SQL).
Portinteger
Default 5432. Direct connections allow 5432–5435 and 6432 (a common PgBouncer port).
Databasestringrequired
The database name.
Username, Passwordcredentialsrequired
A dedicated read-only role (below).
Current schemastring
Becomes the search_path, so FROM customers finds erp.customers.
Schemas in the explorerstring[]
The schemas the database explorer lists. Empty = every schema except information_schema and pg_*.
TLSmode
Maps to sslmode: Disabled → disable, Required → require, Verify CA → verify-ca, Verify certificate and host → verify-full.

The connection test reports the server version and the effective role, and warns when the role is a superuser or can create roles or databases, when it can write to tables or create objects in a schema, and when default_transaction_read_only is off.

A read-only role

Every query already runs in a read-only transaction; a role that is read-only by default and has only SELECT adds defense in depth. The connection test offers this script:

PostgreSQL · read-only role
-- PostgreSQL: a dedicated read-only role for PryntCREATE ROLE prynt_reader LOGIN PASSWORD '<a strong password>'  NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;ALTER ROLE prynt_reader SET default_transaction_read_only = on;GRANT CONNECT ON DATABASE erp TO prynt_reader;GRANT USAGE ON SCHEMA erp TO prynt_reader;GRANT SELECT ON ALL TABLES IN SCHEMA erp TO prynt_reader;ALTER DEFAULT PRIVILEGES IN SCHEMA erp GRANT SELECT ON TABLES TO prynt_reader;

Add GRANT EXECUTE ON FUNCTION … for the functions used by Function datasets. If your tables use Row-Level Security, the role sees only the rows its policies allow.

TLS

  • Direct connections must use TLS; choose Verify certificate and host (verify-full) whenever the server certificate is issued by a trusted authority for its host name.
  • Behind a gateway, on your own network, TLS may be disabled; the test warns about it.

Writing queries for PostgreSQL

Dataset LINES
SELECT r.line_no, r.item, i.description, r.qty, r.price  FROM erp.ddt_lines r  JOIN erp.items i ON i.code = r.item WHERE r.ddt_id = :DOCUMENT_ID   AND (:FROM_DATE IS NULL OR r.created_at >= :FROM_DATE) ORDER BY r.line_no LIMIT 500
  • A dataset is a single SELECT, WITH, VALUES or TABLE statement; a trailing ; is accepted.
  • Bind variables are written :NAME. Casts with ::type, string literals ('…', E'…', $tag$…$tag$) and quoted identifiers are recognized, so a colon inside them is not a bind variable.
  • Unquoted names are folded to lower case by PostgreSQL; Prynt shows field names in upper case in the Designer (LINE_NO), and expressions use them that way.
  • Each query has its own statement_timeout (the dataset timeout), inside a read-only transaction; the datasets of a render share a REPEATABLE READ snapshot.

Set-returning functions

A dataset with the Function source calls a function that returns rows: Prynt runs SELECT * FROM schema.function(:ARG1, :ARG2, …) with the bind variables you choose for the arguments, in order. Values are passed without a type and PostgreSQL converts them to the declared argument types. The function must only read.

Data types

smallint, integer, bigintinteger
Integer columns.
numeric, money, real, doubledecimal
Exact decimals.
datedate
Dates without a time.
timestampdateTime
Date and time as stored.
timestamptzdateTime
Date and time in UTC.
booleanboolean
Yes/no.
byteabinary
Images from a field.
text, varchar, char, json, jsonb, xmlstring
Other types (uuid, arrays, intervals…) are read as text.

Troubleshooting

PostgreSQL errors show their SQLSTATE code with the position in the query. The most common:

42P01SQL
Relation does not exist: write the schema, set the current schema, or grant USAGE on the schema.
42501privileges
Permission denied: grant SELECT on the table (or EXECUTE on the function).
42703SQL
Column does not exist: check its name (and quoted mixed-case names).
25006read-only
A function tried to write inside the read-only transaction.
57014timeout
The statement hit its timeout: add filters or indexes, or raise the dataset timeout.
28P01login
Password authentication failed: check user and password.

Network problems

If the database cannot be reached, renders fail with 503 DATA_SOURCE_UNAVAILABLE. Check host, port and pg_hba.conf from the gateway's server, or the firewall of your cloud database for direct connections.