Webanwendung mit PostgreSQL als Datenbank - Nutzername in Triggerfunktion

Hallo,
ich bräuchte Unterstützung oder Ideen zur Lösung meines Problems:
Name des in der Webanwendung (verifiziert gegen ein ActiveDirectory) angemeldeten Nutzers soll in der Triggerfunktion verwendet werden, damit im Änderungsnachweis erkennbar ist, wer wann welche Einträge vorgenommen/geändert/gelöscht hat.

Dieser Nachweis ist für ausgewählte Tabellen zwingend notwendig, bei anderen wünschenswert - weil “Ich habe das nicht gemacht!” ist die häufigste Antwort bei der Frage “Wer hat den Wert dort eingetragen?”.

Eckdaten:

  • Webanwendung


  • PHP/HTML/CSS/JavaScript, wobei Datenbankzugriffe NUR über PHP programmiert wurden und werden


  • Authentifizierung mittels ActiveDirektory


  • Einträge in pg_hba.conf:
    host DB all IP-BEREICH ldap ldapserver=IP-ADRESSE ldapprefix=“PREFIX” ldapsuffix=""
    wobei für jede DB davor noch Einträge für administrative (anmeldungsbezogene) Anmeldungen mit Passwort vorhanden sind


  • Trigger, um Änderungen in der DB anmeldungsbezogen zu dokumentieren - siehe oben!

Problem:
Für den Verbindungsaufbau zur Datenbank aus der Anwendung heraus wird Nutzername und PASSWORD benötigt. Das Password während der Ausführung (vom Zeitpunkt der Anmeldung mit Abgleich gegen das AD bis zur Abmeldung bzw. Verfall der Session) in einer PHP-Session-Variablen zu speichern finde ich suboptimal, weil der Zugriff darauf (mit etwas gutem Willen) möglich ist. Aktuell stelle ich über Prozeduren sicher, daß bei Codeänderungen ein sofortiger Stop der Ausführung erfolgt. Dies ist ein Minimalschutz, aber nicht das, was ich mir letztendlich vorstelle.

Vielleicht ist ja auch mein Ansatz nicht ideal oder einfach falsch, aber für die Dokumentation der Änderungen an ausgewählten Tabellen ist die Zuordnung des angemeldeten Nutzers (in PHP gegen das AD verifiziert) zwingend erforderlich.

Für die betreffenden Tabellen werden Triggerfunktionen erstellt:

CREATE OR REPLACE FUNCTION public.TABELLE_delete()
  RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO public.tupda(aenderung_nutzer,aenderung_datum,aenderung_zeit,aenderung_tabelle,aenderung_feld,aenderung_wert_alt,aenderung_wert_neu,primary_key_wert) VALUES(current_user,now(),now(),'TABELLE','gruppe|gruppe_beschreibung|gruppe_system|gilt_bis',OLD.gruppe || '|' || OLD.gruppe_beschreibung || '|' || OLD.gruppe_system || '|' || OLD.gilt_bis,'GELOESCHT',OLD.pk_vw01);
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION public.TABELLE_last_change()
  RETURNS trigger AS
