feat(cli): implement "postgresai init" CLI to create DB role and grant permissions + publish NPM package from CI pipelines

Nikolay Samokhvalovrequested to merge
feat/postgresai-init-clean into main

The problem

We have a snippet in README that defines how to set up user and permissions and additional stuff for monitoring: https://gitlab.com/postgres-ai/postgres_ai#-quick-start

Sometimes it's executed manually, sometimes automatically, and we have already several versions of that and no good source of truth to be used programmatically.

Additionally, RDS and some other environments need specific extras, so we have conditional parts. Having them w/o errors is possible -- anonymous PLpgSQL DO blocks -- but this makes code users need to copy-paste cumbersome.

The idea

Implement init command for CLI that would run everything needed -- user (re)creation with password (re)generated, permissions, extras.

With npx, it becomes one-liner, that doesn't need context.

How to use 

export PGPASSWORD='xxxxxxx'
npx postgresai@0.14.0-dev.8 init "host=demo.dblab.dev port=6001 user=tester dbname=postgres"

After merging, it is supposed to be:

npx postgresai init <creds>
# or: npx pgai init <creds>

Admin password options:

  • --admin-password ...
  • or PGPASSWORD=... (psql-style)

Example 

% PGPASSWORD='xxxxxx' npx postgresai@0.14.0-dev.9 init "host=demo.dblab.dev port=6001 user=tester dbname=postgres"
Need to install the following packages:
postgresai@0.14.0-dev.9
Ok to proceed? (y)

Connecting to: postgresql://tester:*****@demo.dblab.dev:6001/postgres
Monitoring user: postgres_ai_mon
Optional permissions: enabled
Generated password for monitoring user postgres_ai_mon: pbo9HlqU9UL9DcMtIN3lQL3vzfYV4jNh
Store it securely (or rerun with --password / PGAI_MON_PASSWORD to set your own).
✓ init completed
⚠ Some optional steps were skipped (not supported or insufficient privileges):
- create rds_tools extension (optional)
- grant rds_tools.pg_ls_multixactdir() (optional)
Applied 12 steps

What's implemented in this MR

Monitoring user password

Monitoring user password options (priority order):

  • --password <password>
  • PGAI_MON_PASSWORD env var
  • interactive prompt (TTY only, masked input)

Optional permissions:

  • enabled by default
  • disable via --skip-optional-permissions

What changed

  • CLI command

    • Added init command wiring in cli/bin/postgres-ai.ts
    • Connection parsing supports URI / conninfo / psql-like options
    • Uses select current_database() to avoid relying on URI parsing for DB name
    • Ensures client.end() always runs (no connection leaks on failures)
    • Errors include the failing step and provide a helpful hint for insufficient privileges

  • Init implementation

    • New cli/lib/init.ts:
      • Builds an idempotent init plan (role create/update + grants + view)
      • Applies required steps in a transaction
      • Applies optional steps best-effort (won’t break the init if unsupported)
      • Masks secrets in logs and supports masked password prompt

  • Docs

    • Updated cli/README.md with postgresai init examples for all connection formats and password input methods.

Tests

  • Unit tests (cli/test/init.test.cjs)

    • conninfo parsing
    • connection resolution (URI + conninfo)
    • init plan generation

  • Integration tests (cli/test/init.integration.test.cjs)

    • Spins up a temporary Postgres cluster and verifies:
      • all connection styles work
      • missing/incorrect permissions are auto-fixed on rerun (idempotency)
      • lack of admin privileges fails with clear errors + hints

CI changes

  • Added cli:node:tests job:

    • runs CLI unit + integration tests in CI
    • installs PostgreSQL binaries required by integration tests
    • runs as a non-root user (because initdb cannot run as root)

  • Added cli:npm:publish job:

    • publishes postgresai to npm for dev.N / beta.N tags
    • uses NPM_TOKEN (masked GitLab variable)
    • derives npm version from the tag using npm version --no-git-tag-version (no version-bump commits needed)

Notes

  • This MR also includes several robustness fixes discovered during CI bring-up:
    • initdb non-root constraint handled in CI
    • integration test socket connections now pass the correct port (pg defaults to 5432 otherwise)
    • tag parsing fixes for dev/beta tags in publish job
Edited by Nikolay Samokhvalov

Merge request reports