Webapplikation soll zusätzlich Postgres unterstützen

Ich bin gerade dabei für mein OSS Projekt neben MySQL auch Postgres als Datenbank-Backend zu ermöglichen.

Die Lösung muss dabei folgende Rahmenbedingungen einhalten:

  1. Das Projekt ist eine in PHP5 geschriebene, webgestützte Applikation.
  2. Es gibt für jede DB Schnittstelle eine eigene Klasse, die sich gegenüber dem Projekt identisch verhält. Es hängt also grundsätzlich nur von der Klasse ab die man zu Beginn einbindet welcher DB-Typ (mysql, mysqli,postgres) verwendet wird.
  3. Die Kommunikation zwischen dem Projekt und der Datenbank erfolgt ausschliesslich über SQL.
  4. Die Einrichtung (Installation -> Datenbank, Tabellen, User) muss ebenfalls ausschliesslich über SQL erfolgen (Webinstaller).
  5. Das Projekt wird international eingesetzt -> Mehrsprachigkeit mit UTF8.
  6. Die Lösung für MySQL/Postgres sollten sich nicht zu stark unterscheiden, also möglichst kompatible SQL Statements.

Aktuell ist der webgestützte Installer schon recht weit, ich bin aber mit Postgres noch zuwenig vertraut um abzuschätzen ob das was bereits funktioniert auch sinnvoll eingerichtet ist :slight_smile:

Folgende Schritte erfolgen:

  1. Verbindung zur postgres DB aufbauen als Adminuser
  2. Datenbank erstellen:
    CREATE DATABASE meine_DB WITH ENCODING=‘UTF8’;
  3. Verbindung erneut aufbauen zu meine_DB als Adminuser
  4. Postgres SQL Datei öffnen und alle SQL Befehle darin Schritt für Schritt abarbeiten (erstellen der Struktur):
    SET NAMES ‘utf8’;
    CREATE TYPE bin_enum AS ENUM (‘0’,‘1’);
    CREATE TABLE IF NOT EXISTS “tbl_first” (
    “id” SERIAL,
    “name” VARCHAR(255) NOT NULL,
    “description” VARCHAR(255) NOT NULL,
    “users” SMALLINT NOT NULL DEFAULT ‘0’,
    “active” bin_enum NOT NULL DEFAULT ‘1’,
    “last_modified” TIMESTAMP NOT NULL,
    UNIQUE (“groupname”),
    PRIMARY KEY (“id”)
    ) ;
    […] (weitere 100 Tabellen)
  5. User erstellen:
    CREATE USER mein_user WITH PASSWORD ‘mein_password’;
  6. User berechtigen:
    GRANT ALL ON DATABASE meine_DB TO mein_user;
  7. Berechtigung für alle Tabellen:
    SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’;
    …dann für jeden Eintrag dieser Abfrage:
    GRANT SELECT, UPDATE, INSERT, DELETE ON “”.$elem[‘table_name’]."" TO mein_user;
  8. Berechtigung für alle Sequenzen:
    SELECT sequence_name FROM information_schema.sequences;
    …dann für jeden Eintrag dieser Abfrage:
    GRANT SELECT, UPDATE ON “”.$elem[‘sequence_name’]."" TO mein_user;
  9. Verbindung erneut aufbauen zu meine_DB als mein_user
  10. Daten in die DB abfüllen

Grundsätzlich klappt das, aber ich bin mir nicht sicher was folgende Dinge betrifft:

  • Braucht der Benutzer die DB Rechte von Punkt 6 - bei MySQL hat er nur Rechte entsprechend Punkt 7 auf die DB-Tabellen
  • Einzelne Datenfelder und Tabellennamen müssen in Anführungszeichen geschrieben werden - hier scheint Postgres nur " zu akzeptieren - bei MySQL war es `.

Wenn ich mit phpPgAdmin die DB danach anschaue ist die DB unter dem Schema “Public”. Ist das richtig?

Gibt es andere Tipps, was man besser machen könnte?

Vielen Dank :wink:

Martin

Soll das Hosting-fähig sein?

Falls ja, wird das CREATE DATABASE wohl fehlschlagen - zumindest bei uns. Da wäre es besser, wenn der Anwender die Verbindungsdaten zu einer bestehenden DB eingeben kann.
Public-Schema ist okay, Rechte brauchst eher nicht zu setzen, wenn Du dich gleich mit den Zugangsdaten verbindest.

Ach ja: PostgreSQL hat einen BOOL-Typ.

