Onboarding CockroachDB

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

1. Statement statistics — the poller reads crdb_internal.statement_statistics. Unlike PostgreSQL, this is built in; there is no extension to install.
2. Schema access — so the engine can run SHOW CREATE TABLE to ground its recommendations in your real DDL.

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

1. Create the read-only user

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

CREATE USER queryinsights WITH PASSWORD :'qi_password';

Using the cockroach sql client, pass the password without hard-coding it, e.g. --set qi_password="$QI_PASSWORD". Avoid typing secrets inline.

2. Grant statement-statistics visibility

By default a non-admin user only sees its own statements in crdb_internal.statement_statistics. The VIEWACTIVITY system privilege lets the user see cluster-wide activity, which is what the poller needs:

GRANT SYSTEM VIEWACTIVITY TO queryinsights;

On older CockroachDB versions that predate system privileges, use the equivalent role option instead: ALTER ROLE queryinsights WITH VIEWACTIVITY;

If you prefer that statement constants be redacted, grant VIEWACTIVITYREDACTED instead — the poller normalizes queries either way.

3. Grant schema-read for a single database

To let QueryInsights read the schema of one database, grant connect + read on its schemas. CockroachDB privileges are scoped per database, so switch into the target database first:

GRANT CONNECT ON DATABASE appdb TO queryinsights;

USE appdb;
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 FOR ALL ROLES IN SCHEMA public
  GRANT SELECT ON TABLES TO queryinsights;

SELECT is enough for SHOW CREATE TABLE to succeed on each table.

4. Grant schema-read for all schemas / all databases

CockroachDB grants are scoped to a database, so "everything" means repeating the read grants for each schema in each database you want covered.

Multiple schemas in one database — list them in a single statement:

USE appdb;
GRANT USAGE  ON SCHEMA public, analytics, billing TO queryinsights;
GRANT SELECT ON ALL TABLES IN SCHEMA public, analytics, billing TO queryinsights;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES IN SCHEMA public, analytics, billing
  GRANT SELECT ON TABLES TO queryinsights;

Discover the schemas in a database with:

SHOW SCHEMAS FROM appdb;

Multiple databases — repeat step 3 (and the block above) for each database, switching with USE <database>; and granting CONNECT ON DATABASE <database> each time. List databases with SHOW DATABASES;.

The grants only ever include CONNECT, USAGE, and SELECT, so the user stays strictly read-only.

5. Point QueryInsights at the instance

Add the instance to QUERYINSIGHTS_COCKROACH_INSTANCES, using the read-only user and a secure sslmode (verify-full is recommended for CockroachDB):

export QUERYINSIGHTS_COCKROACH_INSTANCES='[
  {
    "name": "prod",
    "dsn": "postgresql://queryinsights:REPLACE_WITH_PASSWORD@crdb.internal:26257/appdb?sslmode=verify-full",
    "sampling_rate": 0.5,
    "poll_interval_seconds": 10
  }
]'

See Configuration for every field.

6. Verify

Run these as the queryinsights user to confirm access:

-- Statement statistics are visible cluster-wide
SELECT count(*) FROM crdb_internal.statement_statistics;

-- Schema is readable
SHOW TABLES FROM appdb;

-- DDL reconstruction works (pick any table from the list above)
SHOW CREATE TABLE appdb.public.your_table;

If the first query returns only your own statements (or none), re-check step 2. If SHOW CREATE TABLE is denied, re-check steps 3–4.

Security notes

• The user is read-only — it never needs write or DDL privileges, and it is never made an admin.
• VIEWACTIVITY grants visibility into activity metadata only; it is not a write or superuser capability.
• Keep the password in a secret manager and inject the DSN at runtime.
• Prefer sslmode=verify-full with a pinned CA for any connection that leaves the host.
• Scope grants to only the databases you intend to analyze rather than the whole cluster when you don't need full coverage.