PostgreSQL

Read tables in a PostgreSQL database you run elsewhere, on a managed service or your own server, and run read-only SQL against it.

Connect it in the console under Integrations > Connectors. See Connectors for how connectors work and who can use them.

Create a read-only credential

Create a user that can only read, and use it here:

CREATE ROLE vaif_reader LOGIN PASSWORD 'a-long-random-password';
GRANT CONNECT ON DATABASE app TO vaif_reader;
GRANT USAGE ON SCHEMA public TO vaif_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO vaif_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO vaif_reader;

The host must resolve to a public address: VAIF Studio refuses private, loopback, link-local and cloud metadata addresses, and connects to the address it checked, so a DNS change cannot redirect it.

Settings

Setting		Notes
Host	Required	For example db.example.com.
Port	Optional	Default 5432.
Database	Required	For example app.
Username	Required	Use a user that can only SELECT.
Password	Required	Stored encrypted and never shown again.
SSL	Optional	One of: Encrypt and verify the certificate (recommended), Encrypt, don't verify the certificate, No encryption. Default: Encrypt and verify the certificate (recommended).

Enter the host name only, with no postgres://, port or path. For a server whose certificate is signed by a private authority, as on some managed services, choose Encrypt, don’t verify. No encryption is only for a network you trust.

Tables

Table	Filters	Returns
tables	None	Tables and views outside the system schemas.
columns	table (required)	Columns of one table (schema.table or table).
sql	query (required)	Run one read-only SQL statement. Needs the developer role.

Query from the API

curl -s -X POST "https://api.vaif.studio/database/connector/$VAIF_PROJECT_ID/query" \
  -H "Authorization: Bearer $VAIF_TOKEN" -H "Content-Type: application/json" \
  -d '{"wrapperId":"postgres_fdw","table":"sql","filters":{"query":"SELECT id, email FROM customers ORDER BY created_at DESC"},"limit":50}'

$VAIF_TOKEN is a console access token. See Connectors for the routes.

How it stays read-only

Every query runs as a single statement in a READ ONLY transaction with a 15-second statement timeout. A second statement after a ; is refused before anything is sent, and again by PostgreSQL. A SELECT is capped at the page size on the server, so a large table is never copied whole.