feat(cli): pgai connect — put a database under PostgresAI's care

Nikolay Samokhvalovrequested to merge
feat/pgai-connect into feat/unified-host-metrics

UAT round 3 fixes (2026-10-02)

  • Check the URL before asking for money. connect runs prepare with check first: every refusal is reported, and nothing is created or changed. Only then does it quote. A non-admin URL now gets the SQL, and is never asked to accept $500 first. This reverses the earlier "an SQL stop keeps the price".
  • The price before the prompt. Billing: <price> is printed on its own line, then Provision <name>? (y/N): . Before, a long name made the prompt wrap, and the captured screen showed only the question.
  • The billing link is the platform's. It is the quote's billing_url (platform-all!962), else the configured UI base.
  • pgai disconnect says the billing. The billing: line reads one of:
    • "subscription canceled: no further charges (the current period is not refunded)";
    • "N boxes left on the subscription";
    • "not released (…): contact support".
  • The terminal price-prompt test needs a server for the check, so it moved to the real-Postgres job (connect.integration.test.ts).
  • Second database on one server: a separate MR, stacked on this one.

What

pgai connect <database-url> puts a database under PostgresAI's care in one command (plan (k), CLI design as decided on postgres-ai/internal#354).

pgai connect 'postgres://postgres:<pw>@<svc>.pg.clickhouse.cloud:5432/postgres?sslmode=require' \
  --clickhouse-key <key-id>:<key-secret>

Each step is skipped when it is already done, so a re-run is safe:

  1. Sign in. If there is no API key, an interactive terminal runs pgai auth login. Otherwise the result is action_required with the next step (agents set PGAI_API_KEY).
  2. Prepare the database.
    • An admin URL creates postgres_ai_mon through the existing prepare-db plan. Admin means a superuser, or a CREATEROLE role that can run the whole plan (CREATE on the database, pg_stat_statements installed, and on PG 16+ ADMIN OPTION on pg_monitor and pg_read_all_stats).
    • The postgres_ai_mon URL is verified.
    • Any other role gets the SQL (passwords redacted) and the next step.
    • An existing postgres_ai_mon is never re-passworded silently, because the role is cluster-wide: pass PGAI_MON_PASSWORD, or use its URL.
    • The monitoring URL names the prepared database and carries only the new role. Of the query string it keeps sslmode, channel_binding and application_name; certificate files (sslrootcert, sslcert, sslkey) are used from this machine only. host / port in the query string is refused.
    • When the role exists, one login with a random password tells whether the server checks passwords for this host; if it does not, next says that the password was not checked, and a password from PGPASSWORD is not sent on.
    • A failed run can be re-run: a role this run created with a generated password is dropped again when the platform refuses the launch (a failed reply or a 4xx) or a later step of the plan fails. After a 5xx or no answer the role stays (a box may be starting with it), and the re-run says how to reset the password.
  3. Provider. Detected from the host: *.clickhouse.cloud, *.rds.amazonaws.com, *.supabase.co / pooler.supabase.com, else self-managed. --provider overrides.
    • ClickHouse: --clickhouse-key (or CLICKHOUSE_KEY_ID + CLICKHOUSE_KEY_SECRET) finds the Cloud organization from the key itself and checks that the service is running, before the database is touched. A Basic Service API Reader key is enough.
    • RDS and Supabase: the result points to their console flow (<console>/<org>/monitoring/scale/create/{rds,supabase}).
  4. Provision through platform-all rpc/cloud_monitoring_connect, wait (--wait <min>, default 20), then print dashboard_url and first_checkup_eta.

Output and exit codes. When stdout is not a TTY (or with --json), the result is JSON with status (connected, provisioning, disconnecting, action_required, failed; pgai disconnect prints disconnected), dashboard_url and next. Exit 0 means connected, provisioning, disconnecting or disconnected, 1 failed, 3 action required; pgai init exits with 130 when cancelled at a prompt.

