Tag 2: Datenbanken, Schemas, Rollen und Rechte
Ziel: Du legst Datenbanken mit Vorlage und Owner an, ordnest Objekte in Schemas, baust Gruppenrollen und vergibst Rechte so, dass jede Rolle genau das darf, was sie braucht – auch für Tabellen, die es noch nicht gibt. · Dauer: ca. 90 min · Voraussetzung: Tag 1
Briefing
- Ein Cluster enthält Datenbanken, eine Datenbank Schemas, ein Schema Tabellen, Sequenzen, Funktionen. Rollen gelten clusterweit.
- „Benutzer“ und „Gruppe“ sind beides Rollen;
LOGINmacht einen Benutzer daraus. - Wer ein Objekt anlegt, besitzt es. Alle anderen brauchen Rechte –
auf jeder Ebene: Datenbank (
CONNECT), Schema (USAGE), Objekt (SELECT…). PUBLICheißt „alle Rollen, auch künftige“ und hat ab Werk mehr Rechte, als man denkt.
Rolle --CONNECT--> Datenbank --USAGE--> Schema --SELECT/INSERT--> Tabelle
--USAGE---------> Sequenz
Alles auf pg1, außer es steht pg2 dran.
Datenbank tag02, Rollen mit Präfix tag02_.
Drill 1: Datenbank aus Vorlage mit Owner
CREATE DATABASE kopiert eine Vorlage, ab Werk
template1. Eigene Vorlagen tragen Grundausstattung in jede
neue Datenbank:
psql <<'EOF'
CREATE ROLE tag02_owner NOLOGIN;
CREATE DATABASE tag02_vorlage;
\c tag02_vorlage
CREATE TABLE einstellungen (schluessel text PRIMARY KEY, wert text);
INSERT INTO einstellungen VALUES ('version', '1');
\c postgres
CREATE DATABASE tag02 TEMPLATE tag02_vorlage OWNER tag02_owner;
EOF
psql -d tag02 -c '\dt' -c 'TABLE einstellungen'Erwartet (gekürzt):
CREATE DATABASE
Schema | Name | Type | Owner
--------+---------------+-------+--------
public | einstellungen | table | ubuntu
schluessel | wert
------------+------
version | 1
Die Datenbank gehört tag02_owner, die kopierte Tabelle
aber weiter ubuntu – OWNER gilt nur für die
Datenbank selbst. Eine Vorlage darf beim Kopieren keine Verbindungen
haben:
psql -d tag02_vorlage -c 'SELECT pg_sleep(12)' >/dev/null &
sleep 1
psql -c 'CREATE DATABASE tag02_kopie TEMPLATE tag02_vorlage'
wait
psql -c 'ALTER DATABASE tag02_vorlage IS_TEMPLATE true'
psql -c 'DROP DATABASE tag02_vorlage'Erwartet:
ERROR: source database "tag02_vorlage" is being accessed by other users
DETAIL: There is 1 other session using the database.
ALTER DATABASE
ERROR: cannot drop a template database
CREATE DATABASE wartet fünf Sekunden, dann gibt es auf.
template0 ist die unberührte Reserve
(datallowconn = false) – die nimmst du für eine andere
Kodierung oder Locale und für das Einspielen eines pg_dump
(Tag 8). IS_TEMPLATE schützt vor versehentlichem Löschen
und erlaubt Nicht-Superusern mit CREATEDB, die Datenbank
als Vorlage zu nutzen.
Fortschritt speichern? Kostenlos registrieren
Drill 2: Schemas und search_path
SET ROLE wechselt die Identität – ein Superuser darf
jede Rolle annehmen. So legt die Owner-Rolle ihre Objekte selbst an:
psql -d tag02 <<'EOF'
SET ROLE tag02_owner;
SHOW search_path;
CREATE SCHEMA app;
CREATE TABLE app.einstellungen (schluessel text PRIMARY KEY, wert text);
INSERT INTO app.einstellungen VALUES ('version', '2');
SELECT wert FROM einstellungen;
EOFErwartet:
SET
search_path
-----------------
"$user", public
(1 row)
CREATE SCHEMA
CREATE TABLE
INSERT 0 1
ERROR: permission denied for table einstellungen
Als Datenbank-Owner darf tag02_owner Schemas anlegen –
aber nicht die Tabelle aus der Vorlage lesen, die gehört ja
ubuntu. Besitz übertragen, dann den Suchpfad umstellen:
psql -d tag02 <<'EOF'
ALTER TABLE public.einstellungen OWNER TO tag02_owner;
SET ROLE tag02_owner;
SELECT wert FROM einstellungen;
SET search_path = app, public;
SELECT wert FROM einstellungen;
\dt *.einstellungen
EOFErwartet (gekürzt):
wert
------
1
SET
wert
------
2
Schema | Name | Type | Owner
--------+---------------+-------+-------------
app | einstellungen | table | tag02_owner
public | einstellungen | table | tag02_owner
Unqualifizierte Namen sucht PostgreSQL der Reihe nach im
search_path; der erste Treffer gewinnt, neue Objekte landen
im ersten existierenden Schema. "$user" steht für ein
Schema mit dem Namen der Rolle. Den Pfad für alle Anmeldungen an dieser
Datenbank festlegen:
psql -d tag02 -c 'ALTER DATABASE tag02 SET search_path = app, public' -c '\drds'Erwartet:
ALTER DATABASE
List of settings
Role | Database | Settings
------+----------+-------------------------
| tag02 | search_path=app, public
(1 row)
Das wirkt ab der nächsten Anmeldung.
ALTER ROLE … IN DATABASE … SET geht pro Rolle – aber nur
bei echter Anmeldung, nicht bei SET ROLE.
Fortschritt speichern? Kostenlos registrieren
Drill 3: Rollen, Gruppen und INHERIT
Rechte vergibst du an Gruppenrollen, Menschen und Anwendungen werden Mitglied:
psql -d tag02 <<'EOF'
CREATE ROLE tag02_lesen NOLOGIN;
CREATE ROLE tag02_schreiben NOLOGIN IN ROLE tag02_lesen;
CREATE ROLE tag02_alice LOGIN PASSWORD 'lab-alice-123' IN ROLE tag02_lesen;
CREATE ROLE tag02_bob LOGIN NOINHERIT PASSWORD 'lab-bob-123' IN ROLE tag02_schreiben;
\du tag02*
\drg tag02*
EOFErwartet (gekürzt):
Role name | Attributes
-----------------+----------------
tag02_alice |
tag02_bob | No inheritance
tag02_lesen | Cannot login
tag02_owner | Cannot login
tag02_schreiben | Cannot login
Role name | Member of | Options | Grantor
-----------------+-----------------+--------------+----------
tag02_alice | tag02_lesen | INHERIT, SET | postgres
tag02_bob | tag02_schreiben | SET | postgres
tag02_schreiben | tag02_lesen | INHERIT, SET | postgres
Die Passwörter sind Lab-Werte. \drg (seit PostgreSQL 16)
zeigt jede Mitgliedschaft mit ihren Optionen: INHERIT – die
Rechte der Gruppe wirken automatisch; SET – die Rolle darf
per SET ROLE zur Gruppe werden. tag02_bob erbt
nichts, er muss die Gruppe bewusst annehmen. Pro Mitgliedschaft geht das
auch einzeln:
GRANT tag02_lesen TO x WITH INHERIT FALSE, SET TRUE.
Fortschritt speichern? Kostenlos registrieren
Drill 4: GRANT auf Schema, Tabelle, Sequenz
Jetzt meldet sich tag02_alice wirklich an – über TCP auf
127.0.0.1, dort gilt scram-sha-256:
export PGPASSWORD=lab-alice-123
A="host=127.0.0.1 dbname=tag02 user=tag02_alice"
psql "$A" -c 'SELECT * FROM app.einstellungen'
psql -d tag02 -c 'GRANT USAGE ON SCHEMA app TO tag02_lesen'
psql "$A" -c 'SELECT * FROM app.einstellungen'
psql -d tag02 -c 'GRANT SELECT ON ALL TABLES IN SCHEMA app TO tag02_lesen'
psql "$A" -c 'SHOW search_path' -c 'SELECT wert FROM einstellungen'
unset PGPASSWORDErwartet (gekürzt):
ERROR: permission denied for schema app
LINE 1: SELECT * FROM app.einstellungen
^
GRANT
ERROR: permission denied for table einstellungen
GRANT
search_path
-------------
app, public
wert
------
2
Ebene für Ebene: erst das Schema, dann die Tabelle. Der
search_path aus Drill 2 greift, ohne dass
tag02_alice etwas tun muss. Nun schreibt
tag02_bob in eine Tabelle mit
serial-Spalte:
psql -d tag02 <<'EOF'
SET ROLE tag02_owner;
CREATE TABLE app.protokoll (id serial PRIMARY KEY, eintrag text NOT NULL);
GRANT INSERT ON app.protokoll TO tag02_schreiben;
EOF
export PGPASSWORD=lab-bob-123
B="host=127.0.0.1 dbname=tag02 user=tag02_bob"
psql "$B" -c "INSERT INTO protokoll (eintrag) VALUES ('bob war da')"
psql "$B" -c 'SET ROLE tag02_schreiben' \
-c "INSERT INTO protokoll (eintrag) VALUES ('bob war da')"Erwartet:
ERROR: relation "protokoll" does not exist
LINE 1: INSERT INTO protokoll (eintrag) VALUES ('bob war da')
^
SET
ERROR: permission denied for sequence protokoll_id_seq
Ohne INHERIT hat tag02_bob nicht einmal
USAGE auf app – Schemas ohne
USAGE überspringt die Namenssuche, deshalb „does not exist“
statt „permission denied“. Nach SET ROLE fehlt noch das
Recht, die Sequenz weiterzuzählen:
psql -d tag02 -c 'GRANT USAGE ON SEQUENCE app.protokoll_id_seq TO tag02_schreiben'
psql "$B" -c 'SET ROLE tag02_schreiben' \
-c "INSERT INTO protokoll (eintrag) VALUES ('bob war da')" \
-c 'SELECT current_user, session_user' -c 'RESET ROLE' -c 'SELECT current_user'
unset PGPASSWORDErwartet (gekürzt):
SET
INSERT 0 1
current_user | session_user
-----------------+--------------
tag02_schreiben | tag02_bob
RESET
current_user
--------------
tag02_bob
session_user bleibt der angemeldete Benutzer,
current_user bestimmt die Rechte. Identity-Spalten
(GENERATED … AS IDENTITY) brauchen kein eigenes
Sequenzrecht – ein Grund mehr, sie statt serial zu
nehmen.
Fortschritt speichern? Kostenlos registrieren
Drill 5: ALTER DEFAULT PRIVILEGES
GRANT … ON ALL TABLES gilt nur für Tabellen, die es
jetzt gibt:
export PGPASSWORD=lab-alice-123
psql -d tag02 -c 'SET ROLE tag02_owner' \
-c 'CREATE TABLE app.kunden (id int PRIMARY KEY, name text)'
psql "$A" -c 'SELECT count(*) FROM kunden'
psql -d tag02 <<'EOF'
ALTER DEFAULT PRIVILEGES FOR ROLE tag02_owner IN SCHEMA app
GRANT SELECT ON TABLES TO tag02_lesen;
ALTER DEFAULT PRIVILEGES FOR ROLE tag02_owner IN SCHEMA app
GRANT INSERT, UPDATE, DELETE ON TABLES TO tag02_schreiben;
ALTER DEFAULT PRIVILEGES FOR ROLE tag02_owner IN SCHEMA app
GRANT USAGE ON SEQUENCES TO tag02_schreiben;
SET ROLE tag02_owner;
CREATE TABLE app.auftraege (id int PRIMARY KEY, kunde int);
\ddp
EOF
psql "$A" -c 'SELECT count(*) FROM auftraege'
psql -d tag02 -c 'CREATE TABLE app.von_ubuntu (x int)'
psql "$A" -c 'SELECT count(*) FROM von_ubuntu'
psql -d tag02 -c 'DROP TABLE app.von_ubuntu'
unset PGPASSWORDErwartet (gekürzt):
ERROR: permission denied for table kunden
ALTER DEFAULT PRIVILEGES
...
Default access privileges
Owner | Schema | Type | Access privileges
-------------+--------+----------+---------------------------------
tag02_owner | app | sequence | tag02_schreiben=U/tag02_owner
tag02_owner | app | table | tag02_lesen=r/tag02_owner +
| | | tag02_schreiben=awd/tag02_owner
count
-------
0
CREATE TABLE
ERROR: permission denied for table von_ubuntu
Default Privileges hängen an der Rolle, die das Objekt
anlegt (FOR ROLE). Legt jemand anderes die
Tabelle an – hier ubuntu –, greifen sie nicht. Deshalb
laufen Migrationen immer als dieselbe Owner-Rolle. Die Kürzel:
r SELECT, a INSERT, w UPDATE,
d DELETE, U USAGE.
Fortschritt speichern? Kostenlos registrieren
Drill 6: Die PUBLIC-Falle
Eine frische Rolle ohne jede Mitgliedschaft:
psql -d tag02 <<'EOF'
CREATE ROLE tag02_gast LOGIN PASSWORD 'lab-gast-123';
SELECT has_database_privilege('tag02_gast', 'tag02', 'CONNECT') AS connect,
has_database_privilege('tag02_gast', 'tag02', 'TEMP') AS temp,
has_schema_privilege('tag02_gast', 'public', 'USAGE') AS usage_public,
has_schema_privilege('tag02_gast', 'public', 'CREATE') AS create_public;
\dn+ public
EOF
export PGPASSWORD=lab-gast-123
G="host=127.0.0.1 dbname=tag02 user=tag02_gast"
psql "$G" -c 'CREATE TABLE public.muell (x int)' -c 'CREATE TEMP TABLE t (x int)'Erwartet (gekürzt):
connect | temp | usage_public | create_public
---------+------+--------------+---------------
t | t | t | f
Name | Owner | Access privileges | ...
--------+-------------------+----------------------------------------+ ...
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| ...
| | =U/pg_database_owner | ...
ERROR: permission denied for schema public
LINE 1: CREATE TABLE public.muell (x int)
^
CREATE TABLE
PUBLIC darf ab Werk jede Datenbank betreten
(CONNECT), temporäre Tabellen anlegen (TEMP)
und alle Funktionen ausführen (EXECUTE). Seit PostgreSQL 15
gehört das Schema public der Rolle
pg_database_owner (also dem Datenbank-Owner), und
PUBLIC hat dort nur noch USAGE
(=U), kein CREATE mehr. Bis 14 durfte jeder
Tabellen in public anlegen. Den Datenbankzugang machst du
selbst dicht:
psql -d tag02 <<'EOF'
REVOKE CONNECT, TEMPORARY ON DATABASE tag02 FROM PUBLIC;
GRANT CONNECT ON DATABASE tag02 TO tag02_lesen, tag02_owner;
EOF
psql "$G" -c 'SELECT 1'
PGPASSWORD=lab-alice-123 psql "$A" -Atc 'SELECT current_user'
PGPASSWORD=lab-bob-123 psql "$B" -Atc 'SELECT current_user'Erwartet:
REVOKE
GRANT
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:
permission denied for database "tag02"
DETAIL: User does not have CONNECT privilege.
tag02_alice
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:
permission denied for database "tag02"
DETAIL: User does not have CONNECT privilege.
NOINHERIT gilt auch für CONNECT:
tag02_bob erbt es nicht von seiner Gruppe und braucht es
direkt.
psql -d tag02 -c 'GRANT CONNECT ON DATABASE tag02 TO tag02_bob' \
-Atc "SELECT datacl FROM pg_database WHERE datname = 'tag02'"
PGPASSWORD=lab-bob-123 psql "$B" -Atc 'SELECT current_user'Erwartet:
GRANT
{tag02_owner=CTc/tag02_owner,tag02_lesen=c/tag02_owner,tag02_bob=c/tag02_owner}
tag02_bob
c ist CONNECT, C CREATE, T
TEMPORARY. Kein Eintrag für PUBLIC (=c/…)
mehr.
Fortschritt speichern? Kostenlos registrieren
Drill 7: Vordefinierte Rollen und das notes-api-Problem
PostgreSQL bringt Rollen für typische Aufgaben mit. Statt Rechte auf
jedes Schema zu verteilen, gibt pg_read_all_data Leserechte
auf alles, pg_monitor Einblick in Statistiken:
psql -Atc "SELECT rolname FROM pg_roles WHERE rolname LIKE 'pg\_%' ORDER BY 1" \
| paste -sd' ' | fold -s -w 80
psql -d tag02 -c 'GRANT CONNECT ON DATABASE tag02 TO tag02_gast'
psql "$G" -c 'SELECT count(*) FROM app.kunden'
psql -d tag02 -c 'SELECT pg_sleep(3)' >/dev/null &
sleep 1
psql "$G" -Atc "SELECT usename, query FROM pg_stat_activity WHERE usename = 'ubuntu'"
wait
psql -d tag02 -c 'GRANT pg_read_all_data, pg_monitor TO tag02_gast'
psql "$G" -c 'SELECT count(*) FROM app.kunden' \
-c "INSERT INTO app.kunden VALUES (1, 'x')"
psql -d tag02 -c 'SELECT pg_sleep(3)' >/dev/null &
sleep 1
psql "$G" -Atc "SELECT usename, query FROM pg_stat_activity WHERE usename = 'ubuntu'"
wait
unset PGPASSWORDErwartet (gekürzt):
pg_checkpoint pg_create_subscription pg_database_owner
pg_execute_server_program pg_maintain pg_monitor pg_read_all_data
pg_read_all_settings pg_read_all_stats pg_read_server_files
pg_signal_autovacuum_worker pg_signal_backend pg_stat_scan_tables
pg_use_reserved_connections pg_write_all_data pg_write_server_files
GRANT
ERROR: permission denied for schema app
ubuntu|<insufficient privilege>
GRANT ROLE
count
-------
0
ERROR: permission denied for table kunden
ubuntu|SELECT pg_sleep(3)
pg_read_all_data liest alles, schreibt nichts (dafür
gibt es pg_write_all_data). Ohne pg_monitor
sieht eine Rolle in pg_stat_activity nur ihre eigenen
Abfragen – ideal für einen Monitoring-Benutzer wie den
postgres_exporter in Tag 7. pg_maintain (seit
17) erlaubt VACUUM, ANALYZE,
REINDEX ohne Superuser.
Ein Problem, das dir mit notes-api begegnen wird:
notes-api migrate scheitert als notes_app. Auf
pg2 dieselbe Anweisung, die die Anwendung schickt:
psql service=notes -c 'CREATE TABLE IF NOT EXISTS notes (id uuid PRIMARY KEY,
text text NOT NULL, created_at timestamptz NOT NULL DEFAULT now())'Erwartet:
ERROR: permission denied for schema public
LINE 1: CREATE TABLE IF NOT EXISTS notes (id uuid PRIMARY KEY,
^
Die Tabelle existiert längst, trotzdem prüft PostgreSQL zuerst das
CREATE-Recht auf dem Schema. Auf pg1 das
Recht geben, auf pg2 wiederholen:
psql -d notes -c 'GRANT CREATE ON SCHEMA public TO notes_app'psql service=notes -c 'CREATE TABLE IF NOT EXISTS notes (id uuid PRIMARY KEY,
text text NOT NULL, created_at timestamptz NOT NULL DEFAULT now())'Erwartet:
NOTICE: relation "notes" already exists, skipping
CREATE TABLE
Das ist die schmalste Lösung für eine Anwendung, die ihre Tabellen selbst anlegt. Sauberer ist eine eigene Migrationsrolle als Owner von Schema und Tabellen, und die Anwendung läuft mit einer reinen Lese-/Schreibrolle (Default Privileges aus Drill 5). Teil 2 setzt das in CloudNativePG um.
Fortschritt speichern? Kostenlos registrieren
Drill 8: Row Level Security und Rechte nachweisen
RLS filtert Zeilen pro Rolle – zusätzlich zu den Tabellenrechten:
psql -d tag02 <<'EOF'
SET ROLE tag02_owner;
CREATE TABLE app.notizen (
id serial PRIMARY KEY,
besitzer name NOT NULL DEFAULT current_user,
text text NOT NULL);
INSERT INTO app.notizen (besitzer, text) VALUES
('tag02_alice', 'Notiz von Alice'), ('tag02_bob', 'Notiz von Bob');
ALTER TABLE app.notizen ENABLE ROW LEVEL SECURITY;
CREATE POLICY nur_eigene ON app.notizen USING (besitzer = current_user);
SELECT count(*) FROM app.notizen;
EOF
PGPASSWORD=lab-alice-123 psql "$A" -c 'SELECT besitzer, text FROM notizen'
PGPASSWORD=lab-gast-123 psql "$G" -Atc 'SELECT count(*) FROM app.notizen'Erwartet (gekürzt):
count
-------
2
besitzer | text
-------------+-----------------
tag02_alice | Notiz von Alice
(1 row)
0
Der Owner sieht alles (RLS gilt für ihn erst mit
FORCE ROW LEVEL SECURITY), tag02_alice nur
ihre Zeile – das SELECT-Recht kam über die Default
Privileges. Auch pg_read_all_data umgeht RLS nicht; das
kann nur das Attribut BYPASSRLS. Nachweis der Rechte:
psql -d tag02 -c '\dp app.notizen'
psql -d tag02 -c '\d app.notizen' | sed -n '/Policies/,$p'
psql -d tag02 -c "SELECT r.rolname,
has_table_privilege(r.rolname, 'app.notizen', 'SELECT') AS lesen,
has_table_privilege(r.rolname, 'app.notizen', 'INSERT') AS schreiben
FROM pg_roles r WHERE r.rolname LIKE 'tag02\_%' ORDER BY 1"Erwartet (gekürzt):
Schema | Name | Type | Access privileges | Column privileges ...
--------+---------+-------+----------------------------------+------------------ ...
app | notizen | table | tag02_owner=arwdDxtm/tag02_owner+| ...
| | | tag02_lesen=r/tag02_owner +| ...
| | | tag02_schreiben=awd/tag02_owner | ...
Policies:
POLICY "nur_eigene"
USING ((besitzer = CURRENT_USER))
rolname | lesen | schreiben
-----------------+-------+-----------
tag02_alice | t | f
tag02_bob | f | f
tag02_gast | t | f
tag02_lesen | t | f
tag02_owner | t | t
tag02_schreiben | t | t
has_table_privilege beantwortet „darf Rolle X das?“
inklusive geerbter Rechte – tag02_bob bekommt
f, weil er ohne SET ROLE nichts erbt. Beim
Owner steht D für TRUNCATE, x REFERENCES,
t TRIGGER und m MAINTAIN (seit 17).
Fortschritt speichern? Kostenlos registrieren
Übung: Speed-Drills
- Prüfe per SQL, ob
tag02_aliceinapp.kundenlesen und schreiben darf. Zeitziel: 30 s - Zeige alle Default Privileges der Datenbank
tag02. Zeitziel: 10 s - Nimm
tag02_gastdie Rollepg_monitorwieder weg. Zeitziel: 15 s - Zeige, wem das Schema
publicintag02gehört. Zeitziel: 10 s
Lösung
psql -d tag02 -c "SELECT has_table_privilege('tag02_alice', 'app.kunden', 'SELECT'),
has_table_privilege('tag02_alice', 'app.kunden', 'INSERT')"
psql -d tag02 -c '\ddp'
psql -d tag02 -c 'REVOKE pg_monitor FROM tag02_gast'
psql -d tag02 -c '\dn public'Erwartet (gekürzt):
has_table_privilege | has_table_privilege
---------------------+---------------------
f | f
...
REVOKE ROLE
Name | Owner
--------+-------------------
public | pg_database_owner
app.kunden entstand nach dem
GRANT … ON ALL TABLES und vor den Default Privileges –
daher kein Leserecht für tag02_alice.
Fortschritt speichern? Kostenlos registrieren
Kontrollpunkt
psql -d tag02 -Atc "SELECT pg_get_userbyid(datdba) FROM pg_database
WHERE datname = 'tag02'"Erwartet: tag02_owner
psql -d tag02 -Atc "SELECT has_database_privilege('tag02_gast', 'tag02', 'TEMP')"Erwartet: f – PUBLIC hat
kein TEMPORARY mehr.
PGPASSWORD=lab-alice-123 psql "host=127.0.0.1 dbname=tag02 user=tag02_alice" \
-Atc 'SELECT count(*) FROM notizen'Erwartet: 1 – RLS zeigt nur die eigene
Notiz.
psql -d tag02 -Atc "SELECT count(*) FROM pg_default_acl"Erwartet: 2 (Tabellen und Sequenzen für
tag02_owner in app).
psql -d notes -Atc "SELECT has_schema_privilege('notes_app', 'public', 'CREATE')"Erwartet: t – bis zum Aufräumen.
Challenge
Entferne die Gruppenrolle tag02_schreiben.
DROP ROLE allein scheitert – finde heraus warum und räume
so auf, dass danach keine Spur der Rolle bleibt.
Hinweis: Eine Rolle mit Rechten oder Objekten lässt sich erst löschen, wenn diese weg sind; es gibt einen Befehl, der genau das pro Datenbank erledigt.
Lösung
psql -d tag02 -c 'DROP ROLE tag02_schreiben'
psql -d tag02 -c 'DROP OWNED BY tag02_schreiben' -c 'DROP ROLE tag02_schreiben' \
-c '\ddp'Erwartet:
ERROR: role "tag02_schreiben" cannot be dropped because some objects depend on it
DETAIL: privileges for sequence protokoll_id_seq
privileges for table protokoll
privileges for default privileges on new relations belonging to role tag02_owner in
schema app
privileges for default privileges on new sequences belonging to role tag02_owner in
schema app
privileges for table auftraege
privileges for sequence notizen_id_seq
privileges for table notizen
DROP OWNED
DROP ROLE
Default access privileges
Owner | Schema | Type | Access privileges
-------------+--------+-------+---------------------------
tag02_owner | app | table | tag02_lesen=r/tag02_owner
DROP OWNED BY löscht in der aktuellen Datenbank alle
Objekte der Rolle und entzieht alle ihre Rechte, auch Default
Privileges. Besitzt sie Objekte, die bleiben sollen, vorher
REASSIGN OWNED BY rolle TO andere_rolle. Hat die Rolle
Rechte in mehreren Datenbanken, musst du das in jeder einzelnen tun.
Fortschritt speichern? Kostenlos registrieren
Aufräumen
Das Schema-Recht für notes_app wieder zurücknehmen – die
folgenden Tage gehen vom Zustand aus Tag 0 aus. Dann Datenbank, Vorlage
und Rollen löschen:
psql -d notes -c 'REVOKE CREATE ON SCHEMA public FROM notes_app'
psql -c 'DROP DATABASE tag02'
psql -c 'ALTER DATABASE tag02_vorlage IS_TEMPLATE false'
psql -c 'DROP DATABASE tag02_vorlage'
psql -c 'DROP ROLE tag02_alice, tag02_bob, tag02_gast, tag02_lesen, tag02_owner'Erwartet: REVOKE, zweimal
DROP DATABASE, ALTER DATABASE,
DROP ROLE.
Interview-Notiz
- „Ein neuer Benutzer bekommt
GRANT SELECT ON ALL TABLES, sieht aber neue Tabellen nicht – warum?“ – DasGRANTwirkt nur auf bestehende Tabellen; für künftigeALTER DEFAULT PRIVILEGES FOR ROLE <anlegende Rolle>. - „Was hat sich in PostgreSQL 15 an
publicgeändert?“ –PUBLIChat keinCREATEmehr auf dem Schemapublic, Owner istpg_database_owner. - „Wie baust du einen Read-only-Benutzer?“ – Gruppenrolle mit
CONNECT,USAGE,SELECTplus Default Privileges – oder kurzpg_read_all_data, das aber für alle Datenbanken gilt.
Enterprise-Notiz
- Drei Ebenen Rollen: Owner (Migrationen,
NOLOGIN), Gruppen (_ro,_rw), Logins für Menschen und Dienste – Menschen nie direkt mit Rechten auf Tabellen. REVOKE ALL ON DATABASE … FROM PUBLICgehört in die Grundkonfiguration jeder neuen Datenbank, auchREVOKE EXECUTE ON FUNCTION … FROM PUBLICbeiSECURITY DEFINER-Funktionen.- Rechte als Code: Rollen und Grants in Migrationen oder Werkzeugen
wie Terraform, in CloudNativePG deklarativ (
managed.roles, Tag 17). - Regelmäßige Rechte-Reviews mit
has_*_privilegeund\dp; Audit-Logging (z. B. pgaudit) für Änderungen an Rollen. - RLS für Mandantentrennung nur mit Tests: Owner und
BYPASSRLS-Rollen sehen alles, Funktionen mitSECURITY DEFINERkönnen Policies umgehen.