Allgemein: schön, daß Du das so einbauen willst. Meine Erfahrung ist aber, daß der Versuch, eine DB-Unabhängigkeit zu erreichen einhergeht damit, daß man die Nachteile aller Welten in seine Lösung vereinigt. Aber wie gesagt, schön, daß Du an PG denkst.


Hint: wir haben kostenfreie testaccounts, also, falls Du das mal in einer Hosting-Umgebung testen willst, …


Andreas

Die Herausforderung ist halt, dass ich nicht mehr auf der grünen Wiese damit beginnen kann, weil das Tool bereits seit mehreren Jahren existiert und recht weit verbreitet ist. Bisher gab es lediglich eine MySQL DB Schnittstelle dazu, in der letzten Zeit stieg nun aber auf Anwenderseiteseite - nicht zuletzt aufgrund der Unsicherheit von MySQL/Oracle - der Wunsch neu auch Postgres verwenden zu können.

Ziel ist somit eine zusätzliche Adaption von Postgres mit möglichst geringen Anpassungen an den Abfragen und der DB Struktur. Insbesondere muss es weiterhin auf bestehende MySQL Datenbanken passen mit möglichst geringem/einfachem Upgradeaufwand. Cross-Update bleibt aber ausgeschlossen.

Der BOOL Typ klingt durchaus gut - müsste dann aber auch für MySQL adaptiert werden und durchgängig so angepasst werden. Die Wahl eines ENUM Types ist ein Fehler aus der Vergangenheit - muss ich vermutlich in einem grösseren Release (der aktuell nicht ansteht) einmal grundsätzlich anpassen :slight_smile:

Hosting ist eher weniger ein Thema, da das Tool im Bereich Monitoring/Systemüberwachung eingesetzt wird und ausschliesslich von Systemadministratoren installiert wird welche lokale Adminrechte besitzen und wohl in fast allen Fällen auch Postgres selber installiert haben.

Welche Rechte meinst Du bauche ich nicht zu setzen? DB-Rechte, Tabellenrechte oder Sequenzrechte. Letztere hatte ich nämlich erst vergessen was prompt in einer Fehlermeldung mündete beim Eintragen von Daten (keine Rechte für tbl_xy_id_seq), obwohl ich mit dem neuen User/PW/DB verbunden habe.

Martin

Ich würde 2) 5) und 6) anders machen:

  • als superuser verbinden, dann Benutzer “mein_user” anlegen
  • als superuser ein CREATE DATABASE … OWNER = mein_user ausführen. Damit kann der Benutzer alles mit der (aber nur der) Datenbank machen.
  • als mein_user verbinden und das Skript laufen lassen. Das zusätzliche granten der Privilegien entfällt dann.

Einzelne Datenfelder und Tabellennamen müssen in Anführungszeichen geschrieben werden - hier scheint Postgres nur " zu akzeptieren - bei MySQL war es

PostgreSQL hält sich hier an den Standard (so wie so ziemlich alle anderen Datenbanksysteme) und der ist nunmal ein "
MySQL kann (und sollte meiner Meinung nach auch) so konfiguriert werden, dass es auch " akzeptiert.

Aber von “müssen” kann gar keine Rede sein. Sie sind nur notwendig wenn der Name (Spalte/Tabelle) entweder case-sensitiv sein soll oder aber Zeichen enthält die eigentlich nicht erlaubt sind (Leerzeichen z.B.).

Deswegen ist meine Empfehlung grundsätzlich auf die Verwendung von " (oder ` in MySQL) zu verzichten.
Das macht das Leben erheblich einfacher.

Klingt einfacher. Wobei der Benutzer grundsätzlich gar nicht alle Rechte auf der DB benötigen würde, sondern lediglich SELECT, UPDATE, INSERT, DELETE auf den Tabellen machen muss. Ich denke diese Einschränkung ist dann nicht mehr so einfach möglich, wenn die DB dem Benutzer gehört?

Wieso? Du hast doch getrennte Datenbankklassen - warum müssen die Abfragen oder die Tabellenstruktur identisch sein?

Hinweis: Wenn du diesen Weg gehst, musst du mit dem kleinsten gemeinsamen Nenner gehen - und der ist in der Regel MySQL.
Du wirst nicht glücklich mit diesem Weg, das kenne ich aus langjähriger Erfahrung.
Trenn das komplett, in den Klassen. Und stell der Applikation nur eine API zur Verfügung.


