feat(cli): ClickHouse Managed Postgres provider

Nikolay Samokhvalovrequested to merge
feature/clickhouse-managed-postgres into main

What

clickhouse becomes a known provider in the CLI, for ClickHouse Managed Postgres (public beta on AWS).

  • prepare-db / unprepare-db detect the provider from the host (*.pg.clickhouse.cloud) and print Provider: clickhouse (detected from host). An explicit --provider always wins.
  • ClickHouse hands the customer a real superuser, so the plan is the self-managed one: postgres_ai_mon gets what that plan grants: role, pg_stat_statements, pg_monitor, pg_read_all_stats, select on pg_catalog.pg_index, the postgres_ai schema (view pg_statistic, SECURITY DEFINER table_describe), usage on public, a persistent search_path, and with optional permissions pg_ls_dir/pg_stat_file (plus rds_tools.pg_ls_multixactdir where it exists).
  • Before the first query, prepare-db prints one -- scope: line derived from the plan steps about to run (describeInitScope), listing every grant above; it shrinks under --skip-optional-permissions and is omitted on --reset-password, which grants nothing. Under --json both notes go to stderr, so stdout stays valid JSON.
  • channel_binding=require in a URI (ClickHouse's console hands out such URLs) now enables SCRAM channel binding in node-postgres. Before, the parameter was silently ignored. Other values are stripped.

Tests

  • cli/test/clickhouse-provider.test.ts: provider detection table (including look-alike hosts), the plan equals the self-managed one, the scope line comes before the SQL, and channel binding in both connection paths.
  • cli/test/clickhouse-provider.integration.test.ts and the CI job cli:clickhouse-like:tests: stock postgres:17 and postgres:18 with only pg_stat_statements preloaded. That matches ClickHouse's offering, which has no pg_stat_kcache, pg_wait_sampling or auto_explain. The job runs prepare-db, then --verify, then checks the role is not superuser but can read pg_stat_statements. --json keeps stdout pure JSON, with the scope line on stderr. As postgres_ai_mon, it runs the SQL of every metric in the pgwatch full preset (50 Prometheus-sink and 2 Postgres-sink queries, each picked for the server major). Failures are frozen in clickhouse-like-collector-failures.golden.json, which is [], so the collectors work unchanged on this extension set. It ends with unprepare-db.
  • Red first (67cbe35, 6b9b27b), then green (7d8f5b5, a877ce6). Locally: 30/30 unit tests, 6/6 integration tests on PG 17 and on PG 18.
  • Real cloud (ClickHouse, r6gd.medium PG17, created and deleted in about 7 min): the provider was detected from the host, the scope line was printed, and prepare-db and --verify passed as the service's superuser.
  • Full-stack acceptance on real ClickHouse Cloud, 2026-09-28, together with the stacked !436 (closed): we ran the monitoring stack locally against one r6gd.medium PG17 service (created 07:52 UTC, deleted 08:16 UTC; the org's service list is empty afterwards). 16 min of collection: all 21 host-metric families arrived, pgwatch stored 213 metric names, and the reporter built 25 checks with no errors. D004 reports pg_stat_kcache unavailable and degrades cleanly. The run found two bugs (mon targets add rejected postgres://; pgwatch failed on channel_binding=require); both are fixed red/green in !436 (closed).

Not in this MR

  • Host metrics from the ClickHouse Cloud Prometheus endpoint: stacked MR.

Relates to https://gitlab.com/postgres-ai/internal/-/issues/354

🤖 Generated with Claude Code

Review round 1 (5a3b6c6a)

  • Blocking: scope line now derived from the plan (see above).
  • channel_binding=require: parsed from conninfo too; disables the plaintext retry; rejected with sslmode=disable. The negotiated mechanism is still driver-preferred, not enforced (documented in README).
  • Detection also from --host/PGHOST, trailing-dot FQDN tolerated.
  • --verify --json emits provider only when explicit or detected.
  • Process-level tests for the detected branch (prepare-db and unprepare-db, plain and --json), --skip-optional-permissions, --reset-password; CI sentinel for the DB URL; vacuous assertion dropped.
  • README "ClickHouse Managed Postgres" section, CHANGELOG entry, JSDoc.
  • Not changed: commit subjects in the pushed history, and the .cursor submodule bump.

Executed on postgres:17 with pg_stat_statements (unit 47/47, integration 7/7):

mr434-prepare-db-scope

Round 2 (c20bfef4): table_describe cannot be hijacked through public

On ClickHouse the owner of postgres_ai.table_describe is a real superuser, and the function had search_path = pg_catalog, public. Any role that can create in public could add an exact-type overload that outranks the built-in and runs as the owner. We reproduced this on PG 17: a public.format(text, text, text) created by postgres_ai_mon (after a grant create on schema public) made table_describe fail with "hijacked as postgres".

  • search_path is now pg_catalog, pg_temp. Unqualified names keep their old meaning (pg_catalog, then public) through a catalog lookup; qualified names still go through regclass. We kept SECURITY DEFINER because regclass needs USAGE on the schema, which the monitoring role does not have on customer schemas.
  • A cross-model review of that change found that PG 18 stores NOT NULL constraints as contype 'n', which hit text || "char" ("operator is not unique"). Fixed with a cast. Unqualified names are compared as name, so an overlong name is truncated the way regclass truncates it.
  • Test: table_describe cannot be hijacked through objects in public in clickhouse-provider.integration.test.ts. It creates overloads of array_append and format in public as the monitoring role, then describes a public table, a table in another schema, pg_class, and a missing name (42P01).
  • Red first (ac3b5c0: hijacked on 17, operator error on 18), then green (375d3a7, c20bfef). Integration 7/7 on postgres:17 and on postgres:18.
  • Real ClickHouse (r6gd.medium PG 17, 2026-09-30): the admin is rolsuper = true, proconfig is search_path=pg_catalog, pg_temp, and with a public.format(text, text, text) overload present, table_describe('acc_probe') returned Table: public.acc_probe.

Behaviour changes: unqualified temp tables are no longer found (use pg_temp.name), public tables print schema-qualified in "Referenced by", and malformed identifiers raise 22023 instead of 42602.

What we did not check

  • Callers that relied on the old output format of "Referenced by".
  • The Supabase copy of this function in platform-all (!541) is a separate change.

Round 3 (review of 2026-09-29, round 2)

  • table_describe is SECURITY INVOKER, as in the platform generator (platform-all !541). It reads only the world-readable catalog, so it needs nothing the caller lacks. Qualified names now resolve through pg_class/pg_namespace instead of regclass, which demands USAGE on the schema; that was the only reason round 2 kept SECURITY DEFINER. search_path stays pg_catalog, pg_temp. Names regclass accepted keep working: pg_temp.<name> (the caller's temp schema) and <current database>.<schema>.<name>. The scope line now says execute on postgres_ai.table_describe (SECURITY INVOKER, catalog only).
  • One channel_binding parser. splitChannelBinding and requireChannelBinding (cli/lib/instances.ts) replace the two URL parses and the duplicated "needs TLS" error in init.ts and instances.ts; !436 (closed) uses the same helper for the collector URL.
  • Tests: clickhouse-provider.integration.test.ts asserts prosecdef = false and proconfig = {search_path=pg_catalog, pg_temp}, describes a table in a schema the monitoring role has no USAGE on, a temp table, a database-qualified name, and still resists public overloads. Red b4deef4, b5927c8; green 8d0b2bd, 5738637. 7/7 on stock postgres:15, 17 and 18; CI's cli:clickhouse-like:tests matrix now includes 15.
  • Real ClickHouse Managed Postgres (r6gd.medium, PostgreSQL 17.11, 2026-09-30): prepare-db from this branch exited 0 with the INVOKER scope line; as postgres_ai_mon, prosecdef was false, table_describe('r2_app.orders') described a table in a schema it has no USAGE on, table_describe('r2_probe') worked with a public.format(text, text, text) overload present, pg_class resolved in pg_catalog, and --verify exited 0. The service was deleted; its API URL returns 404.
  • Own delta: code +194/−32 (was +183/−27), tests +381 (was +368).
  • Not changed: announceScope and the resolveProvider notes (round-2 cut list for this MR).

🤖 Generated with Claude Code

https://claude.ai/code/session_01GRNbWXUQJebE3vqWjAgTFh

Edited by Nikolay Samokhvalov

Merge request reports