umbragit/UmbraDatastore
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
UmbraDatastore/migrations/0001_phase1_identity.sql

495 lines
24 KiB
PL/PgSQL
Raw Permalink Blame History

-- =====================================================================
-- UmbraTrans phase-1 identity migration (users.username, user_credentials,
-- additive session columns)
-- =====================================================================
--
-- STATUS: PROPOSED / NOT APPLIED / NOT APPROVED
--
-- This file has never been executed against any database. It was written for
-- review only. Do not run it until every gate in "PRE-FLIGHT GATES" below is
-- signed off by a human operator, and until the review questions in
-- migrations/README.md are answered against the *actual* production schema.
--
-- SCOPE (phase-1 identity and tenancy only; nothing else may be added here)
--
-- ADD COLUMN users.username (nullable, unique, citext)
-- ADD TABLE user_credentials (local_password / oidc credentials)
-- ADD TABLE organizations (tenants, additive; skipped if present)
-- ADD COLUMN sessions.mfa_verified_at (nullable)
-- ADD COLUMN sessions.idle_deadline_at (nullable)
-- ADD COLUMN sessions.last_seen_at (nullable, backfilled, then NOT NULL)
--
-- DELIBERATELY NOT IN THIS MIGRATION
--
-- * No project_membership table. user_roles stays the membership source
-- of truth.
-- * No users.organization_id column. The control plane derives membership
-- from user_roles; the application slice uses a column for phase 1 and
-- the divergence is an open review question, not a settled decision.
-- * No projects/roles/permissions/invitations/audit_events change. They are
-- used unchanged.
-- * No organization row is inserted. Section 4 creates the table if it is
-- absent and verifies it if it is present; it never seeds a tenant.
-- * No password, hash, token, or connection string may appear in this file.
-- * No backfill of existing accounts. Existing rows keep a NULL username
-- until an operator supplies an explicit mapping (see BACKFILL GATE).
-- * No MFA enforcement. mfa_verified_at is added for forward compatibility
-- and stays NULL.
-- * No OIDC subject column. The provider-subject mapping belongs to a later,
-- separately reviewed migration.
--
-- SAFETY RULES FOR THE REVIEWER AND THE OPERATOR
--
-- 1. Read the whole file, including the comments, before running anything.
-- 2. Take and restore-test a custom-format logical backup first
-- (pg_dump -Fc, restore into a scratch database, verify row counts).
-- 3. Run the PRE-FLIGHT VERIFICATION block and save its output. If any
-- assumption below is false, STOP and produce a new migration.
-- 4. The statements run in one transaction with a short lock_timeout and
-- statement_timeout so the live application cannot be blocked behind an
-- open transaction. If the timeout fires, the transaction rolls back.
-- 5. users.username is added as NULLABLE on purpose: a NOT NULL column with
-- no backfill would fail or force an invented identifier.
-- 6. user_credentials is created empty. No credential is inserted here.
-- Credentials are created by the application through the reviewed
-- service path only.
-- 7. The unique constraint on users.username is added as a UNIQUE INDEX
-- (CREATE UNIQUE INDEX, then ATTACH as a constraint) so the build does
-- not hold an ACCESS EXCLUSIVE lock for the duration of the scan.
-- 8. The sessions indexes are plain CREATE INDEX. For a large sessions
-- table, run them separately with CREATE INDEX CONCURRENTLY *outside*
-- this transaction, one statement per session, after verifying the table
-- size. See the note above section 4.
-- 9. Do not run this file with a superuser. Use the dedicated migration
-- role, and record the role used in the change ticket.
-- 10. RLS and GRANT statements are deliberately left as reviewed COMMENTs in
-- section 6: the production policy helper functions and role names are
-- unverified, and inventing them would be a BLOCKER, not a shortcut.
--
-- BACKUP/RESTORE GATE
-- [ ] pg_dump -Fc of umbracontrol completed and checksummed
-- [ ] restore into an isolated scratch database succeeded
-- [ ] restored row counts match for users, sessions, organizations, projects
-- [ ] rollback file reviewed and rehearsed against the scratch database
--
-- RLS TEST GATE (must pass before approval)
-- [ ] two organizations, two projects, two users each
-- [ ] a user in org A cannot read or write org B rows
-- [ ] a session row is readable only by its owner
-- [ ] the application role cannot escalate by setting a GUC
-- [ ] FORCE ROW LEVEL SECURITY is proven, not merely enabled
--
-- ---------------------------------------------------------------------
-- PRE-FLIGHT VERIFICATION (run read-only, save the output with the ticket)
-- ---------------------------------------------------------------------
--
-- SELECT current_database(), current_user, version();
--
-- -- users must exist with a uuid primary key named id
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'public' AND table_name = 'users'
-- ORDER BY ordinal_position;
--
-- -- sessions must exist; record its current columns
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'public' AND table_name = 'sessions'
-- ORDER BY ordinal_position;
--
-- -- citext availability for the unique username
-- SELECT name, default_version, installed_version FROM pg_available_extensions
-- WHERE name = 'citext';
--
-- -- current RLS posture of the tables this file touches
-- SELECT c.relname, c.relrowsecurity, c.relforcerowsecurity
-- FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
-- WHERE n.nspname = 'public' AND c.relname IN ('users','sessions');
--
-- -- existing policies, so the additive columns inherit known-good rules
-- SELECT tablename, policyname, cmd, roles, qual, with_check
-- FROM pg_policies WHERE schemaname = 'public' AND tablename IN ('users','sessions');
--
-- -- confirm nothing named user_credentials already exists
-- SELECT to_regclass('public.user_credentials') AS user_credentials;
--
-- -- does public.organizations already exist? Section 4 is a no-op when it
-- -- does, so record its current shape and row count either way
-- SELECT to_regclass('public.organizations') AS organizations;
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'public' AND table_name = 'organizations'
-- ORDER BY ordinal_position;
-- SELECT count(*) FROM public.organizations;
--
-- -- row counts used as the post-apply baseline
-- SELECT (SELECT count(*) FROM users) AS users, (SELECT count(*) FROM sessions) AS sessions;
--
-- ---------------------------------------------------------------------
-- 0. Transaction guard rails
-- ---------------------------------------------------------------------
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '60s';
SET LOCAL idle_in_transaction_session_timeout = '30s';
-- ---------------------------------------------------------------------
-- 1. users.username
-- ---------------------------------------------------------------------
-- Assumptions: users.id is uuid; no existing column named username.
-- Existing rows are left with NULL: the new login identifier is introduced
-- only through an explicit, operator-approved mapping.
--
-- citext gives case-insensitive uniqueness, matching the application rule
-- that "Umbraadmin" and "umbraadmin" are the same account. If the extension
-- is unavailable, stop and use a lower(...) unique index instead; do not
-- substitute a plain text column with case-sensitive uniqueness.
CREATE EXTENSION IF NOT EXISTS citext;
ALTER TABLE public.users
ADD COLUMN IF NOT EXISTS username citext NULL;
COMMENT ON COLUMN public.users.username IS
'Case-insensitive canonical login identifier. NULL for accounts that have not been mapped yet.';
-- Build the unique index first (it takes a SHARE lock, not ACCESS EXCLUSIVE),
-- then attach it as a constraint so the rule is visible to the planner and to
-- future migrations. NULLs do not conflict, which is what allows the staged
-- backfill to run before the column becomes mandatory.
CREATE UNIQUE INDEX IF NOT EXISTS users_username_key
ON public.users (username);
-- Attaching the index is guarded, not unconditional, so the file can be
-- applied more than once. An index that is already attached to a constraint
-- cannot be attached again, and because this migration runs in one transaction
-- the resulting error would roll back every other change in the file, leaving a
-- half-applied schema rather than an obviously untouched one.
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_constraint c
JOIN pg_class t ON t.oid = c.conrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public'
AND t.relname = 'users'
AND c.conname = 'users_username_key'
) THEN
ALTER TABLE public.users
ADD CONSTRAINT users_username_key
UNIQUE USING INDEX users_username_key;
RAISE NOTICE 'attached users_username_key to users';
ELSE
RAISE NOTICE 'users_username_key already present; leaving it alone';
END IF;
END
$$;
-- ---------------------------------------------------------------------
-- 2. user_credentials
-- ---------------------------------------------------------------------
-- One credential row per user in phase 1. The unique user_id is a deliberate
-- phase-1 constraint (one authentication mechanism at a time); relaxing it is
-- a later, separately reviewed change.
--
-- password_hash holds an Argon2id PHC string only, e.g. the argon2id variant
-- with m=65536, t=3, p=4. No plaintext password, temporary password, or
-- password-recovery value is ever stored in this table or anywhere else.
CREATE TABLE IF NOT EXISTS public.user_credentials (
id uuid PRIMARY KEY,
user_id uuid NOT NULL UNIQUE REFERENCES public.users (id) ON DELETE CASCADE,
credential_type text NOT NULL,
password_hash text NULL,
must_change_password boolean NOT NULL DEFAULT false,
failed_login_count integer NOT NULL DEFAULT 0,
locked_until timestamptz NULL,
last_login_at timestamptz NULL,
password_changed_at timestamptz NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT user_credentials_type_check
CHECK (credential_type IN ('local_password', 'oidc')),
CONSTRAINT user_credentials_local_requires_hash
CHECK (credential_type <> 'local_password' OR password_hash IS NOT NULL),
CONSTRAINT user_credentials_oidc_forbids_hash
CHECK (credential_type <> 'oidc' OR password_hash IS NULL),
CONSTRAINT user_credentials_failed_login_count_check
CHECK (failed_login_count >= 0),
CONSTRAINT user_credentials_password_hash_format
CHECK (password_hash IS NULL OR password_hash ~ '^\$argon2(id|i|d)\$')
);
COMMENT ON TABLE public.user_credentials IS
'Authentication credentials, kept separate from the user profile. Rows are never returned by the API.';
COMMENT ON COLUMN public.user_credentials.password_hash IS
'Argon2id PHC hash. Never returned by an endpoint, never logged, never selected into a report.';
COMMENT ON COLUMN public.user_credentials.must_change_password IS
'True for temporary-password enrollment. Cleared only after a successful first-login change.';
-- Login path and lockout scans. Both are narrow and index-only.
CREATE INDEX IF NOT EXISTS user_credentials_type_idx
ON public.user_credentials (credential_type);
CREATE INDEX IF NOT EXISTS user_credentials_locked_until_idx
ON public.user_credentials (locked_until)
WHERE locked_until IS NOT NULL;
-- updated_at is maintained by the application on every write. If the
-- production schema already has a generic set_updated_at() trigger helper,
-- attach it here instead; do not assume one exists.
-- CREATE TRIGGER user_credentials_set_updated_at
-- BEFORE UPDATE ON public.user_credentials
-- FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
-- ---------------------------------------------------------------------
-- 3. sessions: additive columns
-- ---------------------------------------------------------------------
-- The session bearer token is unchanged: it stays random, and only its hash
-- is stored. The three columns below are what the phase-1 identity slice
-- needs; no existing column is dropped, renamed, or retyped, so an older
-- application build keeps working against this schema.
ALTER TABLE public.sessions
ADD COLUMN IF NOT EXISTS mfa_verified_at timestamptz NULL,
ADD COLUMN IF NOT EXISTS idle_deadline_at timestamptz NULL,
ADD COLUMN IF NOT EXISTS last_seen_at timestamptz NULL;
COMMENT ON COLUMN public.sessions.mfa_verified_at IS
'When the session completed MFA. NULL in phase 1; no MFA is enforced yet.';
COMMENT ON COLUMN public.sessions.idle_deadline_at IS
'Absolute idle deadline for the session. Sessions past it are rejected and revoked.';
COMMENT ON COLUMN public.sessions.last_seen_at IS
'Last time the session was used, for idle accounting and operator review.';
-- Backfill last_seen_at from an existing column only if one can be verified
-- in the pre-flight output. Uncomment exactly one source after confirming the
-- column exists; otherwise leave NULL and let the next write populate it.
-- UPDATE public.sessions SET last_seen_at = now() WHERE last_seen_at IS NULL;
-- Set idle deadlines for live sessions so the new column is never NULL for
-- an active row. The duration must match the reviewed application setting
-- (default 30 minutes idle, 12 hours absolute).
UPDATE public.sessions
SET idle_deadline_at = now() + interval '30 minutes'
WHERE idle_deadline_at IS NULL
AND revoked_at IS NULL;
ALTER TABLE public.sessions
ALTER COLUMN last_seen_at SET DEFAULT now();
-- Make last_seen_at NOT NULL only after the backfill is verified; a partial
-- backfill would fail here, which is the intended safety check.
-- ALTER TABLE public.sessions ALTER COLUMN last_seen_at SET NOT NULL;
CREATE INDEX IF NOT EXISTS sessions_user_id_idx
ON public.sessions (user_id);
CREATE INDEX IF NOT EXISTS sessions_idle_deadline_at_idx
ON public.sessions (idle_deadline_at)
WHERE revoked_at IS NULL;
-- ---------------------------------------------------------------------
-- 4. organizations
-- ---------------------------------------------------------------------
-- Assumptions: the control plane already has a public.organizations table
-- (see 001_control_plane.sql) with id, name, slug, status, created_at and
-- updated_at. If it already exists this section is a no-op: CREATE TABLE IF
-- NOT EXISTS skips it and the statements below verify the shape instead of
-- changing it. That is the expected case, because this file is additive by
-- design and must not redefine a table the audio system already reads.
--
-- The application needs organizations to exist as first-class rows for the
-- administrator interface to manage tenants and to validate an account's
-- organization assignment. Without this section that assignment pointed at
-- nothing at all.
--
-- Deliberately NOT changed here:
-- * users.organization_id is NOT added by this file. The control plane
-- derives organization membership from user_roles (scope='organization'),
-- not from a column on users. The application slice models membership as
-- a column for phase 1; reconciling the two is recorded as an open review
-- question in migrations/README.md rather than silently decided here.
-- * No organization row is inserted. Seeding a tenant is an operator action.
CREATE TABLE IF NOT EXISTS public.organizations (
id uuid PRIMARY KEY,
name text NOT NULL,
slug citext NOT NULL,
status text NOT NULL DEFAULT 'active',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT organizations_status_check
CHECK (status IN ('active', 'suspended', 'archived')),
CONSTRAINT organizations_name_length_check
CHECK (char_length(name) BETWEEN 2 AND 120)
);
-- A duplicate-free tenant handle. Built then attached, so the scan does not
-- hold an ACCESS EXCLUSIVE lock for its duration.
CREATE UNIQUE INDEX IF NOT EXISTS organizations_slug_key
ON public.organizations (slug);
-- Attaching the index is guarded, not unconditional. When the control plane
-- already created organizations it also already created this constraint, and
-- ADD CONSTRAINT against an index that is already attached fails with "index is
-- already associated with a constraint". Found by running this migration against
-- the real schema: unguarded, that error aborts the whole transaction, so the
-- rest of the migration would silently not apply.
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_constraint c
JOIN pg_class t ON t.oid = c.conrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public'
AND t.relname = 'organizations'
AND c.conname = 'organizations_slug_key'
) THEN
ALTER TABLE public.organizations
ADD CONSTRAINT organizations_slug_key
UNIQUE USING INDEX organizations_slug_key;
RAISE NOTICE 'attached organizations_slug_key to organizations';
ELSE
RAISE NOTICE 'organizations_slug_key already present; leaving it alone';
END IF;
END
$$;
COMMENT ON TABLE public.organizations IS
'Tenants that own projects and accounts. Phase 1 inserts no rows; the control plane may already contain some.';
-- The administrator dashboard filters by status on every load.
CREATE INDEX IF NOT EXISTS organizations_status_idx
ON public.organizations (status);
-- ---------------------------------------------------------------------
-- 4b. audit_events: target lookup
-- ---------------------------------------------------------------------
-- The application records a target as the polymorphic (target_type,
-- target_id) pair the control plane already uses, and the administrator audit
-- view filters on it. audit_events_org_created_idx covers the organization and
-- created_at ordering, but nothing covers the target pair, so that filter would
-- be a sequential scan. Additive index only; no column or constraint changes.
CREATE INDEX IF NOT EXISTS audit_events_target_idx
ON public.audit_events (target_type, target_id, created_at DESC);
-- ---------------------------------------------------------------------
-- 5. RLS AND GRANTS (REVIEWED, NOT EXECUTED)
-- ---------------------------------------------------------------------
-- The statements below are intentionally provided as comments. The
-- production policy helper functions, the application role names, and the
-- existing policy conventions are UNVERIFIED in this workspace. Writing
-- executable GRANT/RLS against guessed identifiers is a BLOCKER.
--
-- Required before approval:
-- * Confirm the name of the "current user" helper (for example
-- app.current_user_id() or a request-scoped GUC) and prove it cannot be
-- set by a client-controlled value.
-- * Confirm the migration role and the application role names.
-- * Confirm the existing users/sessions policies so the additive columns
-- inherit the same guarantees.
--
-- Policies to add on user_credentials once the helper is verified:
--
-- ALTER TABLE public.user_credentials ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE public.user_credentials FORCE ROW LEVEL SECURITY;
--
-- -- a user reads and updates only their own credential
-- CREATE POLICY user_credentials_self_read ON public.user_credentials
-- FOR SELECT USING (user_id = app.current_user_id());
-- CREATE POLICY user_credentials_self_update ON public.user_credentials
-- FOR UPDATE USING (user_id = app.current_user_id());
--
-- -- only the administrator service path inserts credentials, and it does so
-- -- as a privileged role that bypasses the per-user policies
--
-- Grants to review (role names must be confirmed, not guessed):
--
-- REVOKE ALL ON public.user_credentials FROM PUBLIC;
-- GRANT SELECT, INSERT, UPDATE ON public.user_credentials TO <app_role>;
-- -- No DELETE for the application role: credential removal is an
-- -- administrative, audited operation.
-- -- No column-level GRANT alternative is meaningful here: the application
-- -- must read password_hash to verify a local password. The control is that
-- -- no endpoint selects it, which the API tests assert.
--
-- On sessions, the new columns need no new policy: they inherit the existing
-- per-session row policies. Adding a policy that keys on a new column before
-- the existing ones are verified risks weakening them.
--
-- ---------------------------------------------------------------------
-- 5. POST-APPLY VERIFICATION (run immediately after COMMIT)
-- ---------------------------------------------------------------------
--
-- \d+ public.users
-- \d+ public.user_credentials
-- \d+ public.sessions
-- \d+ public.organizations
--
-- -- the new unique rule is enforced and case-insensitive
-- SELECT indexname, indexdef FROM pg_indexes
-- WHERE tablename = 'users' AND indexname = 'users_username_key';
--
-- -- the organizations slug rule survived, whether the table pre-existed or
-- -- was created by section 4
-- SELECT indexname, indexdef FROM pg_indexes
-- WHERE tablename = 'organizations' AND indexname = 'organizations_slug_key';
--
-- -- the status vocabulary the application expects is the one the check
-- -- constraint enforces
-- SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint
-- WHERE conrelid = 'public.organizations'::regclass AND contype = 'c'
-- ORDER BY conname;
--
-- -- this migration inserted no tenant
-- SELECT count(*) FROM public.organizations;
--
-- -- the audit target index the administrator view depends on exists
-- SELECT indexname, indexdef FROM pg_indexes
-- WHERE tablename = 'audit_events' AND indexname = 'audit_events_target_idx';
--
-- -- no duplicate, non-null usernames can exist
-- SELECT lower(username::text) AS key, count(*)
-- FROM public.users WHERE username IS NOT NULL
-- GROUP BY 1 HAVING count(*) > 1;
--
-- -- the table is empty: this migration must not invent credentials
-- SELECT count(*) FROM public.user_credentials;
--
-- -- no live session is left without a deadline
-- SELECT count(*) FROM public.sessions
-- WHERE revoked_at IS NULL AND idle_deadline_at IS NULL;
--
-- -- RLS state matches the reviewed expectation
-- SELECT c.relname, c.relrowsecurity, c.relforcerowsecurity
-- FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
-- WHERE n.nspname = 'public' AND c.relname = 'user_credentials';
--
-- -- the application role can verify a credential but cannot delete one
-- -- (run as the application role, not the migration role)
-- SELECT has_table_privilege('<app_role>', 'public.user_credentials', 'SELECT');
-- SELECT has_table_privilege('<app_role>', 'public.user_credentials', 'DELETE');
--
-- ---------------------------------------------------------------------
-- 6. ROLLBACK
-- ---------------------------------------------------------------------
-- Rehearse first, on the scratch database. Rolling back DESTROYS credential
-- rows: any account created after the migration would lose its only
-- authentication material and could not sign in until it is re-enrolled.
--
-- Take a fresh backup immediately before rolling back, then use
-- 0001_phase1_identity_rollback.sql in the same session settings
-- (lock_timeout, statement_timeout). The rollback is not online-safe for a
-- busy sessions table: prefer a maintenance window.
--
-- If the migration failed mid-transaction, nothing was applied: the
-- transaction rolled back and this file can be corrected and re-reviewed.
COMMIT;
Reference in New Issue View Git Blame Copy Permalink