shomei-migrations 0.2.0.0 → 0.2.0.1

raw patch · 12 files changed

+292/−61 lines, 12 filesdep +directorydep +unixdep ~ephemeral-pgdep ~pg-migratedep ~pg-migrate-cli

Dependencies added: directory, unix

Dependency ranges changed: ephemeral-pg, pg-migrate, pg-migrate-cli, pg-migrate-embed, pg-migrate-test-support, shomei-migrations

Files

• CHANGELOG.md +12/−0
• migrations/shomei/0037-remembered-sessions.sql +9/−0
• migrations/shomei/0038-add-totp-envelope-version.sql +9/−0
• migrations/shomei/0039-enforce-new-field-budgets.sql +46/−0
• migrations/shomei/0040-add-session-keyset-index.sql +5/−0
• migrations/shomei/0041-add-active-session-principal-indexes.sql +9/−0
• migrations/shomei/0042-add-admin-keyset-indexes.sql +11/−0
• migrations/shomei/0043-enforce-session-provenance-upgrades.sql +31/−0
• migrations/shomei/manifest +7/−0
• shomei-migrations.cabal +76/−59
• test-support/Shomei/Migrations/TestSupport.hs +14/−1
• test/Main.hs +63/−1

@@ -4,6 +4,18 @@ [PVP](https://pvp.haskell.org/) and is versioned independently of the other Shōmei packages. +## 0.2.0.1 — 2026-11-05++- Keep the exported Haskell API unchanged; require the released [`pg-migrate`](mori://shinzui/pg-migrate/packages/pg-migrate) 1.2 family and [`ephemeral-pg`](mori://shinzui/ephemeral-pg/packages/ephemeral-pg) 0.3.1, with stable per-user test database cleanup roots.+- Migration 0038 versions TOTP envelopes for the required offline conversion; 0039 enforces new-write field budgets, and 0040–0041 add session paging and active-principal indexes.+- Before upgrading, stop all writers, back up the database and retain the TOTP key, then apply migrations and convert legacy TOTP envelopes. Follow the deployment and MFA upgrade guides; old issuers must stay stopped during provenance repair.++- **Upgrade:** migration 0043 repairs legacy machine/delegated provenance and makes omitted SQL provenance default to machine. Missing kinds fail closed on read; authorize also refuses a stored actor. Stop old issuers before migration; affected backing-account humans can establish a new interactive session by logging in. See deployment.md.++- Migration 0042 adds measured ordered user/status and per-user audit indexes.++- `0037-remembered-sessions` adds `shomei_sessions.remembered boolean NOT NULL DEFAULT true`.+ ## 0.2.0.0 — 2026-08-27  - **Breaking:** every migration in the 36-file history was rewritten to schema-qualify each Shōmei

@@ -0,0 +1,9 @@+-- remembered-sessions++SET LOCAL search_path = pg_catalog, pg_temp;++-- Whether the person asked to stay signed in when this session began. A remembered session gets+-- the longer remembered lifetime, and its embedder keeps its cookies across browser restarts.+-- Rows that predate the column default to true, so their cookies stay persistent as before.+ALTER TABLE shomei.shomei_sessions+  ADD COLUMN IF NOT EXISTS remembered boolean NOT NULL DEFAULT true;

@@ -0,0 +1,9 @@+-- add-totp-envelope-version++SET LOCAL search_path = pg_catalog, pg_temp;++-- Existing rows are explicitly legacy; only the offline converter may read version 0.+ALTER TABLE shomei.shomei_totp_credentials+  ADD COLUMN IF NOT EXISTS envelope_version smallint NOT NULL DEFAULT 0+  CHECK (envelope_version IN (0, 1));+

@@ -0,0 +1,46 @@+-- Bound new/changed values without rechecking unchanged legacy identity fields.+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE OR REPLACE FUNCTION shomei.enforce_field_budgets() RETURNS trigger+LANGUAGE plpgsql SET search_path = pg_catalog, pg_temp AS $$+DECLARE+  incoming jsonb := to_jsonb(NEW);+  previous jsonb := '{}'::jsonb;+  field text;+  budget integer;+BEGIN+  IF TG_OP = 'UPDATE' THEN previous := to_jsonb(OLD); END IF;+  FOREACH field IN ARRAY ARRAY['login_id', 'email', 'display_name', 'label', 'role'] LOOP+    IF incoming ? field AND incoming->field IS DISTINCT FROM previous->field THEN+      budget := CASE field WHEN 'login_id' THEN 256 WHEN 'email' THEN 254+                WHEN 'display_name' THEN 256 ELSE 128 END;+      IF (CASE WHEN field = 'email' THEN octet_length(incoming->>field)+               ELSE char_length(incoming->>field) END) > budget THEN+        RAISE EXCEPTION 'field size exceeds new-write budget' USING ERRCODE = '22001';+      END IF;+    END IF;+  END LOOP;+  IF incoming ? 'allowed_scopes' AND incoming->'allowed_scopes' IS DISTINCT FROM previous->'allowed_scopes' THEN+    IF EXISTS (SELECT 1 FROM jsonb_array_elements_text(incoming->'allowed_scopes') AS s(value)+               WHERE char_length(value) > 128) THEN+      RAISE EXCEPTION 'scope size exceeds new-write budget' USING ERRCODE = '22001';+    END IF;+  END IF;+  RETURN NEW;+END;+$$;++CREATE TRIGGER enforce_field_budgets BEFORE INSERT OR UPDATE ON shomei.shomei_users+  FOR EACH ROW EXECUTE FUNCTION shomei.enforce_field_budgets();+CREATE TRIGGER enforce_field_budgets BEFORE INSERT OR UPDATE ON shomei.shomei_password_credentials+  FOR EACH ROW EXECUTE FUNCTION shomei.enforce_field_budgets();+CREATE TRIGGER enforce_field_budgets BEFORE INSERT OR UPDATE ON shomei.shomei_webauthn_credentials+  FOR EACH ROW EXECUTE FUNCTION shomei.enforce_field_budgets();+CREATE TRIGGER enforce_field_budgets BEFORE INSERT OR UPDATE ON shomei.shomei_roles+  FOR EACH ROW EXECUTE FUNCTION shomei.enforce_field_budgets();+CREATE TRIGGER enforce_field_budgets BEFORE INSERT OR UPDATE ON shomei.shomei_role_grants+  FOR EACH ROW EXECUTE FUNCTION shomei.enforce_field_budgets();+CREATE TRIGGER enforce_field_budgets BEFORE INSERT OR UPDATE ON shomei.shomei_oauth_clients+  FOR EACH ROW EXECUTE FUNCTION shomei.enforce_field_budgets();+CREATE TRIGGER enforce_field_budgets BEFORE INSERT OR UPDATE ON shomei.shomei_service_accounts+  FOR EACH ROW EXECUTE FUNCTION shomei.enforce_field_budgets();

@@ -0,0 +1,5 @@+-- Measured against 10000 per-user sessions: ordered LIMIT avoids a full scan + top-N sort.+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE INDEX IF NOT EXISTS shomei_sessions_user_created_id_idx+  ON shomei.shomei_sessions(user_id, created_at DESC, session_id DESC);

@@ -0,0 +1,9 @@+-- Measured with 10000 expired rows and 10000 live rows on another principal.+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE INDEX IF NOT EXISTS shomei_sessions_active_subject_idx+  ON shomei.shomei_sessions(user_id, expires_at)+  WHERE status = 'active' AND kind IN ('machine', 'delegated');+CREATE INDEX IF NOT EXISTS shomei_sessions_active_actor_idx+  ON shomei.shomei_sessions(actor_user_id, expires_at)+  WHERE status = 'active' AND kind IN ('machine', 'delegated');

@@ -0,0 +1,11 @@+-- add-admin-keyset-indexes++SET LOCAL search_path = pg_catalog, pg_temp;++-- 10k-user and 11k-event EXPLAIN evidence: docs/review-remediation/admin-query-plans.txt.+CREATE INDEX IF NOT EXISTS shomei_users_created_id_idx+  ON shomei.shomei_users (created_at DESC, user_id DESC);+CREATE INDEX IF NOT EXISTS shomei_users_status_created_id_idx+  ON shomei.shomei_users (status, created_at DESC, user_id DESC);+CREATE INDEX IF NOT EXISTS shomei_auth_events_user_created_id_idx+  ON shomei.shomei_auth_events (user_id, created_at DESC, event_id DESC);

@@ -0,0 +1,31 @@+-- enforce-session-provenance-upgrades++SET LOCAL search_path = pg_catalog, pg_temp;++-- 0029 defaulted pre-provenance rows (and omitted-kind writes) to interactive.+-- Recover non-interactive provenance from authoritative associations before enforcing it.+-- Every real interactive mint persists a refresh token atomically with the session;+-- pre-provenance machines/delegations have none. Missing evidence requires a new login.+UPDATE shomei.shomei_sessions+SET kind = 'delegated'+WHERE actor_user_id IS NOT NULL AND kind IS DISTINCT FROM 'delegated';++UPDATE shomei.shomei_sessions AS s+SET kind = 'machine'+WHERE s.actor_user_id IS NULL+  AND s.kind = 'interactive'+  AND (+    EXISTS (SELECT 1 FROM shomei.shomei_service_accounts AS a WHERE a.user_id = s.user_id)+    OR NOT EXISTS (SELECT 1 FROM shomei.shomei_refresh_tokens AS r WHERE r.session_id = s.session_id)+  );++-- Explicit NULL has no trustworthy provenance. Preserve the session's ordinary access+-- lifetime but deny interactive authorization; the owner can authenticate again.+UPDATE shomei.shomei_sessions SET kind = 'machine' WHERE kind IS NULL;++-- Old or direct SQL writers that omit provenance must never manufacture an interactive+-- session. Current interpreters always supply a kind. Stop old issuers during upgrade.+ALTER TABLE shomei.shomei_sessions ALTER COLUMN kind SET DEFAULT 'machine';+ALTER TABLE shomei.shomei_sessions ALTER COLUMN kind SET NOT NULL;+ALTER TABLE shomei.shomei_sessions ADD CONSTRAINT shomei_sessions_kind_check+  CHECK (kind IN ('interactive', 'machine', 'delegated'));

@@ -34,3 +34,10 @@ 0034-sessions-authenticated-at.sql 0035-status-checks-and-case-insensitive-identity.sql 0036-unique-password-credential-per-user.sql+0037-remembered-sessions.sql+0038-add-totp-envelope-version.sql+0039-enforce-new-field-budgets.sql+0040-add-session-keyset-index.sql+0041-add-active-session-principal-indexes.sql+0042-add-admin-keyset-indexes.sql+0043-enforce-session-provenance-upgrades.sql

@@ -1,6 +1,6 @@-cabal-version:      3.0-name:               shomei-migrations-version:            0.2.0.0+cabal-version: 3.0+name: shomei-migrations+version: 0.2.0.1 synopsis:   Schema migrations for Shōmei (pg-migrate component, embedded SQL) @@ -13,32 +13,37 @@   test-support sublibrary that provisions a fresh ephemeral PostgreSQL with   the schema already applied. -homepage:           https://github.com/shinzui/shomei-bug-reports:        https://github.com/shinzui/shomei/issues-license:            MIT-license-file:       LICENSE-author:             Nadeem Bitar-maintainer:         nadeem@gmail.com-copyright:          2026 Nadeem Bitar-category:           Database, Security-build-type:         Simple-tested-with:        GHC ==9.12.4-extra-doc-files:    CHANGELOG.md+homepage: https://github.com/shinzui/shomei+bug-reports: https://github.com/shinzui/shomei/issues+license: MIT+license-file: LICENSE+author: Nadeem Bitar+maintainer: nadeem@gmail.com+copyright: 2026 Nadeem Bitar+category: Database, Security+build-type: Simple+tested-with: ghc ==9.12.4+extra-doc-files: CHANGELOG.md extra-source-files:   migrations/shomei/*.sql   migrations/shomei/manifest  source-repository head-  type:     git+  type: git   location: https://github.com/shinzui/shomei.git  common warnings   ghc-options:-    -Wall -Wcompat -Widentities -Wincomplete-record-updates-    -Wincomplete-uni-patterns -Wpartial-fields -Wredundant-constraints+    -Wall+    -Wcompat+    -Widentities+    -Wincomplete-record-updates+    -Wincomplete-uni-patterns+    -Wpartial-fields+    -Wredundant-constraints  common shared-  default-language:   GHC2024+  default-language: GHC2024   default-extensions:     BlockArguments     DeriveAnyClass@@ -51,58 +56,70 @@     TemplateHaskell  library-  import:          warnings, shared-  hs-source-dirs:  src+  import: warnings, shared+  hs-source-dirs: src   exposed-modules: Shomei.Migrations   build-depends:-    , base              >=4.18 && <5-    , containers        >=0.7  && <0.8-    , hasql             >=1.10 && <1.11-    , pg-migrate        >=1.1  && <1.2-    , pg-migrate-embed  >=1.1  && <1.2-    , text              >=2.0  && <2.2+    base >=4.18 && <5,+    containers >=0.7 && <0.8,+    hasql >=1.10 && <1.11,+    pg-migrate >=1.2 && <1.3,+    pg-migrate-embed >=1.2 && <1.3,+    text >=2.0 && <2.2,  executable shomei-migrate-  import:         warnings, shared-  main-is:        Main.hs+  import: warnings, shared+  main-is: Main.hs   hs-source-dirs: app-  ghc-options:    -threaded -rtsopts -with-rtsopts=-N+  ghc-options:+    -threaded+    -rtsopts+    -with-rtsopts=-N+   build-depends:-    , aeson                 >=2.1      && <2.3-    , base                  >=4.18     && <5-    , bytestring            >=0.11     && <0.13-    , hasql                 >=1.10     && <1.11-    , optparse-applicative  >=0.19     && <0.20-    , pg-migrate            >=1.1      && <1.2-    , pg-migrate-cli        >=1.1      && <1.2-    , shomei-migrations     ^>=0.2.0.0-    , text                  >=2.0      && <2.2+    aeson >=2.1 && <2.3,+    base >=4.18 && <5,+    bytestring >=0.11 && <0.13,+    hasql >=1.10 && <1.11,+    optparse-applicative >=0.19 && <0.20,+    pg-migrate >=1.2 && <1.3,+    pg-migrate-cli >=1.2 && <1.3,+    shomei-migrations ^>=0.2.0.1,+    text >=2.0 && <2.2,  library test-support-  import:          warnings, shared-  visibility:      public-  hs-source-dirs:  test-support+  import: warnings, shared+  visibility: public+  hs-source-dirs: test-support   exposed-modules: Shomei.Migrations.TestSupport   build-depends:-    , base               >=4.18     && <5-    , ephemeral-pg       >=0.2.2    && <0.3-    , pg-migrate         >=1.1      && <1.2-    , shomei-migrations  ^>=0.2.0.0-    , text               >=2.0      && <2.2-    , time               >=1.12     && <1.15+    base >=4.18 && <5,+    directory >=1.3 && <1.4,+    ephemeral-pg >=0.3.1 && <0.4,+    pg-migrate >=1.2 && <1.3,+    shomei-migrations ^>=0.2.0.1,+    text >=2.0 && <2.2,+    time >=1.12 && <1.15,+    unix >=2.8 && <2.9,  test-suite shomei-migrations-test-  import:         warnings, shared-  type:           exitcode-stdio-1.0+  import: warnings, shared+  type: exitcode-stdio-1.0   hs-source-dirs: test-  main-is:        Main.hs-  ghc-options:    -threaded -rtsopts -with-rtsopts=-N+  main-is: Main.hs+  ghc-options:+    -threaded+    -rtsopts+    -with-rtsopts=-N+   build-depends:-    , base                     >=4.18     && <5-    , containers               >=0.7      && <0.8-    , hasql                    >=1.10     && <1.11-    , pg-migrate               >=1.1      && <1.2-    , pg-migrate-test-support  >=1.1      && <1.2-    , shomei-migrations        ^>=0.2.0.0-    , tasty                    >=1.4      && <1.6-    , tasty-hunit              >=0.10     && <0.11+    base >=4.18 && <5,+    bytestring >=0.11 && <0.13,+    containers >=0.7 && <0.8,+    hasql >=1.10 && <1.11,+    pg-migrate >=1.2 && <1.3,+    pg-migrate-embed >=1.2 && <1.3,+    pg-migrate-test-support >=1.2 && <1.3,+    shomei-migrations ^>=0.2.0.1,+    tasty >=1.4 && <1.6,+    tasty-hunit >=0.10 && <0.11,

@@ -7,15 +7,18 @@   ) where +import Data.Monoid qualified as EphemeralMonoid import Data.Text (Text) import EphemeralPg qualified as Pg import Shomei.Migrations (applyShomeiMigrations)+import System.Directory qualified as EphemeralDirectory+import System.Posix.User qualified as EphemeralUser  -- | Run @action@ against a fresh ephemeral PostgreSQL connection string whose database -- already has the full Shōmei schema applied. withShomeiMigratedDatabase :: (Text -> IO a) -> IO a withShomeiMigratedDatabase action = do-  result <- Pg.withCached \db -> do+  result <- withEphemeralPg \db -> do     let connStr = Pg.connectionString db     applied <- applyShomeiMigrations connStr     case applied of@@ -25,3 +28,13 @@   case result of     Left err -> error ("Failed to start ephemeral PostgreSQL: " <> show err)     Right value -> pure value++-- | Stable per-user root lets the next invocation reap abandoned clusters.+-- See mori://shinzui/ephemeral-pg/docs/guides (temporary-roots-and-stale-cleanup.md; artifact URI pending).+withEphemeralPg :: (Pg.Database -> IO a) -> IO (Either Pg.StartError a)+withEphemeralPg action = do+  uid <- EphemeralUser.getEffectiveUserID+  let root = "/tmp/ephpg-shomei-" <> show uid+  EphemeralDirectory.createDirectoryIfMissing True root+  let config = Pg.defaultConfig {Pg.temporaryRoot = EphemeralMonoid.Last (Just root)}+  Pg.withCachedConfig config Pg.defaultCacheConfig action

@@ -1,9 +1,14 @@+{-# OPTIONS_GHC -fplugin=Database.PostgreSQL.Migrate.Embed.RecompilePlugin #-}+ module Main (main) where +import Data.ByteString qualified import Data.Int (Int64) import Data.List.NonEmpty (NonEmpty (..))+import Data.List.NonEmpty qualified as NE import Data.Set qualified as Set import Database.PostgreSQL.Migrate+import Database.PostgreSQL.Migrate.Embed (embedMigrationManifest) import Database.PostgreSQL.Migrate.Test import Hasql.Connection qualified as Connection import Hasql.Decoders qualified as Decoders@@ -22,7 +27,8 @@         "Shomei migrations"         [ testCase             "composition preserves a host component's namespace state"-            testCompositionPreservesHostNamespace+            testCompositionPreservesHostNamespace,+          testCase "provenance upgrade repairs legacy machine and actor rows and defaults unknown writers safely" testProvenanceUpgrade         ]     ) @@ -136,3 +142,59 @@  expectRight :: (Show error) => Either error value -> value expectRight = either (error . show) id++-- Apply the exact embedded history through 0042, seed rows with its legacy default,+-- then run the real 0043 migration through the same runner in its own transaction.+testProvenanceUpgrade :: Assertion+testProvenanceUpgrade = do+  let history = NE.toList embeddedHistory+      (before, upgradeAndLater) = span ((/= "0043-enforce-session-provenance-upgrades.sql") . fst) history+      fixture = ("0042-upgrade-fixture.sql", legacySessionFixture)+      component = expectRight (migrationComponentFromEmbeddedSql "provenance-upgrade" Set.empty (NE.fromList (before <> [fixture] <> upgradeAndLater)))+      plan = expectRight (migrationPlan (component :| []))+  result <- withMigratedDatabase plan \connection -> do+    initial <- Connection.use connection (Session.statement () provenanceSnapshot)+    initial @?= Right (1, 3, 1, True)+    inserted <- Connection.use connection (Session.script "INSERT INTO shomei.shomei_sessions(session_id,user_id,status,created_at,expires_at) VALUES ('aaaaaaaa-0000-0000-0000-000000000006','11111111-1111-1111-1111-111111111111','active',now(),now()+interval '1 day')")+    inserted @?= Right ()+    omitted <- Connection.use connection (Session.statement () provenanceSnapshot)+    omitted @?= Right (1, 4, 1, True)+    invalid <- Connection.use connection (Session.script "UPDATE shomei.shomei_sessions SET kind='unknown' WHERE kind='interactive'")+    case invalid of+      Left _ -> pure ()+      Right _ -> assertFailure "unknown provenance was admitted"+    final <- Connection.use connection (Session.statement () provenanceSnapshot)+    final @?= Right (1, 4, 1, True)+  case result of+    Left err -> assertFailure (show err)+    Right () -> pure ()++legacySessionFixture :: Data.ByteString.ByteString+legacySessionFixture =+  """+  INSERT INTO shomei.shomei_users(user_id,login_id,status,created_at,updated_at) VALUES+    ('11111111-1111-1111-1111-111111111111','human','active',now(),now()),+    ('22222222-2222-2222-2222-222222222222','machine','active',now(),now());+  INSERT INTO shomei.shomei_service_accounts(service_account_id,client_id,user_id,secret_hash,display_name,allowed_scopes,status,created_at)+    VALUES ('33333333-3333-3333-3333-333333333333','machine-client','22222222-2222-2222-2222-222222222222','public-fixture','Machine','[]','active',now());+  INSERT INTO shomei.shomei_sessions(session_id,user_id,status,created_at,expires_at,actor_user_id,kind) VALUES+    ('aaaaaaaa-0000-0000-0000-000000000001','11111111-1111-1111-1111-111111111111','active',now(),now()+interval '1 day',NULL,'interactive'),+    ('aaaaaaaa-0000-0000-0000-000000000002','22222222-2222-2222-2222-222222222222','active',now(),now()+interval '1 day',NULL,'interactive'),+    ('aaaaaaaa-0000-0000-0000-000000000003','11111111-1111-1111-1111-111111111111','active',now(),now()+interval '1 day','22222222-2222-2222-2222-222222222222','interactive'),+    ('aaaaaaaa-0000-0000-0000-000000000004','11111111-1111-1111-1111-111111111111','active',now(),now()+interval '1 day',NULL,NULL),+    ('aaaaaaaa-0000-0000-0000-000000000005','11111111-1111-1111-1111-111111111111','active',now(),now()+interval '1 day',NULL,'interactive');+  INSERT INTO shomei.shomei_refresh_tokens(refresh_token_id,session_id,token_hash,status,created_at,expires_at)+    VALUES ('bbbbbbbb-0000-0000-0000-000000000001','aaaaaaaa-0000-0000-0000-000000000001','public-human-refresh-fixture','active',now(),now()+interval '1 day');+  """++provenanceSnapshot :: Statement () (Int64, Int64, Int64, Bool)+provenanceSnapshot =+  Statement.preparable+    "SELECT count(*) FILTER (WHERE kind='interactive'), count(*) FILTER (WHERE kind='machine'), count(*) FILTER (WHERE kind='delegated'), bool_and(CASE session_id::text WHEN 'aaaaaaaa-0000-0000-0000-000000000001' THEN kind='interactive' WHEN 'aaaaaaaa-0000-0000-0000-000000000003' THEN kind='delegated' ELSE kind='machine' END) FROM shomei.shomei_sessions"+    Encoders.noParams+    (Decoders.singleRow ((,,,) <$> required <*> required <*> required <*> Decoders.column (Decoders.nonNullable Decoders.bool)))+  where+    required = Decoders.column (Decoders.nonNullable Decoders.int8)++embeddedHistory :: NonEmpty (FilePath, Data.ByteString.ByteString)+embeddedHistory = $(embedMigrationManifest "migrations/shomei/manifest")