Docs / PostgreSQL

Connect PostgreSQL

Open-source relational database

PostgreSQL connects with ordinary database credentials. The setup below creates a user that can read and nothing else — climpt never writes, so anything beyond SELECT is access it will not use.

Credentials

Host
Hostname or IP of the server, for example db.example.com.
Port
5432 unless you have changed it.
Database
The database to query, for example analytics.
Username
The read-only user created below.
Password
That user’s password.

Creating a read-only user

Run these as a superuser, substituting your own database name and a strong password.

1 · The user itself

CREATE USER climpt_readonly WITH
  PASSWORD 'strong_secure_password_here'
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT
  CONNECTION LIMIT 10;

2 · Connection and read access

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

-- Optional: only if you need sequence values
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO climpt_readonly;

3 · Enforce read-only

REVOKE CREATE ON SCHEMA public FROM climpt_readonly;
REVOKE CREATE ON DATABASE analytics FROM climpt_readonly;
The step most people miss

GRANT SELECT ON ALL TABLES covers the tables that exist right now. Without the statement below, any table created later is invisible to climpt, and the failure looks like missing data rather than a permission problem.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO climpt_readonly;

Restricting where connections come from

On a self-managed server, add a line to pg_hba.conf limiting this user to the address climpt connects from, then reload. Never use 0.0.0.0/0.

host    analytics    climpt_readonly    10.0.1.50/32    scram-sha-256

Reload with sudo systemctl reload postgresql. Prefer scram-sha-256 over md5, and check that listen_addresses in postgresql.conf is set to what you expect.

Managed PostgreSQL — RDS, Cloud SQL, Azure

On a managed service you cannot edit pg_hba.conf or postgresql.conf. Every SQL statement above still applies unchanged; only the network configuration moves into the provider’s console.

  • AWS RDS — security groups and parameter groups.
  • Google Cloud SQL — authorized networks and database flags.
  • Azure Database — firewall rules and server parameters.

Worth knowing

  • Use sslmode=require at minimum, verify-full if you can supply the CA certificate. Never disable SSL on a database reachable over a network.
  • Open port 5432 only to the addresses that need it.
  • The connection limit prevents climpt from exhausting the server’s connection pool.
  • REVOKE CREATE is what makes the account genuinely read-only rather than read-only by convention.
  • Rotate the password roughly every 90 days.
  • Enable statement logging if you want an independent record of what climpt runs.