Hosting ist eher weniger ein Thema, da das Tool im Bereich Monitoring/Systemüberwachung eingesetzt wird und ausschliesslich von Systemadministratoren installiert wird welche lokale Adminrechte besitzen und wohl in fast allen Fällen auch Postgres selber installiert haben.

Du könntest allerdings davon lernen: Biete als Auswahl an ob jemand die Datenbank erst erstellen möchte (bei PostgreSQL musst du dafür trotzdem die Zugangsdaten einer bereits existierenden Datenbank angeben, in der Regel “template1” oder “postgres”) oder ob jemand eine existierende Datenbank nutzen möchte.

MySQL kann auch mit " und mit ’ umgehen.


Aber von “müssen” kann gar keine Rede sein. Sie sind nur notwendig wenn der Name (Spalte/Tabelle) entweder case-sensitiv sein soll oder aber Zeichen enthält die eigentlich nicht erlaubt sind (Leerzeichen z.B.).

Je nachdem wie man das Framework einsetzt macht es trotzdem Sinn, pauschal alles zu quoten.
Vermeidet hässliche Fehler, durch die ggf. jemand in die Datenbank eindringen kann.

Sag ich ja :slight_smile:
Aber nur wenn man auch den SQL Modus “ANSI” einschaltet.

Je nachdem wie man das Framework einsetzt macht es trotzdem Sinn, pauschal alles zu quoten.

Also meine Erfahrung ist, dass das zu sehr vielen Problemen führt.

Vermeidet hässliche Fehler, durch die ggf. jemand in die Datenbank eindringen kann.

Hmm, kannst Du mal erklären wie ein Angriff möglich ist, weil man die Namen nicht quoted?

In erster Instanz passiert erst mal nichts - wenn man in der Applikation bis dahin auch alles richtig gemacht hat.
Nur wie das so ist … hier mal was übersehen, da nicht aufgepasst, schon hat man ein Scheunentor offen.

Daher macht es eigentlich Sinn, sowieso alle Daten pauschal zu quoten und außer man schreibt die Bezeichner fest in die Abfrage dann auch diese zu quoten.

Mir ist leider immer noch nicht klar welches “Scheunentor” durch die nicht-Verwendung von Quotes geöffnet wird. Hast Du ein Beispiel?

Nein, kein konkretes. Ich habe allerdings Frameworks gesehen die z. B. Spaltennamen direkt aus HTML durchgereicht haben.
In so einem Fall (auch wenn man als Autor des Frameworks da vielleicht gar nichts für kann) möchte man eigentlich alles quoten.
Andernfalls hast du eine Tür für SQL-Injections geöffnet.

Bei der Migration bin ich nun noch auf ein weiteres Problem gestossen, bei dem sich PostgreSQL anders verhält als MySQL.

Ich erstelle eine Tabelle:
CREATE TABLE IF NOT EXISTS “tbl_mytable” (
“id” SERIAL,
“name” VARCHAR(255) NOT NULL,
“alias” TEXT NOT NULL,
“last_modified” TIMESTAMP NOT NULL,
PRIMARY KEY (“id”),
UNIQUE (“name”)
) ;

Nun füge ich Daten hinzu:
INSERT INTO “tbl_mytable” (“id”, “name”, “alias”, “last_modified”) VALUES(1, ‘testname’, ‘testalias’, NOW());

Klappt problemlos

Nun füge ich weitere Daten hinzu:
INSERT INTO “tbl_mytable” (“name”, “alias”, “last_modified”) VALUES(‘testname2’, ‘testalias2’, NOW());

Jetzt erhalte ich den Fehler:
FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »tbl_mytable_pkey«
DETAIL: Schlüssel »(id)=(1)« existiert bereits.

Bei MySQL stellt dieses Beispiel kein Problem dar, da MySQL hier den “AUTO_INCREMENT” Wert immer auf die höchste freie ID setzt, egal ob ein INSERT von Daten mit Angabe der ID oder ohne erfolgt. Grundsätzlich ist das lösbar - die Frage ist also lediglich ob es eine Möglichkeit gibt, dass PostgreSQL die ID auch immer automatisch mitführt oder ob es einen Befehl gibt um den Wert einer Tabelle manuell zu aktualisieren (suche höchste freie id und setze letzten Wert entsprechend).

Du musst den Wert der Sequence auf den aktuell höchsten Wert in deiner Tabelle setzen.
Die Dokumentation zu setval() dürfte dich in dem Zusammenhang interessieren :wink: