sqlite2enveloped

Overview ¶

Command sqlite2enveloped migrates IAM's PLAINTEXT SQLite database (the current production at-rest format) to the ENVELOPED, per-org SQLite layout the running IAM opens under IAM_KMS_MASTER_KEY. It is the keystone of the prod encryption cutover: plaintext-at-rest → SQLCipher-encrypted per-org + global dbs, WITHOUT losing a single NULL.

Usage:

IAM_KMS_MASTER_KEY=<64-hex> \
  sqlite2enveloped -src /path/to/plaintext/iam.db -dst /data/iam

-src is the live plaintext iam.db FILE (read-only; never mutated — point it at a COPY for verification). -dst is a DATA DIRECTORY (not a file); the tool writes the exact 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.

WHY A SEPARATE TOOL FROM pg2sqlite. pg2sqlite reads Postgres with xorm's QueryInterface, which returns ""/0/false for a SQL NULL — destroying the NULL-vs-empty-vs-zero distinction at the READ step (before the NULL-honouring INSERT ever runs). Prod IAM is plaintext SQLite, not Postgres, and a faithful migration MUST preserve NULLs. This tool reads the source SQLite with a per-column database/sql `*any` scan, which yields a Go nil for SQL NULL, int64/float64/string/[]byte otherwise — then hands those faithful values to the SHARED object.InsertRows / object.CopyUsersPerOrg write path (database/sql honours nil as NULL). There is exactly ONE write path; only the reader differs.

PARITY IS CONTENT, NOT COUNT. After writing, the tool RE-OPENS the encrypted destination (as the daemon would) and compares, per table, the MULTISET of canonical per-row hashes of the source against the destination. A row hash encodes every column's storage class and value with a NULL sentinel, so a single NULL coerced to "", a bool flipped, or a row dropped/added is detected — which count parity cannot. The tool exits non-zero on ANY mismatch.

Routing mirrors the runtime exactly: only the User table is per-org; every other table goes to the global engine. Encryption is mandatory: it FAILS CLOSED without IAM_KMS_MASTER_KEY (object.NewMigrationTarget refuses to write a plaintext destination) and a CGO+libsqlcipher build refuses to start with the key set but no codec linked.