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 …
-
listen_addresses beschränken
-
pg_hba.conf mit Regex als bsp. /^db1_.* nur bestimmte User auf bestimmte Datenbanken begrenzen
-
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… -
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