FireAdministration/prisma/migrations/20260902000100_rls_policies/migration.sql

-- =============================================================================
-- Row Level Security: Mandantentrennung auf Datenbankebene.
--
-- Warum auf Datenbankebene und nicht nur im Code?
-- Weil ein vergessener WHERE-Filter sonst Daten einer fremden Wehr ausliefert.
-- Mit RLS ist der schlimmste Fall ein leeres Ergebnis, nicht ein Datenleck.
--
-- Funktionsweise:
--   Die Anwendung verbindet sich als "florian_app" (NOBYPASSRLS) und setzt zu
--   Beginn jeder Transaktion
--       SET LOCAL app.current_organisation = '<uuid>';
--   Siehe src/lib/db.ts. Ohne gesetzten Wert liefert jede Abfrage null Zeilen.
--
--   Der Eigentümer der Tabellen (POSTGRES_USER) umgeht RLS. Das ist gewollt:
--   Migrationen und das Seed-Skript müssen über alle Mandanten hinweg
--   arbeiten. Deshalb steht hier bewusst kein FORCE ROW LEVEL SECURITY.
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Hilfsfunktionen
-- -----------------------------------------------------------------------------

-- Liest die aktive Organisation aus der Sitzung. Ist nichts gesetzt oder steht
-- dort kein gültiges UUID, kommt NULL zurück — und NULL vergleicht sich mit
-- nichts, die Abfrage liefert also keine Zeilen.
CREATE OR REPLACE FUNCTION aktuelle_organisation()
RETURNS uuid
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    wert text;
BEGIN
    wert := current_setting('app.current_organisation', true);
    IF wert IS NULL OR wert = '' THEN
        RETURN NULL;
    END IF;
    RETURN wert::uuid;
EXCEPTION
    WHEN invalid_text_representation THEN
        RETURN NULL;
END;
$$;

COMMENT ON FUNCTION aktuelle_organisation() IS
    'Aktive Organisation der Sitzung, gesetzt per SET LOCAL app.current_organisation.';

-- Verhindert Änderungen am Audit-Log — auch durch den Tabelleneigentümer.
CREATE OR REPLACE FUNCTION audit_log_unveraenderlich()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE EXCEPTION
        'Der Audit-Log ist unveraenderlich. % ist auf audit_log nicht zulaessig.',
        TG_OP
        USING ERRCODE = 'insufficient_privilege';
END;
$$;

-- -----------------------------------------------------------------------------
-- Mandantenpflichtige Tabellen
-- -----------------------------------------------------------------------------

-- Die Organisation selbst: hier ist die id die Mandantenspalte.
ALTER TABLE "organisation" ENABLE ROW LEVEL SECURITY;

CREATE POLICY organisation_mandant ON "organisation"
    FOR ALL
    USING ("id" = aktuelle_organisation())
    WITH CHECK ("id" = aktuelle_organisation());

-- Alle übrigen Tabellen hängen über organisationId am Mandanten.
DO $$
DECLARE
    tabelle text;
BEGIN
    FOREACH tabelle IN ARRAY ARRAY[
        'person',
        'rolle',
        'person_rolle',
        'rolle_berechtigung',
        'ausweis_token',
        'datei',
        'fahrzeug',
        'geraet'
    ]
    LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', tabelle);
        EXECUTE format(
            'CREATE POLICY %I ON %I FOR ALL '
            'USING ("organisationId" = aktuelle_organisation()) '
            'WITH CHECK ("organisationId" = aktuelle_organisation())',
            tabelle || '_mandant', tabelle
        );
    END LOOP;
END;
$$;

-- -----------------------------------------------------------------------------
-- Audit-Log: mandantengetrennt UND append-only
-- Bewusst nur SELECT und INSERT — es gibt keine Policy für UPDATE und DELETE,
-- die Anwendungsrolle kann also gar nicht ändern oder löschen.
-- -----------------------------------------------------------------------------
ALTER TABLE "audit_log" ENABLE ROW LEVEL SECURITY;

CREATE POLICY audit_log_lesen ON "audit_log"
    FOR SELECT
    USING ("organisationId" = aktuelle_organisation());

CREATE POLICY audit_log_schreiben ON "audit_log"
    FOR INSERT
    WITH CHECK ("organisationId" = aktuelle_organisation());

CREATE TRIGGER audit_log_kein_update
    BEFORE UPDATE ON "audit_log"
    FOR EACH ROW EXECUTE FUNCTION audit_log_unveraenderlich();

CREATE TRIGGER audit_log_kein_delete
    BEFORE DELETE ON "audit_log"
    FOR EACH ROW EXECUTE FUNCTION audit_log_unveraenderlich();

COMMENT ON TABLE "audit_log" IS
    'Append-only. Aenderungen und Loeschungen sind per Trigger gesperrt. '
    'Die Aufbewahrungsfrist raeumt der Worker ab, nachdem er exportiert hat.';

-- -----------------------------------------------------------------------------
-- Berechtigungskatalog: systemweit, kein Mandantenbezug.
-- Er beschreibt, was die Software kann — nicht, was eine Wehr besitzt.
-- Deshalb kein RLS, aber nur Lesezugriff für die Anwendung.
-- -----------------------------------------------------------------------------
COMMENT ON TABLE "berechtigung" IS
    'Systemweiter Katalog der Einzelrechte. Bewusst ohne organisationId: '
    'mandantenbezogen wird es erst ueber rolle_berechtigung.';

-- -----------------------------------------------------------------------------
-- Rechte für die Anwendungsrolle. Existiert die Rolle nicht (z. B. bei einer
-- Entwicklungsdatenbank ohne den Init-Container), wird der Block übersprungen.
-- -----------------------------------------------------------------------------
DO $$
BEGIN
    IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'florian_app') THEN
        EXECUTE 'GRANT USAGE ON SCHEMA public TO florian_app';
        EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO florian_app';
        EXECUTE 'GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO florian_app';
        EXECUTE 'GRANT EXECUTE ON FUNCTION aktuelle_organisation() TO florian_app';

        -- Der Katalog ist für die Anwendung nur lesbar.
        EXECUTE 'REVOKE INSERT, UPDATE, DELETE ON "berechtigung" FROM florian_app';
        -- Der Audit-Log ist für die Anwendung nur schreib- und lesbar.
        EXECUTE 'REVOKE UPDATE, DELETE ON "audit_log" FROM florian_app';
    ELSE
        RAISE NOTICE 'Rolle florian_app nicht vorhanden — Rechtevergabe uebersprungen.';
    END IF;
END;
$$;