tl;dr
Wenn man bei PostgreSQL einzelne gelöschte Datenbanken, deren Nutzer sowie alle damit zusammenhängenden Berechtigungen wiederherstellen möchte, reichen pg_dump und pg_dumpall --globals-only nicht aus. Zusätzlich muss man kreativ im vollständigen Dump von pg_dumpall rumgreppen.

Wie ich schon in mehreren Blogartikeln schrub, ist die Betätigung im Umfeld von PostgreSQL lehrreich und bisweilen überraschend. Das Folgende wird ein etwas längeres Posting, da der Kontext des beschriebenen Phänomens/Problems etwas umfangreicher ist.

Daneben sollte ich für einige kritische Leser evtl. noch vorher erwähnen, dass ich den ganzen Kram hier auch schreibe, damit ich mich später erinnere, warum ich es so gemacht habe…

Hintergrund

Wie sicherlich die meisten meiner Leser wissen, betreut meine Wenigkeit hauptberuflich (und das natürlich nur neben unzähligen anderen Dingen 😉 ) die zentralen PostgreSQL-Datenbankserver der Humboldt-Universität, auf denen Wissenschaftler, Projektmitarbeiter, usw. ihre Datenbanken ablegen können.

Auf PostgreSQL-Ebene wird das Ganze so umgesetzt, dass jeder Nutzer nach Beantragung ein oder mehrere Datenbanken (in den folgenden Beispielen “datenbank” genannt) mit je einem eigenen Datenbankaccount (im Folgenden “nutzer”) erhält. Zusätzlich kann es je nach Bedarf weitere Datenbankaccounts (im Folgenden “unternutzer”) mit optional eingeschränkten Zugriffsrechten geben.

Standardrechte in PostgreSQL

Bereits beim Anlegen der Datenbanken muss man tierisch aufpassen, da das Standardverhalten von PostgreSQL offensichtlich für andere Einsatzszenarien vorgesehen ist: auf eine neu angelegte Datenbank hat automatisch jeder, dem Server bekannte Nutzer Zugriff!

Um dies zu verhindern, lautet die erste Anweisung, die bei uns automatisch bei jeder neuen Datenbank ausgeführt wird:

REVOKE ALL ON DATABASE datenbank FROM PUBLIC;


Damit werden der magischen Rolle PUBLIC alle Rechte entzogen. Dass der Nutzer dennoch an seine Datenbank kommt, liegt daran, dass sein Account als Owner der Datenbank weiterhin alle Rechte besitzt.

Da dies jedoch nicht für die weiteren Accounts des Nutzers gilt, müssen für diese die Zugriffsberechtigungen (bei uns ebenfalls automatisch) nachgetragen werden:

GRANT CONNECT ON DATABASE datenbank TO unternutzer;


Die zweite Falle lauert eine Ebene tiefer: Auf das Standardschema namens public (nicht zu verwechseln mit der bereits genannten, gleichnamigen Rolle) darf jeder mit der Datenbank verbundene Nutzer (egal welchen Einschränkungen er sonst unterliegt) per default eigene Objekte (also Tabellen, Views, usw.) anlegen.

Also folgen bei uns den bereits genannten Befehlen beim Anlegen einer Datenbank noch:

\c datenbank;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO nutzer;


…sowie für jeden einzelnen Unteraccount:

\c datenbank;
GRANT USAGE ON SCHEMA public TO unternutzer;


Soweit so gut – kommen wir zum Backup:

Logisches Backup/Restore im Allgemeinen

Bei PostgreSQL hat man, wenn man nur logische Backups betrachtet, die Wahl zwischen 2,5 Werkzeugen: pg_dump, pg_dumpall und pg_dumpall mit dem Schalter -g.

Ohne zu tief in die Details einzusteigen (die ich teilweise schon an anderer Stelle beschrieben habe), leisten die Werkzeuge folgendes:

• pg_dump zieht eine einzelne Datenbank ab
• pg_dumpall zieht den gesamten Cluster ab (Datenbanken und sonstige Objekte, z.B. Rollen)
• pg_dumpall -g zieht nur die sonstigen Objekte (globals) ab

Wir lassen täglich alle genannten Werkzeuge laufen: je einmal pg_dumpall und pg_dumpall -g auf jedem unserer Cluster und dann noch einmal pg_dump für jede einzelne Datenbank.

