Eine Postgres Instanz für mehrere Applikationen

Hallo,

jetzt wende ich mich mal an die Allgemeinheit.
Seit ein paar Wochen lese ich mich intensiv ein, um eine Postgres Instanz für mehrere Applikationen zu erstellen.
Gleich vorweg, ich bin weit weg davon ein DB-Guru zu sein. Bisher war ich eher mit DB-Abfragen beschäftigt.

Einige Applikationen nutzen bisher SQLite als Datenbank, diese möchte ich nun auf eine Postgres Instanz zusammenlegen.

Dafür würde die aktuelle Version PostgreSQL 16.1 zum Einsatz kommen.

Ich habe schon einiges an Möglichkeiten gelesen.

  • Eine DB mit Multiplen Schemas zur Trennung zwischen den Applikationen
  • Je Applikation eine eigene Datenbank.
  • Je Applikation eine eigene Instanz.

Habe mich für “je Applikation eine eigene Datenbank” entschieden.
Ersteres würde sicherlich dann mehr Sinn machen, wenn eine Kommunikation innerhalb/zwischen den Schema/Applikationen stattfinden würde.
Dies kommt aber bis auf 1/2 Ausnahmen nicht vor.


Jetzt zu meinen Ideen. Bitte gerne Feedback dazu. Gibt es irgendwo grobe Schnitzer, etc …

  1. listen_addresses beschränken

  2. pg_hba.conf mit Regex als bsp. /^db1_.* nur bestimmte User auf bestimmte Datenbanken begrenzen

  3. Auch wenn für meinen Fall selten nötig, würde ich gleich alles über Gruppen/Rollen lösen.
    Folgende Gruppen ddl_ (Admin), dml_ (App), read_only_, etc…

  4. Hier bin ich mir gerade gar nicht sicher. Gruppen/Rollen DB übergreifend oder doch per Database?

ddl_grp
dml_grp
read_only_grp

… oder doch für jede Datenbank …

db1_ddl_grp
db1_dml_grp
db1_read_only_grp

Da die Applikationen bisher keine besonderen Berechtigungen benötigen, so meine ich, kann ich auf die erstere Variante zurückgreifen.
Sollte doch mal speziellere Rechte benötigen werden, so würde ich dann dafür eine eigene Gruppe erstellen.


Und hier wie ich dies bisher noch am testen bin.

pg_hba.conf

host	/^db1_	/^db1_	10.0.0.2/32	scram-sha-256

psql:

-- erstelle für die gesamte Instanz einmalig die benötigten Rollen.
CREATE ROLE ddl_grp WITH NOLOGIN;
CREATE ROLE dml_grp WITH NOLOGIN;
CREATE ROLE read_only_grp WITH NOLOGIN;


-- erstelle User Accounts
CREATE ROLE db1_admin WITH LOGIN PASSWORD 'supersecretpw1';
CREATE ROLE db1_prod WITH LOGIN PASSWORD 'supersecretpw2';
CREATE ROLE db1_read_only WITH LOGIN PASSWORD 'supersecretpw3';


-- erstelle Datenbank
CREATE DATABASE db1_db OWNER db1_admin;


-- verknüpfe User Rolle mit Gruppen Rolle
GRANT ddl_grp TO db1_admin;
GRANT dml_grp TO db1_prod;
GRANT read_only_grp TO db1_read_only;


-- wechsele auf Datenbank
\c db1_db db1_admin


-- bearbeite Public Schema
REVOKE ALL ON DATABASE db1_db FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- fügt allen Gruppen/Rollen die Basis Rechte CONNECT, TEMPORARY hinzu.
GRANT CONNECT, TEMPORARY ON DATABASE db1_db TO ddl_grp, dml_grp, read_only_grp;


-- erstelle Schema
CREATE SCHEMA db1_schema AUTHORIZATION db1_admin;


-- Regel für vorhandene Tabellen (Rolle ddl_grp) …
GRANT USAGE, CREATE ON SCHEMA db1_schema TO ddl_grp;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "db1_schema" TO ddl_grp;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA "db1_schema" TO ddl_grp;


-- Regel für vorhandene Tabellen (alle weiteren Rollen) …
GRANT USAGE ON SCHEMA db1_schema TO dml_grp, read_only_grp;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA db1_schema TO dml_grp;
GRANT SELECT ON ALL TABLES IN SCHEMA db1_schema TO read_only_grp;

GRANT UPDATE ON ALL SEQUENCES IN SCHEMA db1_schema TO dml_grp;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA db1_schema TO dml_grp, read_only_grp;


