Mandantenfähigkeit mit PostgreSQL

In einer Applikation mit mehreren Mandanten, muss sichergestellt sein, dass die Daten eines Mandanten nicht für andere angezeigt werden bzw. durch Handlungen anderer verändert werden können. In diesem Artikel wird eine Lösung für PostgreSQL Datenbanken erläutert.

Wer schon einmal eine Mandantenfähigkeit (oder auch: „Multi-Tenancy“) in einem Projekt entwickeln musste, wird wissen, dass es dafür viele verschiedene Lösungswege gibt.

Meistens wird von den folgenden drei Vorgehensweisen gesprochen:

1. Eine Datenbank pro Mandanten
Für jeden Mandanten gibt es eine eigene Datenbank. Die Applikation muss bei einer Anfrage die Kommunikation mit der richtigen Datenbank sicherstellen.

2. Eine geteilte Datenbank mit einem Schema pro Mandanten
Es gibt nur eine Datenbank für alle Mandanten, jedoch hat jeder Mandant sein eigenes Datenbank Schema. Jeder Mandant hat außerdem seinen eigenen Datenbank-Benutzer, mit dem Datenbank-Abfragen getätigt werden. Die Applikation muss sich bei einer Anfrage darum kümmern, eine Datenbankverbindung mit dem richtigen Benutzer herzustellen bzw. zu benutzen.

3.Eine geteilte Datenbank mit einem geteilten Schema für alle Mandanten
Es gibt nur eine Datenbank mit nur einem Schema auf dem sich die Daten aller Mandanten befinden. Um die Daten zu den Mandanten zuordnen zu können, werden die Datensätze mit einem Identifier erweitert.

Die dritte Variante hat zwei große Nachteile. Die Daten sind nicht voneinander isoliert und Datenbank-Abfragen müssen vom Backend-Entwickler immer mit dem Mandanten-Identifier erweitert werden, so dass bei einer Abfrage auch nur die Daten des gewünschten Mandanten ausgelesen werden.

Row-Level-Security

Seit Version 9.5 bietet PostgreSQL die Funktion Row-Level-Security (kurz: RLS) an. Damit lassen sich die beiden genannten Nachteile umgehen. Mit RLS lassen sich sogenannte Policies auf Tabellen erstellen. Mit diesen Policies lässt sich der Zugriff auf der Tabelle, sowohl beim Lesen als auch beim Schreiben, einschränken.

Hier ein Beispiel:
In einer Mitarbeiter-Verwaltung beinhaltet die Tabelle „employees“ die Arbeitnehmer von jedem Mandanten.

id:integername: varchartenant_id: integer
1Hans1
2Barbara1
3Silvia2
4Wolfgang2

Auf dieser Tabelle wird nun folgende Policy erstellt:
CREATE POLICY tenant_policy ON employees
USING (tenant_id = 1)
WITH CHECK (tenant_id = 1)

Diese bewirkt, dass nur noch Angestellte mit der Mandanten-ID 1 ausgelesen und eingefügt werden können. Eine solche Policy würde natürlich in der Praxis keine Anwendung finden. Hier geht es lediglich darum, die Funktionalität der Policies zu verdeutlichen.
Der „USING“ teil bestimmt, welche Datensätze beim Auslesen und Löschen berücksichtigt werden und der „WITH CHECK“ Teil, welche Bedingung erfüllt sein muss, um einen neuen Datensatz zu erstellen oder zu verändern.
Mit dieser Policy würde folgende Abfrage:
SELECT * FROM employees
dieses Ergebnis zeigen:

idnametenant_id
1Hans1
2Barbara1

Row-Level-Security muss allerdings erst für jede Tabelle einzeln aktiviert werden. Folgender Befehl aktiviert RLS auf einer Tabelle:

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

Datenbank-Benutzer

Damit dieses Verfahren funktioniert muss es mindestens zwei Datenbank-Benutzer geben. Einen Admin-Benutzer und einen Applikation-Benutzer. Der Admin-Benutzer muss das gesamte Datenbank-Schema, alle Policies und Funktionen erstellen. Die Applikation verbindet sich immer mit dem Applikation-Benutzer.