Other commands

  • pgai init: sign in, ask for the URL, run pgai connect.
  • pgai databases, pgai status [name], pgai disconnect <name> (non-interactive use needs --yes).
  • pgai mon ... is unchanged.
  • --self-hosted runs a child mon local-install. The monitoring URL and keys go in its environment (PGAI_DB_URL, PGAI_API_KEY, CLICKHOUSE_*), never argv. If a stack already runs on the machine, the result is an action, not a false success.
  • MCP tool connect_database: the same JSON; it does not wait. The URL comes from an agent, so it differs from the CLI: the URL must carry its password and only the query parameters sslmode, channel_binding, application_name; PGAI_MON_PASSWORD, PGPASSWORD and CLICKHOUSE_KEY_ID + CLICKHOUSE_KEY_SECRET of the server process are not read; a TLS failure is not retried in plaintext; there is no --self-hosted.

Changed for every command that takes a URL. sslmode in the URL now wins over PGSSLMODE, as in libpq (before, PGSSLMODE=disable turned ?sslmode=verify-full into plaintext).

Shared code. callRpc (joe.ts) is exported and takes an org scope. The debug-log redactor now covers db_url and *_key_secret.

Depends on

  • The branch is on top of !442 (closed) (feat/unified-host-metrics, which carries !434 (closed)/!436 (closed)/!440 (closed)): ClickHouse detection, mon local-install host metrics, the host_* rules. Merge after it.
  • The platform API is postgres-ai/platform-all!894.
  • The box side is postgres-ai/postgres_ai-ansible!51.
  • This MR targets feat/unified-host-metrics so its diff is only this change; GitLab retargets it to main when !442 (closed) merges.

Tests (red, then green)

  • test/connect.test.ts: the step machine with every effect faked, whole results frozen. Cases:
    • the happy path with a key
    • already connected
    • RDS/Supabase hand-off
    • SQL-only
    • a stopped service
    • a refused launch
    • a failed box
    • --wait 0
    • no key
    • self-hosted, and self-hosted with a stack already running
    • a disconnect still in flight
    • bad input
    • the ClickHouse org lookup against a fake Cloud API
    • the MCP tool
    • debug redaction
  • test/connect.cli.test.ts: the commands as an agent runs them: no TTY, a fake platform API, a throwaway HOME. Covers the JSON contract, exit codes 0/1/3, disconnect without --yes, and a bad --wait.
  • test/connect.integration.test.ts: the prepare step against real Postgres 15/17/18 over SCRAM, in the cli:clickhouse-like:tests job. Covers:
    • a superuser URL with credentials in the query string and no database
    • the monitoring URL connects as postgres_ai_mon to the right database
    • no silent re-password
    • the SQL for an unprivileged role, as JSON and as printed at a terminal
    • CREATEROLE roles: lacking one thing the plan needs, with all of it, and with a later step failing
    • a login role whose session runs as another role
    • re-runs after a rejected key and a refused launch; the --self-hosted child
  • CI at fed2b269 (pipeline 2897911559): cli:node:tests 2255 pass, 38 skipped, 0 fail; cli:clickhouse-like:tests green on PG 15, 17 and 18. Until fed2b269 that job's Postgres trusted the job container (a global POSTGRES_HOST_AUTH_METHOD: trust from components/index_pilot/.gitlab-ci.yml), so the wrong-password refusals were not asserted in CI; the job now sets scram-sha-256 and the test fails in CI if the server does not check passwords.
  • Two security reviews by a second model. Every blocking finding was fixed red, then green: debug-log redaction, URL normalization, no silent re-password, and on the self-hosted child: env instead of argv, org and base URL carried over, and output to stderr.

