190 lines
7.4 KiB
SQL
190 lines
7.4 KiB
SQL
-- =============================================================================
|
|
-- Phase 10 — Mandantenfähigkeit: Konto und Mitgliedschaft
|
|
--
|
|
-- Bis hierher war die Personenakte zugleich das Anmeldekonto: Passwort-Hash
|
|
-- und OIDC-Subject hingen an `person`, und `person` gehört genau einer Wehr.
|
|
-- Ein Mensch konnte damit nicht zu zwei Wehren gehören, ohne zwei Passwörter
|
|
-- zu haben — und die Anmeldung per E-Mail war mehrdeutig, sobald dieselbe
|
|
-- Adresse in zwei Wehren stand (die Eindeutigkeit galt nur je Organisation).
|
|
--
|
|
-- Ab jetzt:
|
|
--
|
|
-- benutzer das Konto. Global eindeutige E-Mail, ein Passwort.
|
|
-- mitgliedschaft die Zugehörigkeit zu einer Wehr, mit oder ohne Akte.
|
|
-- person bleibt die Akte je Wehr — ohne Anmeldedaten.
|
|
--
|
|
-- DIESE MIGRATION IST DATENERHALTEND. Jede Person mit Anmeldedaten bekommt
|
|
-- ein Konto und eine Mitgliedschaft, bevor die Spalten fallen. Wer schon
|
|
-- heute in zwei Wehren mit derselben E-Mail steht, bekommt EIN Konto mit ZWEI
|
|
-- Mitgliedschaften — also genau das, was vorher nicht möglich war.
|
|
--
|
|
-- HINWEIS zu den entfernten DropIndex-Bloecken: Prisma schlaegt bei jeder
|
|
-- Migration erneut vor, die von Hand angelegten GiST- und Trigram-Indizes aus
|
|
-- Phase 1 abzuraeumen. Es kennt sie nicht, weil sie nicht aus dem Schema
|
|
-- stammen. Zehnte Migration in Folge, in der sie herausgenommen werden.
|
|
-- =============================================================================
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "MitgliedschaftArt" AS ENUM ('MITGLIED', 'AUFSICHT');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "benutzer" (
|
|
"id" UUID NOT NULL,
|
|
"email" VARCHAR(200) NOT NULL,
|
|
"passwortHash" VARCHAR(120),
|
|
"passwortGeaendertAm" TIMESTAMPTZ(3),
|
|
"oidcSubject" VARCHAR(255),
|
|
"oidcGruppen" TEXT[] DEFAULT ARRAY[]::TEXT[],
|
|
"letzteAnmeldung" TIMESTAMPTZ(3),
|
|
"fehlversuche" INTEGER NOT NULL DEFAULT 0,
|
|
"gesperrtBis" TIMESTAMPTZ(3),
|
|
"aktiv" BOOLEAN NOT NULL DEFAULT true,
|
|
"istVerwalter" BOOLEAN NOT NULL DEFAULT false,
|
|
"erstelltAm" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"geaendertAm" TIMESTAMPTZ(3) NOT NULL,
|
|
"geloeschtAm" TIMESTAMPTZ(3),
|
|
|
|
CONSTRAINT "benutzer_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "mitgliedschaft" (
|
|
"id" UUID NOT NULL,
|
|
"organisationId" UUID NOT NULL,
|
|
"benutzerId" UUID NOT NULL,
|
|
"personId" UUID,
|
|
"art" "MitgliedschaftArt" NOT NULL DEFAULT 'MITGLIED',
|
|
"aktiv" BOOLEAN NOT NULL DEFAULT true,
|
|
"vergebenVonId" UUID,
|
|
"erstelltAm" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"geaendertAm" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "mitgliedschaft_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "benutzer_email_key" ON "benutzer"("email");
|
|
CREATE UNIQUE INDEX "benutzer_oidcSubject_key" ON "benutzer"("oidcSubject");
|
|
CREATE INDEX "benutzer_oidcSubject_idx" ON "benutzer"("oidcSubject");
|
|
|
|
CREATE UNIQUE INDEX "mitgliedschaft_benutzerId_organisationId_key"
|
|
ON "mitgliedschaft"("benutzerId", "organisationId");
|
|
CREATE UNIQUE INDEX "mitgliedschaft_personId_key" ON "mitgliedschaft"("personId");
|
|
CREATE INDEX "mitgliedschaft_organisationId_art_idx"
|
|
ON "mitgliedschaft"("organisationId", "art");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "mitgliedschaft" ADD CONSTRAINT "mitgliedschaft_organisationId_fkey"
|
|
FOREIGN KEY ("organisationId") REFERENCES "organisation"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "mitgliedschaft" ADD CONSTRAINT "mitgliedschaft_benutzerId_fkey"
|
|
FOREIGN KEY ("benutzerId") REFERENCES "benutzer"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "mitgliedschaft" ADD CONSTRAINT "mitgliedschaft_personId_fkey"
|
|
FOREIGN KEY ("personId") REFERENCES "person"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Die Umstellung der vorhandenen Anmeldungen
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
-- Ein Konto je E-Mail-Adresse, über alle Wehren hinweg.
|
|
--
|
|
-- Angelegt wird nur für Personen, die sich überhaupt anmelden konnten: mit
|
|
-- Passwort-Hash oder OIDC-Subject. Eine Akte ohne beides ist eine Akte und
|
|
-- kein Konto — dabei bleibt es.
|
|
--
|
|
-- `DISTINCT ON (lower(email))` entscheidet bei mehreren Akten mit derselben
|
|
-- Adresse: die älteste gewinnt und stellt die Anmeldedaten. Die anderen
|
|
-- werden gleich darunter zu weiteren Mitgliedschaften desselben Kontos.
|
|
INSERT INTO "benutzer" (
|
|
"id", "email", "passwortHash", "passwortGeaendertAm",
|
|
"oidcSubject", "oidcGruppen", "letzteAnmeldung", "fehlversuche",
|
|
"gesperrtBis", "aktiv", "istVerwalter", "erstelltAm", "geaendertAm"
|
|
)
|
|
SELECT DISTINCT ON (lower(p."email"))
|
|
gen_random_uuid(),
|
|
lower(p."email"),
|
|
p."passwortHash",
|
|
p."passwortGeaendertAm",
|
|
p."oidcSubject",
|
|
p."oidcGruppen",
|
|
p."letzteAnmeldung",
|
|
p."fehlversuche",
|
|
p."gesperrtBis",
|
|
p."aktiv",
|
|
-- Wer bislang SUPERADMIN war, verwaltet ab jetzt die Installation. Das
|
|
-- ist der einzige Weg, auf dem dieses Merkmal je gesetzt wird, ohne dass
|
|
-- ein Mensch es vergibt.
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM "person_rolle" pr
|
|
JOIN "rolle" r ON r."id" = pr."rolleId"
|
|
WHERE pr."personId" = p."id"
|
|
AND r."schluessel" = 'SUPERADMIN'
|
|
),
|
|
p."erstelltAm",
|
|
now()
|
|
FROM "person" p
|
|
WHERE p."email" IS NOT NULL
|
|
AND p."geloeschtAm" IS NULL
|
|
AND (p."passwortHash" IS NOT NULL OR p."oidcSubject" IS NOT NULL)
|
|
ORDER BY lower(p."email"), p."erstelltAm" ASC;
|
|
|
|
-- Je Akte eine Mitgliedschaft am passenden Konto.
|
|
--
|
|
-- Auch für die Akten, die beim Anlegen der Konten übergangen wurden: genau
|
|
-- sie sind die Doppelmitgliedschaften, um die es in dieser Phase geht.
|
|
INSERT INTO "mitgliedschaft" (
|
|
"id", "organisationId", "benutzerId", "personId", "art", "aktiv",
|
|
"erstelltAm", "geaendertAm"
|
|
)
|
|
SELECT
|
|
gen_random_uuid(),
|
|
p."organisationId",
|
|
b."id",
|
|
p."id",
|
|
'MITGLIED',
|
|
p."aktiv",
|
|
p."erstelltAm",
|
|
now()
|
|
FROM "person" p
|
|
JOIN "benutzer" b ON b."email" = lower(p."email")
|
|
WHERE p."email" IS NOT NULL
|
|
AND p."geloeschtAm" IS NULL
|
|
AND (p."passwortHash" IS NOT NULL OR p."oidcSubject" IS NOT NULL);
|
|
|
|
-- Ein zweites Konto in derselben Wehr kann es nach der Umstellung nicht
|
|
-- geben — der eindeutige Index oben hätte es verhindert. Diese Prüfung ist
|
|
-- die Gegenprobe: schlägt sie an, ist etwas an den Annahmen falsch, und die
|
|
-- Migration bricht ab, statt einen halben Zustand zu hinterlassen.
|
|
DO $$
|
|
DECLARE
|
|
ohne_konto INTEGER;
|
|
BEGIN
|
|
SELECT count(*) INTO ohne_konto
|
|
FROM "person" p
|
|
WHERE p."geloeschtAm" IS NULL
|
|
AND (p."passwortHash" IS NOT NULL OR p."oidcSubject" IS NOT NULL)
|
|
AND NOT EXISTS (SELECT 1 FROM "mitgliedschaft" m WHERE m."personId" = p."id");
|
|
|
|
IF ohne_konto > 0 THEN
|
|
RAISE EXCEPTION
|
|
'Umstellung unvollstaendig: % Personen mit Anmeldedaten haben kein Konto '
|
|
'bekommen. Vermutlich fehlt ihnen die E-Mail-Adresse.', ohne_konto;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Erst jetzt fallen die alten Spalten
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
DROP INDEX IF EXISTS "person_organisationId_oidcSubject_key";
|
|
DROP INDEX IF EXISTS "person_oidcSubject_idx";
|
|
|
|
ALTER TABLE "person"
|
|
DROP COLUMN "passwortHash",
|
|
DROP COLUMN "passwortGeaendertAm",
|
|
DROP COLUMN "oidcSubject",
|
|
DROP COLUMN "oidcGruppen",
|
|
DROP COLUMN "letzteAnmeldung",
|
|
DROP COLUMN "fehlversuche",
|
|
DROP COLUMN "gesperrtBis";
|