Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0080_superuser_remap.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0080_superuser_remap.sql |
| Original SHA-256 | 3a859cbbc6141d5d9ea141e83f7022d9ce4a0db84aea3991939561577e05ccea |
| Git blob SHA | 0c27fea7bbc041c16a626df0e8e8628b08d9391d |
| Reading-copy SHA-256 | 3a859cbbc6141d5d9ea141e83f7022d9ce4a0db84aea3991939561577e05ccea |
Recovered source transcript
Download complete source text ↓108 lines · 5.6 KB
-- +goose Up
-- THE SUPERUSER REMAP, amendment 6 section 2 item 3 and Track U item 5. One audited migration.
--
-- What amendment 6 changed is a MEANING, not a value. Superuser used to be two things at once, Joseph's
-- platform controls and the client's top seat, which is the design flaw the FG Talent feedback exposed:
-- a client saw a platform tab because there was no other place for the platform to live. Operator now
-- holds the platform plane, so superuser becomes what it should always have been, the tenant top seat.
--
-- The consequence for existing holders is deliberately small and is stated so it can be checked:
--
-- TENANT HUMANS holding superuser KEEP FULL POWER INSIDE THEIR OWN TENANT. Their role value is not
-- touched, their capabilities are still resolved by rule, and under decision 12 they now hold every
-- product tool their tenant is entitled to whether or not anyone ever granted it. What they lose is the
-- platform surfaces they were never meant to see, and that loss is enforced in code by RequirePlatform,
-- which answers 404, not by a role change here.
--
-- THE PLATFORM ACCOUNT moves to operator. That is the only role value this migration writes.
--
-- Every superuser grant that exists at the moment this runs is written to audit_logs, promoted or not,
-- because the amendment says audit every existing superuser grant and a grant left in place is still a
-- finding. audit_logs sits outside RLS, is append only by trigger, and takes a NULL organization_id for a
-- platform event; the tenant rows here carry their own organization so a tenant reader can see the change
-- that touched their own people.
-- +goose StatementBegin
DO $$
DECLARE
-- The platform operator accounts, by email. A one time remap names the accounts it remaps; there is no
-- other identifier for "Joseph's account" that survives a database this migration has never seen.
-- Adding an address here later is a NEW migration, never an edit to this one.
platform_emails text[] := ARRAY['joseph.w.anady@gmail.com'];
audited bigint := 0;
promoted bigint := 0;
r record;
BEGIN
-- STEP 1, the audit of every existing superuser grant, taken BEFORE anything is written, so the trail
-- records the state that was found rather than the state that was left.
FOR r IN
SELECT id, organization_id, email, full_name, status, mfa_enrolled,
(lower(email) = ANY (SELECT lower(unnest(platform_emails)))) AS is_platform
FROM users
WHERE role = 'superuser'
ORDER BY email
LOOP
INSERT INTO audit_logs (organization_id, actor_user_id, action, entity_type, entity_id, old_values, new_values)
VALUES (
r.organization_id,
NULL, -- a migration has no human actor; the named human is Joseph, recorded in BUILD_STATE
'authz.superuser.remap_audited',
'user',
r.id::text,
jsonb_build_object('role', 'superuser', 'status', r.status, 'mfa_enrolled', r.mfa_enrolled),
jsonb_build_object(
'role', CASE WHEN r.is_platform THEN 'operator' ELSE 'superuser' END,
'plane', CASE WHEN r.is_platform THEN 'platform' ELSE 'tenant' END,
'email', r.email,
'full_name', r.full_name,
'note', CASE WHEN r.is_platform
THEN 'moved to the platform plane by amendment 6 section 2 item 3'
ELSE 'kept full power within its own tenant; platform surfaces now answer 404'
END));
audited := audited + 1;
END LOOP;
-- STEP 2, the only role write. Guarded on the CURRENT role being superuser, so this can never promote an
-- account somebody has since demoted, and so a rerun is a no op rather than a second promotion.
UPDATE users
SET role = 'operator', updated_at = now()
WHERE role = 'superuser'
AND lower(email) = ANY (SELECT lower(unnest(platform_emails)));
GET DIAGNOSTICS promoted = ROW_COUNT;
-- STEP 3, revoke the promoted account's live sessions, through the EXISTING revoke function rather than
-- a hand written UPDATE, so this obeys the same semantics every other revocation does. A session carries
-- the role it was minted under, and an operator session must satisfy MFA; leaving a pre promotion session
-- alive would be a platform seat reachable with a password alone, which is the one thing decision 1
-- forbids. The next login issues a pre MFA session whose only permitted act is enrolling an authenticator.
PERFORM auth_revoke_user_sessions(id, NULL) FROM users WHERE role = 'operator';
RAISE NOTICE 'superuser remap: % grants audited, % promoted to operator', audited, promoted;
END $$;
-- +goose StatementEnd
-- +goose Down
-- Return the platform accounts to superuser. The audit rows STAY: they record what was true when the
-- migration ran, and audit_logs refuses UPDATE and DELETE by trigger anyway, so attempting to remove them
-- would fail the rollback rather than clean it up. That is the correct behaviour for an append only trail.
--
-- Sessions are revoked again on the way back for the same reason as on the way out: a session minted under
-- one role must not survive a change to that role.
-- +goose StatementBegin
DO $$
DECLARE demoted bigint := 0; ids uuid[];
BEGIN
SELECT array_agg(id) INTO ids FROM users WHERE role = 'operator';
UPDATE users SET role = 'superuser', updated_at = now() WHERE role = 'operator';
GET DIAGNOSTICS demoted = ROW_COUNT;
IF ids IS NOT NULL THEN
PERFORM auth_revoke_user_sessions(unnest, NULL) FROM unnest(ids);
END IF;
INSERT INTO audit_logs (organization_id, actor_user_id, action, entity_type, entity_id, new_values)
VALUES (NULL, NULL, 'authz.superuser.remap_reverted', 'migration', '0080',
jsonb_build_object('demoted', demoted));
END $$;
-- +goose StatementEnd