Size at fed2b269 (18 files, +2567/-34, including !336's scram.ts and init.ts changes until it merges): code +804/-24, tests +1655/-10, docs +108.

Acceptance on real ClickHouse Cloud (2026-09-30)

This run predates the review fixes (cf1eedb9, 0729a1e1, ad5f8f02, fed2b269) and was not repeated after them. The fixes were run by hand with the real CLI against real PostgreSQL 15/17 and a platform stand-in; the screenshots are in the MR notes.

Setup. One ClickHouse Managed Postgres service, r6gd.medium, PG 17, us-east-1. It was created 07:11:46 UTC, was running at 07:17:21, and was deleted at 07:25:43. GET returned 404 at 07:25:49, and the org has no services left. For the run we created a separate Basic Service API Reader key and deleted it afterwards.

Stand-ins. We ran the platform locally, not on staging, and the box launch was a local stand-in, not a real SI/Hetzner VM:

  • Platform: this branch's migration on a local stack (.ci compose: Postgres and PostgREST).
  • SI /launch: a stand-in that runs what postgres_ai-ansible runs on the box. That is the same postgresai mon local-install, with the env the role builds from the extraVars (including the three new CLICKHOUSE_* lines), in an isolated compose project on this machine, with images feat-unified-host-metrics.
  • credential-service /encrypt: a stand-in doing AES-GCM.

The user's side: pgai connect '<admin URL>' --clickhouse-key <reader key>, with no TTY and a throwaway HOME.

  1. First attempt. Prepare ran, the org was found from the reader key, and provisioning started. The platform encrypted the key, and SI got a platform-signed bearer. The box install then failed: our stand-in hit a container-name clash on this machine. The CLI reported failed, exit 1, with next. pgai disconnect --yes destroyed the box and revoked the key, with an audit row.
  2. Re-run.
    • action_required, exit 3: postgres_ai_mon already exists … PGAI_MON_PASSWORD. No silent re-password.
    • With PGAI_MON_PASSWORD set: connected in 80 s (07:19:50 to 07:21:10), with dashboard_url, host_metrics: true and first_checkup_eta.
    • Running it once more: connected, exit 0, nothing prepared or provisioned.
  3. Platform checks.
    • The instance is active with state.provider = clickhouse.
    • clickhouse_credentials holds 80 bytes of ciphertext.
    • The plaintext secret appears in no credential, instance-state or audit row: 0 matches.
    • _audit has use and revoke rows per instance.
  4. Box checks.
    • The ClickHouse scrape was up=1 with the reader key. That key also listed the organization and the service, so no org id was needed.
    • The host metrics files: key 0600, directory 0700.
    • 30 PostgresServer_* series, and the recording rules produced 10 host_* families.
    • pgwatch and host series share cluster=default and node_name=<instance>.
  5. Dashboard 01, Host row, queried through Grafana with the cluster and node variables. Every panel had points: CPU utilization 4, Memory 9, Storage 3, Disk IOPS 4, Network 4.
  6. First checkup from monitoring data. The stack's reporter ran once (initial delay set to 0; it is 30 min by default). It built and uploaded 25 checks (A002…N001) to the platform, under the connected database's project (checkup report 1041).
  7. pgai disconnect <name> --yes launched the destroy (platform-signed) and the box was removed. The instance went to deleted, the key was marked revoked, and a revoke audit row was written. pgai databases then showed [].

The run found three CLI bugs; each was fixed red, then green:

  • a re-connect waited on an instance still being deleted
  • pgai status showed that instance as provisioning
  • disconnect picked it instead of the live one

Cost. ClickHouse Cloud usageCost for the day reads 0.036 CHC, about $0.04; the figure may still lag. The service ran 14 min. The local stack cost nothing.

What we did not check

  • A real SI/Hetzner box: the acceptance run used a local stand-in (see Acceptance).
  • A browser sign-in inside connect: we used PGAI_API_KEY.
  • RDS and Supabase end to end: they hand off to flows being built separately.

Part of postgres-ai/internal#354

Edited by Nikolay Samokhvalov

Merge request reports