-- Regel für zukünftige Tabellen
ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA db1_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dml_grp;
ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA db1_schema GRANT SELECT ON TABLES TO read_only_grp;
ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA db1_schema GRANT UPDATE ON SEQUENCES TO dml_grp;
ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA db1_schema GRANT SELECT, USAGE ON SEQUENCES TO dml_grp, read_only_grp;

Was sagt Ihr dazu?
Ich bin wirklich auf Feedback gespannt.

Viele Grüße
Chris

Zwei Applikationen in derselben Datenbank betreiben würde ich nur, wenn sie dieselben Daten benützen müssen. Dadurch wird die Verwaltung komplizierter (z.B. das Verlegen der Daten einer der Applikationen anderswohin). Außerdem ist es ein Sicherheitsproblem: ein Angreifer, der in die Datenbank einer Applikation eindringt, kann auch Daten stehlen oder zerstören, die zu anderen Applikationen gehören.

Für die Frage, ob es mehrere Datenbanken in einem Cluster oder mehrere Cluster mit je einer Datenbank sein sollen: nur einen Cluster zu haben, reduziert den Verwaltungsaufwand ein wenig (Backup, Upgrade) und spart ein bißchen Resourcen. Allerdings sind Datenbanken, die in einem Cluster liegen, in vielem verbunden: wenn man eine Datenbank abdrehen möchte, muß man alle abdrehen; ebenso beim Upgrade. Das kann unangenehm sein, wenn die Applikationen nicht zusammengehören oder von denselben Leuten benützt werden. Im Zweifelsfall würde ich mehrere Cluster nehmen.

Hallo laurenz,

danke für deine Rückmeldung.
Auch wenn es mich erst mal wieder Zurückgeworfen hat, gehe ich diesen Weg jetzt nach.
Das Sicherheitsproblem sollte so nicht bestehen, sollte nämlich ein Angreifer wirklich eine Applikation übernehmen, so wär dieser auf diese eine Datenbank beschränkt gewesen. :slight_smile:

Da mir die Erfahrung im Bezug Verwaltung fehlt, musste ich nochmals darüber nachdenken, nach einiger Überlegung stimme ich diesem zu.
Zwar steht nicht in Verlegen der Daten, etc. an. Aber man weiß heute nicht was eventuell noch kommen wird.

Zwar habe ich auch nicht mit einem Abdrehen der DB/Cluster gerechnet, aber auch hier, man weiß nie was noch kommt.
Vor einem Jahr ist dies sogar 1x vorgekommen, war ein Postgres Cluster mit einer DB welche wegen einem Neu aufsetzen 3-4 Stunden nicht erreichbar war.
Jetzt sehe ich den Vorteil in Cluster Aufteilung, nur diesen einen Cluster herunterzufahren und die anderen sind davon nicht berührt.

Die Rollen/Datenbank Erstellung (siehe oben) hätte auch in diesem Fall seine Gültigkeit.
Gibt es daran etwas auszusetzen?
Z.b. hatte ich vorher einen Test gemacht und nur einen “Owner” User angelegt, dann gab es aber Probleme mit dem setzen der “ALTER DEFAULT PRIVILEGES”.
Darum hab ich nun der Gruppen Rolle “ddl_grp” den Owner Status gegeben. Damit ging es.

Aber gibt es an diesem Beispiel prinzipiell etwas auszusetzen?

Ich teste/arbeite gerade noch zum Thema Clustererstellung weiter, komme aber sicher noch mit der ein oder anderen Frage zurück. :slight_smile:

Ach ja, da gibt es schon die erste Frage…
Vorher hatte ich nur einen Cluster zu Konfigurieren. Damit meine ich die Settings in der “postgresql.conf”.
Es gibt ja die Web Tools pgtune oder pgconfig für eine schnelle Grund konfiguration.
Als einfaches bsp. “Total Memory” wie muss ich es betrachten?
Sagen wir mal ich habe 10GB, müsste ich nun umdenken und 5GB für jeden Cluster angeben? Oder bezieht es sich, wie der Name es schon verrät, auf das System und ich kann/soll 10GB weiterhin angeben?

Viele Grüße
Chris

Die Frage zu den Benutzern und Rechten, kann ich nicht beantworten, dazu habe ich zu wenig vom Setup verstanden.
Klar, das RAM muß man sich auf die verschiedenen Cluster aufgeteilt denken, und jeder bekommt nur einen Teil. Ich würde shared_buffers eher konservativ setzen, denn PostgreSQL nutzt auch das „ungenutzte” RAM, und auf diese Weise kann jede Datenbank das belegen, was sie gerade braucht.