$BODY$
BEGIN
if (TG_OP = 'UPDATE') THEN
INSERT INTO public.tupda(aenderung_nutzer,aenderung_datum,aenderung_zeit,aenderung_tabelle,aenderung_feld,aenderung_wert_alt,aenderung_wert_neu,primary_key_wert) VALUES(current_user,now(),now(),'TABELLE','gruppe|gruppe_beschreibung|gruppe_system|gilt_bis',OLD.gruppe || '|' || OLD.gruppe_beschreibung || '|' || OLD.gruppe_system || '|' || OLD.gilt_bis,NEW.gruppe || '|' || NEW.gruppe_beschreibung || '|' || OLD.gruppe_system || '|' || NEW.gilt_bis,NEW.pk_vw01);
else
INSERT INTO public.tupda(aenderung_nutzer,aenderung_datum,aenderung_zeit,aenderung_tabelle,aenderung_feld,aenderung_wert_alt,aenderung_wert_neu,primary_key_wert) VALUES(current_user,now(),now(),TABELLE','gruppe|gruppe_beschreibung|gilt_bis','NEUANLAGE',NEW.gruppe || '|' || NEW.gruppe_beschreibung || '|' || NEW.gruppe_system || '|' || NEW.gilt_bis,NEW.pk_vw01);
END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

current_user (in den Triggerfunktionen) ist ja der an der Datenbank (über den connect-String verbundene) angemeldete Nutzer - ich habe hierfür aber keinen anderen Ansatz gefunden. Die Angabe muß dem angemeldeten (Webanwendung) Nutzer entsprechen.

Danach werden die Trigger zum Aufruf der Funktion beim Eintreten der Ereignisse definiert:

-- Anlegen Trigger
CREATE TRIGGER "delete"
  AFTER DELETE
  ON public.TABELLE
  FOR EACH ROW
  EXECUTE PROCEDURE public.TABELLE_delete();

CREATE TRIGGER last_change
  BEFORE INSERT OR UPDATE
  ON public.TABELLE
  FOR EACH ROW
  EXECUTE PROCEDURE public.TABELLE_last_change();

Hat jemand eine Idee oder kann mir auch mitteilen “Das, was Du machen willst - geht so nicht!” - auch gerne dies, dann brauche ich keine Energie zu vergeuden. Aber irgendwie muß es doch machbar sein…

Einen Gedanken, den ich schon mal hatte, wäre ein Nutzer in der Datenbank, der sich durch einen Hashwert vom Namen des Nutzers eindeutig erzeugen läßt. Aber dies muß eben eindeutig sein und die Anforderungen an die Komponenten zur Bildung des Hash (Algorithmus, Länge des zu ‘hashenden’ Wertes, eventuell Domainangaben und Salt zum Nutzernamen ergänzen, …) sind mir nur oberflächlich bekannt. Mit gefährlichem Halbwissen zu agieren ist nicht mein Ding, daher müßte ich hier weitere Recherchen betreiben.

Ich bin Euch für JEDE Idee dankbar!

Gruß Rainer

also, wenn ich Dich richtig verstehe, das läuft immer mit einem Applikationsuser, zumindest aus Sicht der Datenbank. Nur die Applikation kennt den eigentlichen, konkreten User.

Die DB kann in diesem Szenario nicht wissen, was die aufrufende Applikation weiß, aber diese kann einen Hinweis geben. Der Trick wäre, hier “application_name” passend zu setzen. Im TRIGGER kannst Du via pg_backend_pid() die PID rausbekommen und über diese aus pg_stat_activity den application_name.

hope that helps …

Hallo,
sind mir bisher unbekannte Optionen - daher danke für den Hinweis und werde mich mal auf die Suche nach Umsetzungsmöglichkeiten machen.

Soweit ich Dich verstanden habe, muß ich der aufrufenden Webanwendung, die - richtig - den Nutzer “kennt”, etwas in der Verbindung zur Datenbank “auf den Weg geben”.

Ich danke Die für die Anregung!

Gruß Rainer

nutzt Du PHP?

http://php.net/manual/de/function.pg-connect.php, bei den ‘User Contributed Notes’ findest Du ein Beispiel. Mit psql:

psql 'host=localhost port=5432 dbname=test application_name=bla'
psql (11beta1 (Ubuntu 11~beta1-2.pgdg16.04+1), Server 9.5.13)
Geben Sie »help« für Hilfe ein.

test=# show application_name;
 application_name 
------------------
 bla
(1 Zeile)

test=*#

Hallo,
ja - das hatte ich vor kurzem gefunden und bin schon am “probieren”, denn ich möchte das, was da passiert, nachvollziehen können.

Aber das könnte der Ansatz sein, wenn ich den Wert in einer PHP-Session Variable ablege, PostgreSQL ist die Version 9.6 - hatte ich beim Startpost vergessen anzugeben.

Danke für Deine schnellen Hinweise!

Gruß Rainer

you are welcome :wink:

Hallo akretschmer,
ich konnte zwischenzeitlich die ersten vielversprechenden Tests erfolgreich abschließen! - Vielen Dank für den treffenden Hinweis auf application_name als Parameter der Verbindungskennung.

Nun muß ich “nur noch” die Zugriffe bezüglich der Datenbankberechtigungen anpassen, aber das ist (zumindest auf den ersten Blick) übersichtlich.

Für Diejenigen, die ein ähnliches “Problem” haben:
Damit application_name nach meinen ersten Versuchen eindeutig ist, habe ich den Wert (Anmeldename des Nutzers an der Webapplikation) in Sterne gepackt, sicher wäre auch was Anderes gegangen(!?). Ich muß, in meinen Augen, etwas eindeutiges haben, sonst bekomme ich auch andere Werte von application_name. Hatte es mit der pgAdmin-4.3-GUI getestet.

Die Triggerfunktionen enthalten nun nicht mehr den Verweis current_user, sondern stattdessen:
(SELECT application_name FROM pg_stat_activity WHERE application_name like ‘%’) <== hier die Sterne - ober eben was Anderes(!?)

Die Verbindungskennung wird wie folgt gebildet:
host=127.0.0.1 port=5432 dbname=NameDatenbank user=NutzerZugriffDatenbank password=PassendesKennwort application_name=" . $_SESSION[‘nutzer’] . "" <== hier auch die Sterne - oder eben was Anderes (!?) und die PHP-Session-Variable, die, neben einem weiteren Wert, den Anmeldestatus sicherstellt.

Das Kennwort wird jetzt nur noch zum Abgleich mit dem AD benötigt und danach verworfen. Sobald die Session verfällt oder gelöscht wird, muß sich der Nutzer neu anmelden.

Gruß und nochmals vielen Dank
Rainer

neee, geht einfacher…

select application_name from pg_stat_activity where pid = pg_backend_pid();

Hallo,
Danke für Deine Ergänzung, dann kann ich “meine Eindeutigkeit (Sterne)” rausschmeißen.

Ich hatte nach Deinem ersten Hinweis auf pg_backend_pid() danach gesucht, bin aber mit den Ergebnisse nicht klar gekommen. Die Abfrage ohne WHERE lieferte auch manchmal mehrzeilige Ergebnisse…

Dann mache ich das mal so. :wink:

Vielen Dank
Gruß Rainer