Trigger für jede SQL-Abfrage möglich?

Probleme und Fragen zum Thema Trigger und Funktionen.
Antworten
rhaupt
Neuer Benutzer
Beiträge: 21
Registriert: Di 26. Mär 2013, 18:25

Trigger für jede SQL-Abfrage möglich?

Beitrag von rhaupt »

Hallo in die Runde,
für die Modellierung einer mulit-tenant application (also Mandantenfähigkeit) haben wir die Vorstellung, für jeden Datensatz eine Tenant-ID abzulegen. Nun soll sichergestellt werden, dass jede Abfrage auf die Datenbank eine Bestandteil "WHERE ID_Tenant=12345" hat. Abfragen/Zugriffe ohne eine "WHERE ID_Tenant=.." sollen generell verboten sein.

Frage 1: Kann man einen Trigger etablieren, der auf JEDE Datenbank-Anfrage reagiert?

Frage 2: Hat jemand bessere Vorschläge für die Durchsetzung der Datensicherheit auf Datenbankebene für eine Shared Database / Shared Schema Anwendung? Dürfte ja eine typische Fragestellung für Online-Portale sein .. ?

Besten Dank im Voraus!
Rainer

akretschmer
Erfahrener Benutzer
Beiträge: 7733
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Trigger für jede SQL-Abfrage möglich?

Beitrag von akretschmer »

rhaupt hat geschrieben:Hallo in die Runde,
für die Modellierung einer mulit-tenant application (also Mandantenfähigkeit) haben wir die Vorstellung, für jeden Datensatz eine Tenant-ID abzulegen. Nun soll sichergestellt werden, dass jede Abfrage auf die Datenbank eine Bestandteil "WHERE ID_Tenant=12345" hat. Abfragen/Zugriffe ohne eine "WHERE ID_Tenant=.." sollen generell verboten sein.

Frage 1: Kann man einen Trigger etablieren, der auf JEDE Datenbank-Anfrage reagiert?


Nein.

Frage 2: Hat jemand bessere Vorschläge für die Durchsetzung der Datensicherheit auf Datenbankebene für eine Shared Database / Shared Schema Anwendung? Dürfte ja eine typische Fragestellung für Online-Portale sein .. ?

Besten Dank im Voraus!
Rainer


Alle Zugriffe in Funktionen (security definer) kapseln, alle anderen Zugriffe verbieten.

Andreas

Auge_Ohr
Erfahrener Benutzer
Beiträge: 104
Registriert: So 12. Aug 2012, 20:23

Re: Trigger für jede SQL-Abfrage möglich?

Beitrag von Auge_Ohr »

rhaupt hat geschrieben:für die Modellierung einer mulit-tenant application (also Mandantenfähigkeit) haben wir die Vorstellung, für jeden Datensatz eine Tenant-ID abzulegen. Nun soll sichergestellt werden, dass jede Abfrage auf die Datenbank eine Bestandteil "WHERE ID_Tenant=12345" hat. Abfragen/Zugriffe ohne eine "WHERE ID_Tenant=.." sollen generell verboten sein.
Mandanten-fähig mit einer Datenbank ???

IHMO würde ich für jeden Mandanten eine neue Datenbank anlegen die man über seine Client Applikation erreicht.
innerhalb deiner Applikation kannst du ja mit verschiedenen Connection pro Mandant zugreifen.

rhaupt
Neuer Benutzer
Beiträge: 21
Registriert: Di 26. Mär 2013, 18:25

Re: Trigger für jede SQL-Abfrage möglich?

Beitrag von rhaupt »

na ich weiß ja nicht, wenn du 100 oder mehr Kunden auf Deiner Applikation hast, dann eine kleine Änderung im Datenmodell oder ein neuer Trigger .. und das dann auf allen 100 Datenbanken nachführen? Erscheint mir nicht optimal ... oder wie würdest Du das lösen?

