kimo
Open demo
Docs

Connect PostgreSQL

Connect a PostgreSQL database with a read-only user, optionally through an SSH tunnel or PrivateLink, and choose between cached and live query modes.

Updated Oct 4, 20267 min readEdit on GitHub
Works with PostgreSQL 11+ and managed variants: Amazon RDS & Aurora, Cloud SQL, Azure Database, Supabase, Neon.

The PostgreSQL connector reads tables and views from one or more schemas. Kimo never writes to your database: it only needs SELECT on the objects you want to model, plus access to information_schema to discover columns and keys.

Create a read-only user

Run the following as a superuser or the owner of the schema. Replace the password and schema names with your own. Using a dedicated role makes it easy to audit and revoke Kimo’s access later.

kimo_reader.sql
CREATE ROLE kimo_reader WITH LOGIN PASSWORD 'change-me';GRANT CONNECT ON DATABASE app TO kimo_reader;GRANT USAGE ON SCHEMA public, billing TO kimo_reader;GRANT SELECT ON ALL TABLES IN SCHEMA public, billing TO kimo_reader;-- Keep future tables readable tooALTER DEFAULT PRIVILEGES IN SCHEMA public, billing  GRANT SELECT ON TABLES TO kimo_reader;

Add the source in Kimo

  1. 1
    Open Connectors → Add source → PostgreSQL

    Give the source a name people will recognize, such as "Production DB (replica)".

  2. 2
    Enter connection details

    Host, port (default 5432), database, user and password. Kimo requires TLS; choose verify-full and paste your CA certificate if your provider uses a private CA.

  3. 3
    Pick schemas and tables

    Kimo lists every schema the user can see. Select the ones to sync; you can add more later without re-authenticating.

  4. 4
    Choose a query mode

    See the table below. You can switch modes at any time; switching to cached triggers a full initial sync.

ModeHow it worksUse when
Cached (default)Incremental syncs into Kimo’s columnar cache every 15 min.Most analytics; protects your primary from heavy queries.
LiveQueries are pushed down to Postgres at view time.Read replicas, small tables, data that must never leave the network.
HybridLarge fact tables cached, small dimension tables live.Big event tables joined to frequently edited reference data.

Private networks

If the database is not reachable from the internet, use one of three options. SSH tunnel: Kimo connects through a bastion host with a key it generates for you. AWS PrivateLink / GCP Private Service Connect: available on Business and Enterprise. Kimo Agent: a small container you run inside your network that opens an outbound-only connection, ideal for on-prem databases.

docker run -d --name kimo-agent --restart unless-stopped \  -e KIMO_AGENT_TOKEN=agt_live_3f9c… \  -e KIMO_REGION=eu \  ghcr.io/kimo/agent:2.14

Troubleshooting

ErrorLikely cause
28P01 password authentication failedWrong password, or pg_hba.conf rejects the IP.
42501 permission denied for tableMissing GRANT SELECT or default privileges.
timeout expiredFirewall: allowlist Kimo egress IPs or use the agent.
SSL offServer does not accept TLS; enable ssl = on.

Impact on your database

Kimo is designed to be a polite guest. Incremental syncs read only rows whose cursor moved, run with a statement_timeout of 5 minutes and use at most two concurrent connections per source by default. On the first sync, large tables are read in primary-key ranges of 50,000 rows so no single query holds locks for long. If you can, point Kimo at a read replica: replication lag of a few seconds is invisible on dashboards and your primary never sees analytics traffic.

  • application_name = kimo is set automatically, so you can filter Kimo sessions in pg_stat_activity.
  • Raise or lower concurrency under Source → Advanced → Max connections.
  • Logical replication (CDC) is available on Enterprise for tables that change faster than every 5 minutes.