Onboarding PostgreSQL

To observe a PostgreSQL instance, QueryInsights needs two things from it:

  1. Query statistics — via the pg_stat_statements extension, so the poller can see the queries that run.
  2. Schema access — so the engine can reconstruct table DDL (from information_schema and pg_stats) to ground its recommendations.

This guide creates a dedicated, read-only user that can do exactly those two things and nothing more.

These steps configure a database you want QueryInsights to observe. They are separate from the QueryInsights metastore (QUERYINSIGHTS_DB_URL).

1. Enable pg_stat_statements

pg_stat_statements must be loaded at server start. Add it to shared_preload_libraries in postgresql.conf (or your managed provider's parameter group):

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = 'top'

Restart PostgreSQL for the change to take effect, then create the extension. The statistics are cluster-wide, so creating it once (commonly in the postgres database) is enough:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

On managed platforms (Amazon RDS/Aurora, Cloud SQL, Azure) enable pg_stat_statements through the parameter group / flags UI, then run CREATE EXTENSION as above.

2. Create the read-only user

Create a login role with a strong password supplied from your secret store:

CREATE ROLE queryinsights WITH LOGIN PASSWORD :'qi_password';

Using psql, pass the password without hard-coding it, e.g. psql -v qi_password="$QI_PASSWORD" .... Avoid typing secrets inline.

3. Grant statement-statistics access

Without this, queries from other users appear as <insufficient privilege> and the poller sees almost nothing. The built-in pg_read_all_stats role grants full visibility into pg_stat_statements across every database:

GRANT pg_read_all_stats TO queryinsights;

4. Grant schema-read for a single database

If you only want QueryInsights to read the schema of one database, grant connect + read on that database's schemas. Run this while connected to the target database:

-- Run in the target database (e.g. \c appdb)
GRANT CONNECT ON DATABASE appdb TO queryinsights;
GRANT USAGE ON SCHEMA public TO queryinsights;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO queryinsights;

-- Ensure tables created in the future are readable too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO queryinsights;

SELECT is what makes tables visible to schema reconstruction — a role only sees objects in information_schema that it has privileges on.

5. Grant schema-read for all schemas in the cluster

Simplest (PostgreSQL 14+)

The built-in pg_read_all_data role grants read on every table, view, and sequence, plus USAGE on every schema — exactly the read-only footprint QueryInsights needs:

GRANT pg_read_all_data TO queryinsights;

Combined with step 3, a complete least-privilege setup on PostgreSQL 14+ is:

CREATE ROLE queryinsights WITH LOGIN PASSWORD :'qi_password';
GRANT pg_read_all_stats TO queryinsights;  -- see all queries
GRANT pg_read_all_data  TO queryinsights;  -- read all schemas (read-only)

Explicit (all schemas, older PostgreSQL)

On versions without pg_read_all_data, grant read across every non-system schema in the current database. This uses format(%I) for safe identifier quoting and only ever grants SELECT/USAGE, so it stays read-only. Run it in each database you want covered:

DO $$
DECLARE
  schema_name text;
BEGIN
  FOR schema_name IN
    SELECT nspname FROM pg_namespace
    WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
      AND nspname NOT LIKE 'pg_temp%'
      AND nspname NOT LIKE 'pg_toast_temp%'
  LOOP
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO queryinsights', schema_name);
    EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO queryinsights', schema_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO queryinsights', schema_name);
  END LOOP;
END $$;

6. Point QueryInsights at the instance

Add the instance to QUERYINSIGHTS_POSTGRES_INSTANCES, using the read-only user and sslmode=require (or stricter) in production:

export QUERYINSIGHTS_POSTGRES_INSTANCES='[
  {
    "name": "prod",
    "dsn": "postgres://queryinsights:REPLACE_WITH_PASSWORD@db.internal:5432/appdb?sslmode=require",
    "sampling_rate": 1.0,
    "poll_interval_seconds": 10
  }
]'

See Configuration for every field.

7. Verify

Run these as the queryinsights user to confirm access:

-- Statistics are visible (should return a count > 0 on a busy server)
SELECT count(*) FROM pg_stat_statements;

-- Schema is readable (should list the target database's tables)
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY 1, 2
LIMIT 20;

If the first query errors or returns nothing, re-check steps 1 and 3. If the second returns no rows, re-check steps 4–5.

Security notes

  • The user is read-only — it never needs INSERT, UPDATE, DELETE, or DDL.
  • Keep the password in a secret manager; inject the DSN at runtime.
  • Prefer sslmode=require, or verify-full with a pinned CA, for connections that leave the host.
  • Scope access to only the databases you intend to analyze if you don't need cluster-wide coverage — use step 4 instead of step 5.