rhaupt
Neuer Benutzer
Beiträge: 21
Registriert: Di 26. Mär 2013, 18:25

Re: Trigger für jede SQL-Abfrage möglich?

Beitrag von rhaupt »

danke an Andreas .. die "security definer"-Funktionen würdest Du in der Datenbank definieren? oder aus der App heraus? Aber klar, es gibt ja meines Wissens Portale, die fast die gesamte Business-Logik in der Datenbank haben .. da wäre es dann ja eigentlich ein logischer Schritt, auch jeden Zugriff über eine Function machen.

akretschmer
Erfahrener Benutzer
Beiträge: 7733
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Trigger für jede SQL-Abfrage möglich?

Beitrag von akretschmer »

rhaupt hat geschrieben:danke an Andreas .. die "security definer"-Funktionen würdest Du in der Datenbank definieren? oder aus der App heraus? Aber klar, es gibt ja meines Wissens Portale, die fast die gesamte Business-Logik in der Datenbank haben .. da wäre es dann ja eigentlich ein logischer Schritt, auch jeden Zugriff über eine Function machen.


Na in der DB, wo sonst ;-)

Wir haben einen $KUNDEN bei uns, wo das richtig cool gelöst ist. Also jetzt NICHT so eine Kapselung vieler Kunden voreinander, sondern Zugriff auf die Daten und Abbildung der Business-Logik sehr stark in der DB. Das macht bei der Anwendung da auch durchaus sehr viel Sinn, weil da sehr viele Meßwerte, welche an Geo-Koordinaten gekoppelt sind, verarbeitet werden. Da ist sehr viel in pl/pgsql und GIS-Funktionen gemacht worden - und die Anwendung selbst ist sehr flott und macht kaum Last.

castorp
Erfahrener Benutzer
Beiträge: 1209
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Trigger für jede SQL-Abfrage möglich?

Beitrag von castorp »

rhaupt hat geschrieben:na ich weiß ja nicht, wenn du 100 oder mehr Kunden auf Deiner Applikation hast, dann eine kleine Änderung im Datenmodell oder ein neuer Trigger .. und das dann auf allen 100 Datenbanken nachführen? Erscheint mir nicht optimal ... oder wie würdest Du das lösen?
Mit einem sauberen Script-Management für die Schemamigrationen.
Wenn das richtig organisiert ist, dann ist sowas kein Thema.

Wir verwenden z.B. Liquibase um die Schemamigrationen zu verwalten. Damit kann man sich sicher sein, dass eine DB nach dem Ausführen der Migrationen auf dem richtigen Stand ist. Liquibase kann man natürlich problemlos in einem Shell/Batchscript automatisch für jede definierte Datenbank aufrufen, damit macht es dann auch keinen Unterschied ob es 1 oder 100 Datenbanken sind (von der Laufzeit mal abgesehen)

Es gibt natürlich noch andere Lösungen außer Liquibase z.B. Flyway.
Das Prinzip bleibt aber immer das Gleiche: die Scripte zum Aufbau des Datenbankschemas (incl aller Triggers, Funktionen, Views, ...) müssen klar strukturiert sein und gehören in eine Versionsverwaltung. Die Skripte sollten sowohl eine DB komplett neu aufbauen als auch eine Migration von Version X zu Version X+n durchführen können.

rhaupt
Neuer Benutzer
Beiträge: 21
Registriert: Di 26. Mär 2013, 18:25

Re: Trigger für jede SQL-Abfrage möglich?

Beitrag von rhaupt »

Vielen Dank für den diversen Input! Getrennte Schemas machen sicher Sinn bis zu einer gewissen Anzahl "Mandanten". Aber ich glaube nicht, dass zB GMX für jeden Email-Account ein separates Schema hat .. ?

Bzgl "Security Definer" hab ich inzwischen einiges gelesen .. klingt sehr passend. ich les mal noch ein bisserl weiter .. bevor ich weiter fremde zeit in anspruch nehme ;-)

