Zum Inhalt springen
Trainingslager IT-Trainingslager

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; LOGIN macht einen Benutzer daraus.
  • Wer ein Objekt anlegt, besitzt es. Alle anderen brauchen Rechte – auf jeder Ebene: Datenbank (CONNECT), Schema (USAGE), Objekt (SELECT …).
  • PUBLIC heiß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;
EOF

Erwartet:

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
EOF

Erwartet (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*
EOF

Erwartet (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 PGPASSWORD

Erwartet (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 PGPASSWORD

Erwartet (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 PGPASSWORD

Erwartet (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 PGPASSWORD

Erwartet (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

  1. Prüfe per SQL, ob tag02_alice in app.kunden lesen und schreiben darf. Zeitziel: 30 s
  2. Zeige alle Default Privileges der Datenbank tag02. Zeitziel: 10 s
  3. Nimm tag02_gast die Rolle pg_monitor wieder weg. Zeitziel: 15 s
  4. Zeige, wem das Schema public in tag02 gehö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?“ – Das GRANT wirkt nur auf bestehende Tabellen; für künftige ALTER DEFAULT PRIVILEGES FOR ROLE <anlegende Rolle>.
  • „Was hat sich in PostgreSQL 15 an public geändert?“ – PUBLIC hat kein CREATE mehr auf dem Schema public, Owner ist pg_database_owner.
  • „Wie baust du einen Read-only-Benutzer?“ – Gruppenrolle mit CONNECT, USAGE, SELECT plus Default Privileges – oder kurz pg_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 PUBLIC gehört in die Grundkonfiguration jeder neuen Datenbank, auch REVOKE EXECUTE ON FUNCTION … FROM PUBLIC bei SECURITY 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_*_privilege und \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 mit SECURITY DEFINER können Policies umgehen.