PostgreSQL Setup

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

Prerequisites

• A reachable Postgres host (cloud-hosted or behind a public endpoint)
• A database admin who can create roles and grant SELECT
• The schema name you want Klairr to query (default: public)

For self-hosted databases on a private network, Klairr supports a per-connector private-network exception — contact support before connecting.

Step 1: Create a read-only role

CREATE ROLE klairr_reader WITH LOGIN 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;
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.

Step 2: Confirm a read-replica (recommended)

If your Postgres serves production traffic, point Klairr at 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 a replica isolates analytics load from your application.

Step 3: Add the connector

In Klairr, open Settings → Connectors → Add connector, pick PostgreSQL, and enter:

Klairr verifies the credentials by attempting a no-op write inside a rolled-back transaction. A read-only role rejects this; an over-permissioned role is rejected by Klairr.

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. Klairr never runs EXPLAIN ANALYZE, COPY, or any write verb.

Troubleshooting

• Connection refused — confirm the host is publicly reachable, or request the private-network exception via support.
• Permission denied — your role is missing SELECT on at least one table. Re-run the GRANT for ALL TABLES IN SCHEMA.
• “Credentials grant write access” — Klairr’s connect-time probe found the role can create temp tables. Switch to a role with only SELECT privileges.