PostgreSQL Setup

This guide connects a PostgreSQL database to Klairr. The connection uses a dedicated read-only role and TLS — Klairr never has write access to your data.

Prerequisites

• PostgreSQL 12 or higher. Earlier versions may work but are not officially supported.
• A reachable Postgres host — public endpoint, or private network with a route from Klairr (see Network Access).
• TLS enabled on the cluster. TLS 1.2+ is required.
• A database admin who can create roles and grant SELECT.

Choose a connection method

Step 1: Create a read-only role

Run as a Postgres superuser or an account with the CREATEROLE privilege:

-- Replace <password> with a strong random secret.
CREATE ROLE klairr_reader WITH LOGIN PASSWORD '<password>';

GRANT CONNECT ON DATABASE analytics TO klairr_reader;
GRANT USAGE ON SCHEMA public TO klairr_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO klairr_reader;

-- Pick up tables created in the future automatically.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO klairr_reader;

The role only needs CONNECT, USAGE on schema, and SELECT on tables. Klairr explicitly does not request INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, or TRIGGER.

If you can’t provision a strict read-only role (common in dev environments), see “Allow writable role” below.

Step 2: Point Klairr at a read replica (recommended)

If your Postgres serves production traffic, use a read replica or a logical-replication standby. Klairr enforces server-side default_transaction_read_only = on and runs every query inside a READ ONLY transaction, but isolating analytics load is the safer pattern.

Step 3: Add the connector in Klairr

Open Settings → Connectors → Add connector, pick PostgreSQL, and enter:

Allow writable role

If your role grants writes (because your database conflates read and write grants, or because you’re connecting in a dev environment), Klairr’s connect-time probe will block the setup.

You can set “Allow writable role” to “Yes” to acknowledge this and proceed. Klairr’s executor will still only emit SELECT — write statements (INSERT, UPDATE, DELETE, DROP, TRUNCATE, MERGE, GRANT, etc.) are blocked at the application layer regardless of what the role permits. The acknowledgement is recorded on the connector record so admins can see at a glance which connectors are running on writable roles.

Setup tests

When you click Connect, Klairr runs the following checks. Each failure maps to the troubleshooting entry below.

1. Network reachability — DNS resolves and the TCP port accepts a connection.
2. TLS handshake — the cluster speaks TLS 1.2+; the cert validates against the system root store.
3. Authentication — the role can connect and the password is correct.
4. Read access — the role has SELECT on at least one table in the configured schema.
5. Write probe — Klairr attempts a no-op write inside a rolled-back transaction. A read-only role rejects this; a writable role triggers the “Allow writable role” path above.
6. Schema introspection — Klairr enumerates tables, columns, and types from information_schema.

What Klairr queries

Schema introspection runs against information_schema and pg_class to enumerate tables, views, columns, and types — no row data leaves your database during introspection.

Question answering issues plain SELECT statements with a server-enforced LIMIT. Every emitted query is validated against an allow-list before execution: any statement that contains INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, TRUNCATE, MERGE, CALL, GRANT, REVOKE, EXECUTE, BEGIN, EXPORT, LOAD, RENAME, LOCK, ASSERT, or SELECT … INTO is rejected at the application layer — not just by your database role.

Troubleshooting

• Connection refused — the host is unreachable from Klairr. Confirm the host is publicly reachable; if it’s on a private network, set “Host is on a private network” to Yes and ensure the route exists. See Network Access.
• TLS handshake failed — the cluster speaks an unsupported TLS version, or the cert isn’t valid. Confirm TLS 1.2+ is enabled. For self-signed certs, downgrade SSL mode from Verify-full to Require.
• Authentication failed — the role doesn’t exist, the password is wrong, or pg_hba.conf rejects the connection. Verify in psql from a workstation that has the same network reachability.
• Permission denied — the role is missing SELECT on at least one table. Re-run the GRANT SELECT ON ALL TABLES statement and verify with \dp public.* in psql.
• “Credentials grant write access” — Klairr’s probe found the role can write. Either narrow the role to SELECT-only, or set “Allow writable role” to Yes (Klairr’s executor allow-list will still block writes).
• Schema introspection returned 0 tables — the role has CONNECT but no USAGE on schema. Re-run GRANT USAGE ON SCHEMA public TO klairr_reader.

Related

• Network Access — egress IPs, private-network options, TLS requirements.
• Roles & Permissions — who in your Klairr workspace can see and query which connectors.
• Supported Data Sources — every database and SaaS Klairr connects to.
• Security & Data Handling — encryption, logging, what we do and don’t store.