Interessant ist nun die Frage, welches Backup wir zur Wiederherstellung verwenden und die wenig überraschende Antwort lautet: Kommt drauf an…

Wenn man den gesamten Cluster wiederherstellen muss, ist die Antwort leicht: man legt einen neuen Cluster an und spielt den pg_dumpall-Dump ein (kam bisher erfreulicherweise nur geplant bei großen Datenbankupdates vor).

Hat ein Nutzer nur eine Tabelle gelöscht, kann man mit dem pg_dump-Dump sehr selektiv nur diese wiederherstellen (kommt sehr selten vor).

Der mit Abstand häufigste Fall ist jedoch das komplette Wiederherstellen einer einzelnen Datenbank, gerne nachdem wir(!) sie gelöscht haben – nein, nur weil ein Nutzer sagt, dass er eine Datenbank nicht mehr braucht, heißt nicht, dass er sie nicht mehr braucht – und bisweilen merkt er das erst deutlich später. In diesem Fall existiert nicht nur die Datenbank nicht mehr; auch die Datenbankaccounts haben durch uns bereits das Zeitliche gesegnet.

Aus diesem Grund hilft dann auch der reine pg_dump-Dump nicht mehr weiter, da dieser die Existenz aller Datenbankaccounts voraussetzt.

Logisches Backup/Restore einzelner Datenbanken inkl. Nutzer

Naiver Ansatz: man greppt aus dem pg_dumpall -g (geht deutlich schneller als den pg_dumpall-Dump zu nehmen) alle CREATE- und ALTER-ROLE-Statements raus und führt diese vor dem Einspielen des Dumps aus. Dieser Ansatz hat aber leider zwei Nachteile: erstens muss man alle betroffenen Datenbankaccounts kennen und, viel schlimmer, einige Berechtigungen werden nicht wiederhergestellt. pg_dump speichert nämlich nur die Informationen inklusive Berechtigungen die sich innerhalb einer Datenbank befinden. Das CREATE-DATABASE-Statement legt pg_dump nur auf besonderen Wunsch und dann aber auch nur mit dem ganz oben beschriebenen Standardverhalten (alle haben Zugriff) an.

Der pg_dumpall-Dump enthält alle notwendigen Informationen; leider kann man aus diesem aber keine einzelnen Datenbanken wiederherstellen (es sei denn, man editiert von Hand in dem riesigen Dumpfile, bei uns im GB-Bereich, rum).

Als Lösung verwenden wir folgenden Ansatz: wir greppen im pg_dumpall-Dump nach allen GRANTS auf die wiederherzustellende Datenbank und greppen die CREATE-ROLE-Anweisungen der dabei gefundenen Datenbankaccounts dann aus dem pg_dumpall -g-Dump. Dann müssen wir nur das CREATE-DATABASE- sowie die GRANT- und REVOKE-Statements aus dem pg_dumpall-Dump holen.

Sobald wir PostgreSQL mit den gesammelten SQL-Anweisungen gefüttert haben, erhalten wir eine leere Datenbank samt aller Nutzer und deren Berechtigungen, die nur noch darauf wartet, mittels des pg_dump-Dumps befüllt zu werden.

Das beschriebene Zusammenklauben aller SQL-Anweisungen leistet das folgende Shellskript:

#!/bin/bash

# Nach allen Nutzern suchen, die Rechte
# auf der Datenbank haben...
for i in `\
	grep "^GRANT .* ON DATABASE $DATABASE TO" $PGDUMPALLFILE \
	| sed "s/^GRANT .* ON DATABASE $DATABASE TO \(.*\);/\1/" \
	`;
do
	# ... und zu diesen Nutzern die CREATE- und
	# ALTER-ROLE-Anweisungen holen
	fgrep -w "ROLE $i" $PGDUMPALLGLOBALFILE ;
done

# Die CREATE- und ALTER-DATABASE- sowie die zugehörigen
# REVOKE- und GRANT-Anweisungen holen
fgrep -w "DATABASE $DATABASE" $PGDUMPALLFILE;

Nur der Vollständigkeit halber – der anschließende Aufruf von pg_restore sieht folgendermaßen aus:

pg_restore -1 -d $DATABASE $PGDUMPFILE

Yep, PostgreSQL macht Spaß…