pg2sqlite

Overview ¶

Command pg2sqlite migrates IAM's xorm-managed Postgres database to the ENVELOPED, per-org SQLite layout the running IAM actually opens. Used during the postgres → SQLite consolidation.

Usage:

IAM_KMS_MASTER_KEY=<64-hex> \
  pg2sqlite -src "user=hanzo password=... host=postgres port=5432 dbname=iam sslmode=disable" \
            -dst /data/iam

-dst is a DATA DIRECTORY (not a single file). The tool writes the same shape the daemon reads:

{dst}/iam.db        (+ .dek)  ← GLOBAL: certs (JWT signing keys), apps,
                                providers, tokens, sessions, roles, … —
                                every table EXCEPT User. Encrypted at rest.
{dst}/orgs/<slug>/iam.db (+ .dek) ← PER-ORG: that org's User rows.
                                    Encrypted with a per-org DEK.

Each db gets its own random DEK wrapped (AES-256-GCM) under a KEK derived from IAM_KMS_MASTER_KEY; the wrapped DEK is the `.dek` sidecar. This is produced by the SAME object-layer primitives the runtime uses (object.NewMigrationTarget → openEncrypted + OrgDBManager), so there is exactly ONE on-disk format and the daemon opens the migrated layout directly — no plaintext staging file that the runtime would refuse (it refuses any db without a sidecar).

Routing mirrors the runtime exactly: only the User table is per-org (orgEngine is called only from user.go); every other table goes to the global engine.

Strategy:

1. Open the source Postgres engine using the same xorm dialect IAM uses.
2. Open the enveloped destination layout (object.NewMigrationTarget) — global engine fully schema-synced; per-org user dbs synced lazily.
3. Iterate every source table, read rows as []map[string]any, and route: the `user` table → per-org engines grouped by owner; all others → global.
4. Report per-table row counts so the operator can verify parity before flipping the cluster to driverName=sqlite.

This avoids xorm.DumpAllToFile round-trips through SQL text (which mis-quote bytea / control chars / DEFAULTs on cross-DB exports). The struct-driven path also surfaces schema drift: any column the destination Sync2 adds (model ahead of the pg DB) is logged.