Vai al contenuto
Menu della documentazione

PostgreSQL

Collegare database PostgreSQL: connessione e TLS, un ruolo in sola lettura, note sul dialetto, funzioni che restituiscono righe, tipi di dati.

Prynt legge i database PostgreSQL con il driver Npgsql, tramite query semplici e funzioni che restituiscono righe. Questa guida tratta ciò che è specifico di PostgreSQL; i concetti generali sono in Sorgenti dati e Dataset.

Collegare un database PostgreSQL

Hosttestoobbligatorio
Con un gateway, come lo vede il gateway; diretta, un nome DNS pubblico (servizi gestiti come Azure Database, Amazon RDS, Google Cloud SQL).
Portaintero
Predefinita 5432. Le connessioni dirette ammettono 5432–5435 e 6432 (una porta comune di PgBouncer).
Databasetestoobbligatorio
Il nome del database.
Utente, Passwordcredenzialiobbligatorio
Un ruolo dedicato in sola lettura (vedi sotto).
Schema correntetesto
Diventa il search_path, così "FROM clienti" trova erp.clienti.
Schemi nell'esploratoreelenco
Gli schemi che l'esploratore del database elenca. Vuoto = tutti gli schemi tranne information_schema e pg_*.
TLSmodalità
Corrisponde a sslmode: Disattivato → disable, Obbligatorio → require, Verifica la CA → verify-ca, Verifica certificato e host → verify-full.

La prova della connessione riporta la versione del server e il ruolo effettivo, e avvisa quando il ruolo è un superuser o può creare ruoli o database, quando può scrivere nelle tabelle o creare oggetti in uno schema, e quando default_transaction_read_only è disattivato.

Un ruolo in sola lettura

Ogni query gira già in una transazione in sola lettura; un ruolo in sola lettura per impostazione predefinita e con il solo SELECT aggiunge una difesa in profondità. La prova della connessione offre questo script:

PostgreSQL · ruolo in sola lettura
-- PostgreSQL: un ruolo dedicato a Prynt in sola letturaCREATE ROLE prynt_reader LOGIN PASSWORD '<una password robusta>'  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;

Aggiungi GRANT EXECUTE ON FUNCTION … per le funzioni usate dai dataset Funzione. Se le tue tabelle usano la Row-Level Security, il ruolo vede solo le righe che le sue policy consentono.

TLS

  • Le connessioni dirette devono usare TLS; scegli Verifica certificato e host (verify-full) ogni volta che il certificato del server è emesso da un'autorità fidata per il suo nome host.
  • Dietro un gateway, nella tua rete, TLS si può disattivare; la prova lo segnala.

Scrivere query per PostgreSQL

Dataset RIGHE
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
  • Un dataset è una sola istruzione SELECT, WITH, VALUES o TABLE; un ; finale è ammesso.
  • Le bind variable si scrivono :NOME. I cast con ::tipo, le stringhe ('…', E'…', $tag$…$tag$) e gli identificatori tra virgolette sono riconosciuti, quindi i due punti al loro interno non sono bind variable.
  • PostgreSQL riporta in minuscolo i nomi senza virgolette; nel Designer Prynt mostra i nomi dei campi in maiuscolo (LINE_NO), e le espressioni li usano così.
  • Ogni query ha il proprio statement_timeout (il timeout del dataset), dentro una transazione in sola lettura; i dataset di una generazione condividono un'istantanea REPEATABLE READ.

Funzioni che restituiscono righe

Un dataset con origine Funzione chiama una funzione che restituisce righe: Prynt esegue SELECT * FROM schema.funzione(:ARG1, :ARG2, …) con le bind variable che scegli per gli argomenti, nell'ordine. I valori sono passati senza tipo e PostgreSQL li converte nei tipi dichiarati degli argomenti. La funzione deve solo leggere.

Tipi di dati

smallint, integer, bigintinteger
Colonne intere.
numeric, money, real, doubledecimal
Decimali esatti.
datedate
Date senza ora.
timestampdateTime
Data e ora come memorizzate.
timestamptzdateTime
Data e ora in UTC.
booleanboolean
Sì/no.
byteabinary
Immagini da un campo.
text, varchar, char, json, jsonb, xmlstring
Gli altri tipi (uuid, array, intervalli…) si leggono come testo.

Problemi frequenti

Gli errori PostgreSQL mostrano il codice SQLSTATE con la posizione nella query. I più comuni:

42P01SQL
La relazione non esiste: scrivi lo schema, imposta lo schema corrente, oppure concedi USAGE sullo schema.
42501privilegi
Permesso negato: concedi SELECT sulla tabella (o EXECUTE sulla funzione).
42703SQL
La colonna non esiste: controlla il nome (e i nomi tra virgolette con maiuscole e minuscole).
25006sola lettura
Una funzione ha provato a scrivere dentro la transazione in sola lettura.
57014timeout
L'istruzione ha raggiunto il timeout: aggiungi filtri o indici, oppure alza il timeout del dataset.
28P01accesso
Autenticazione con password non riuscita: controlla utente e password.

Problemi di rete

Se il database non è raggiungibile, le generazioni falliscono con 503 DATA_SOURCE_UNAVAILABLE. Controlla host, porta e pg_hba.conf dal server del gateway, oppure il firewall del tuo database in cloud per le connessioni dirette.