Mandanten-ID einer Datenbank-Verbindung zuweisen

Wie bereits erwähnt, ist die Beispiel-Policy nicht praktisch, da diese nur für den Mandanten mit der ID 1 funktioniert. Die Policy muss für jeden Mandanten funktionieren.
Zu allererst muss es dafür eine Möglichkeit geben, die Mandanten-ID für die aktuelle Datenbank-Verbindung bestimmen zu können. Dazu wird eine Tabelle benötigt in der die Mandanten-ID zu der Session-ID gespeichert werden kann:

pid_tenant_id
pid: integertenant_id: integer

Der Applikation-Benutzer darf für diese Tabelle weder Lese- noch Schreibrechte haben.

Um die Mandanten-ID für die aktuelle Session einzutragen, wird folgende Funktion vom Admin-Benutzer erstellt:

CREATE FUNCTION set_tenant_id(p_tenant_id INTEGER, pwd TEXT) RETURNS text AS $$
DECLARE
v_pwd TEXT;
BEGIN
SELECT set_tenant_id_password INTO v_pwd FROM secrets WHERE set_tenant_id_password = crypt(pwd, set_tenant_id_password);

IF v_pwd IS NULL THEN
RAISE EXCEPTION ‚invalid password‘;
END IF;

INSERT INTO public.pid_tenant_id VALUES (pg_backend_pid(), p_tenant_id) ON CONFLICT (pid) DO UPDATE SET tenant_id = p_tenant_id;

RETURN p_tenant_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE;

 

Die Funktion setzt die übergebene Mandanten-ID in die Tabelle „pid_tenant_id“. Außerdem ist diese Funktion mit einem Passwort geschützt. Dadurch kann sichergestellt werden, dass die Mandanten-ID durch SQL-Injection nicht geändert werden kann. Das Passwort wird hier in einer Tabelle namens „secrets“ als Hash-Wert gespeichert.
Der Aufruf dieser Funktion geschieht in einem sicheren Bereich im Backend. Idealerweise an der Stelle, an der die Datenbank-Verbindung aus einem Connection-Pool für die aktuelle Anfrage entnommen wird.
Um die Mandanten-ID der Session nach der Benutzung wieder zurückzusetzen, wird die Funktion kurz vor dem Freigeben der Datenbank-Verbindung mit „-1“ aufgerufen.

Mandanten-ID der Session in der Policy verwenden

Nun wird noch eine Funktion benötigt, um die Mandanten-ID der aktuellen Session wieder auszulesen:

CREATE FUNCTION get_tenant_id() RETURNS integer AS $$
DECLARE
v_tenant_id INT;
BEGIN
SELECT tenant_id INTO v_tenant_id FROM public.pid_tenant_id WHERE pid = pg_backend_pid();
RETURN v_tenant_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;
Die Policy kann nun mit der Funktion „get_tenant_id“ erweitert werden.
CREATE POLICY tenant_policy ON employees
USING (tenant_id = get_tenant_id())
WITH CHECK (tenant_id = get_tenant_id())

Somit kann eine Session nur noch die Datensätze mit der zugewiesenen Mandanten-ID auslesen, verändern, anlegen und löschen.

Fazit

RLS bietet eine elegante Lösung die Daten mehrerer Mandanten auf einer Datenbank sicher voneinander zu trennen, ohne die Datenbank-Abfragen im Backend erweitern zu müssen. Mit RLS lässt sich sicherstellen, dass die Daten eines Mandanten nicht per SQL-Injection oder Programmierfehler von einem anderen Mandanten ausgelesen oder verändert werden können. Wenn es also nicht möglich ist, für jeden Mandanten eine eigene Datenbank aufzusetzen, ist RLS die schlanke Alternative.

Diesen Artikel teilen

Jan Lippert
Software Development
Egal ob in seiner Band oder in der Softwareentwicklung: Jan ist immer voll dabei und gibt alles, um gut zu performen.