Rainer

akretschmer
Erfahrener Benutzer
Beiträge: 7733
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Trigger für jede SQL-Abfrage möglich?

Beitrag von akretschmer »

rhaupt hat geschrieben:Vielen Dank für den diversen Input! Getrennte Schemas machen sicher Sinn bis zu einer gewissen Anzahl "Mandanten". Aber ich glaube nicht, dass zB GMX für jeden Email-Account ein separates Schema hat .. ?

Bzgl "Security Definer" hab ich inzwischen einiges gelesen .. klingt sehr passend. ich les mal noch ein bisserl weiter .. bevor ich weiter fremde zeit in anspruch nehme ;-)

Rainer


Kurze Demo:

Zuerst einmal als Superuser:

Code: Alles auswählen

test=# create database rhaupt;CREATE DATABASE
Time: 304,252 ms
test=# \c rhaupt;You are now connected to database "rhaupt" as user "kretschmer".
rhaupt=# create user rhaupt1;CREATE ROLE
Time: 0,380 ms
rhaupt=*# create user rhaupt2;
CREATE ROLE
Time: 0,125 ms
rhaupt=*# revoke all on schema public from rhaupt1;
REVOKE
Time: 0,437 ms
rhaupt=*# revoke all on schema public from rhaupt2;
REVOKE
Time: 0,155 ms
rhaupt=*# create table foo (user_id text default session_user, value int);CREATE TABLE
Time: 5,338 ms
rhaupt=*# create function insert_foo(int) returns bool as $$begin insert into foo(user_id,value) values (default, $1); return true;end;$$language plpgsql security definer;
CREATE FUNCTION
Time: 1,284 ms
rhaupt=*# create function get_foo() returns setof int as $$begin return query select value from foo where user_id=session_user; end;$$language plpgsql security definer;
CREATE FUNCTION
Time: 0,536 ms
rhaupt=*# commit;
COMMIT
Time: 0,460 ms


Wichtig ist, daß man mit session_user da hantiert, da augfrund security definer der current_user ja der Superuser (Ersteller) ist. bedingt natürlich, daß jeder Mandant in der DB als eigener User angelegt ist, aber das ist ja kein Problem.

Nun in einer anderen Session:

Code: Alles auswählen

kretschmer@tux:~$ psql -U rhaupt1 rhaupt;
Timing is on.                           
psql (9.2.0)                             
Type "help" for help.                   

rhaupt=> select * from foo;
ERROR:  permission denied for relation foo
Time: 0,832 ms
rhaupt=*> select insert_foo(1);
 insert_foo
------------
 t
(1 row)

Time: 1,326 ms
rhaupt=*> select get_foo();
 get_foo
---------
       1
(1 row)

Time: 1,481 ms
rhaupt=*> \q
kretschmer@tux:~$ psql -U rhaupt2 rhaupt;
Timing is on.
psql (9.2.0)
Type "help" for help.

rhaupt=> select get_foo();
 get_foo
---------
(0 rows)

Time: 2,262 ms
rhaupt=*> select insert_foo(2);
 insert_foo
------------
 t
(1 row)

Time: 0,495 ms
rhaupt=*> select get_foo();
 get_foo
---------
       2
(1 row)

Time: 0,201 ms
rhaupt=*>


So hat man eine extra Schicht eingezogen und trennt schön die einzelnen Mandanten. Als Superuser hast natürlich alle rechte, wieder die erste Session:

Code: Alles auswählen

rhaupt=# select * from foo;
 user_id | value
---------+-------
 rhaupt2 |     2
 rhaupt1 |     1
(2 rows)


(ich hatte erst das commit vergessen, daher nun andere reihenfolge...)

.oO( ich muß mal wieder compilieren, 9.2.0, aber das ist hier lokal nur meine Spielwiese)

Antworten