DB-Rollen und Berechtigungen

Status: umgesetzt (2026-10-26, Migration 008_role_grants.sql). Massgebend sind die Grants in db/db-setup.sql (Abschnitt Roles & Grants). Dieses Dokument listet dieselben Rechte mit Begründung. db/tests/grants.sql prüft eine Datenbank genau gegen diese Liste.

Jeder Dienst verbindet sich mit einer eigenen Login-Rolle. Eine Rolle hat nur die Rechte, die ihr Code braucht: pro Tabelle, und bei UPDATE pro Spalte, wenn der Dienst nur einzelne Felder ändert.

Grundsätze

• Keine Pauschalrechte. Es gibt kein GRANT … ON ALL TABLES und keine DEFAULT PRIVILEGES. Eine neue Tabelle ist für alle Dienste unsichtbar, bis sie eigene Grants bekommt (siehe Änderungen).
• Eine Tabelle hat einen Eigentümer-Dienst. Er legt die Zeilen an. Andere Dienste lesen oder ändern nur einzelne Spalten, die ihnen gehören (z.B. setzt der Writer app_peerreviews.state = 'closed', die AppView verlängert grace_until).
• DELETE nur, wo der Code löscht. Das betrifft nur den Taxonomie-Neuaufbau im Indexer und den Embedding-Cache im Writer. Sonst wird weich gelöscht (deleted = true).
• Register mit Spaltenrechten. Die verschlüsselten Passwörter der Community-Konten (pw_ciphertext, pw_nonce) lesen nur Writer und Registrar.
• Regeln über SECURITY DEFINER-Funktionen. participation_check und peer_review_settings lesen das Register im Namen des Aufrufers. Indexer, AppView und Writer brauchen dafür keinen Lesezugriff auf registry.participants oder registry.clients.
• Owner atvoto ist die DBA-Rolle (POSTGRES_USER des Postgres-Pods). Kein Dienst verwendet sie. Sie spielt Setup und Migrationen ein.

Rollen

Rolle	Dienst	Secret (Variable)	Aufgabe
indexer	Indexer	indexer-secrets (INDEXER_POSTGRES_URL)	Firehose in die app_*-Tabellen projizieren
appview	AppView	appview-secrets (APPVIEW_POSTGRES_URL)	Lese-API, Lesezustand (gesehen, Peer-Review-Check-in)
writer	Writer	writer-secrets (APPVIEW_POSTGRES_URL)	Annahmeschleuse, Peer Review zuteilen/abschliessen, Crosspost, Übersetzungen, Embeddings
registrar	Registrar	registrar-secrets (APPVIEW_POSTGRES_URL)	Admin-API: Teilnehmer, Dossiers, Client-Einstellungen

Alle vier Dienst-Rollen haben CONNECT auf atvoto und USAGE auf public und registry. PUBLIC hat kein CONNECT. Die Rolle calculator ist seit Migration 014 entfernt, die Rolle ozone seit Migration 016.

Übersicht

R = SELECT, I = INSERT, U = UPDATE (ganze Zeile), U(…) = UPDATE nur dieser Spalten, D = DELETE, – = kein Zugriff.

Schema public

Tabelle	indexer	appview	writer	registrar
app_dossier_meta	R	R	R	R I U
indexer_cursors	R I U	–	–	–
app_arguments	R I U	R	R, U(peerreview_status, indexed_at, bsky_post_uri, bsky_post_cid, bsky_post_removed)	R, U(title, body, translations, bsky_post_removed, content_purged_at)
app_acceptance_queue	R I	–	R, U(status, reason, updated_at)	–
app_likes	R I U	R	–	R
app_comments	R I U	R	R	R, U(title, text, content_purged_at)
app_comment_translations	R I U	R	R	–
app_taxonomy_node	R I U D	R	R, U(langs, translations, translation_status)	–
app_taxonomy_membership	R I U D	R	–	–
app_held_contributions	R I U D	–	–	–
app_taxonomy_snapshot	R I	–	–	–
app_peerreviews	R I U	R, U(grace_until)	R, U(state, closed_at, result_uri, result_cid, approvals, rejections)	R
app_peerreview_invitations	R I	R, U(checked_in_at, last_activity_at)	R I	–
app_peerreview_responses	R I	R	R	R
app_content_creations	–	R	R I	–
app_activity_seen	–	R I	–	–
app_profiles	R I U	R	–	R
app_embeddings	–	R	R I U D	–
labels	R	R	–	R I D
labels_active (View)	R	R	–	R

Schema registry

Objekt	indexer	appview	writer	registrar
clients	–	–	–	R, U(participation_policy, peer_review)
participation_policy_log	–	–	–	R I
peer_review_log	–	–	–	R I
participants	–	–	–	R I U
community_accounts	R(did, dossier_rkey)	R(did, dossier_rkey)	R (inkl. pw_*)	R I
v_eligible_participants (View)	–	–	R	–

Sequenzen

Sequenz	Rolle
app_acceptance_queue_id_seq, app_taxonomy_node_id_seq	indexer
app_content_creations_id_seq	writer
registry.participation_policy_log_id_seq, registry.peer_review_log_id_seq	registrar
labels_seq_seq	registrar

Funktionen (EXECUTE)

Funktion	Rollen	Art
registry.participation_check(did, dossier, action)	indexer, appview, writer	SECURITY DEFINER: liest Register und Client-Regeln
registry.peer_review_settings(dossier)	indexer	SECURITY DEFINER: liest clients.peer_review
app_response_gate(argument, reviewer)	writer	SECURITY INVOKER: braucht SELECT auf app_peerreviews, app_arguments, app_peerreview_invitations
app_label_active(subject, labeler)	indexer	SECURITY INVOKER: ist das neueste Label nicht negiert?
app_refresh_hidden(uri, did, labeler)	indexer	SECURITY INVOKER: die Regel für hidden; schreibt nur in Tabellen des Indexers
registry.assurance_rank(text)	alle (PUBLIC)	reine Hilfsfunktion ohne Datenzugriff

PUBLIC darf nur registry.assurance_rank ausführen. Die Trigger-Funktion app_taxonomy_node_reset_translations behält das Standardrecht. Postgres prüft bei Triggern kein EXECUTE.

Begründung pro Rolle

indexer

Projiziert die Records aus der Firehose und ist Eigentümer fast aller app_*-Tabellen. Code: services/indexer/src/db.js.

• R I U auf app_arguments, app_comments, app_likes, app_comment_translations, app_peerreviews, indexer_cursors: Upserts (ON CONFLICT DO UPDATE), Zähler (like_count, comment_count), Soft-Delete, Peer-Review-Übergänge (provisional_closed, Abschluss aus dem Result-Record). Der Bluesky-Poller setzt bsky_reply_count.
• R I auf app_acceptance_queue, app_taxonomy_snapshot, app_peerreview_invitations, app_peerreview_responses: nur einfügen (ON CONFLICT DO NOTHING). Peer-Review-Daten werden nie geändert oder gelöscht.
• R I U D auf app_taxonomy_node, app_taxonomy_membership: Der Snapshot baut den Baum eines Dossiers neu auf und löscht dabei Knoten und Zuordnungen, die nicht mehr vorkommen.
• R I U D auf app_held_contributions: Kommentare und Bewertungen, die die Phase der Vorlage zurückhält; nach dem Nachholen wird die Zeile gelöscht.
• R auf app_dossier_meta: Status-Gate (nur open projiziert Beiträge).
• registry: community_accounts(did, dossier_rkey), um Community-Repos zu erkennen. participation_check für das Gate, peer_review_settings beim Anlegen eines User-Arguments.
• R auf labels, labels_active und EXECUTE auf app_refresh_hidden: Nur der Indexer leitet aus Labels das Flag hidden ab (MODERATION.md, Lesemodell).

appview

Öffentliche Lese-API. Code: services/appview/src/routes/participation/.

• R auf alle Tabellen, die die API ausliefert (Argumente, Kommentare, Bewertungen, Übersetzungen, Profile, Taxonomie, Peer Review), auf app_content_creations (Kontingent, quota.py) und auf app_dossier_meta (Status in permissions.py).
• R I auf app_activity_seen: „gesehen“-Markierungen (activity.py).
• U(checked_in_at, last_activity_at) auf app_peerreview_invitations: Check-in und Aktivität im Peer Review (reviews.py).
• U(grace_until) auf app_peerreviews: verlängert die Nachfrist, solange jemand tippt. Das Spaltenrecht erlaubt auch SELECT … FOR UPDATE auf die Zeile.
• registry: community_accounts(did, dossier_rkey) und participation_check (permissions.py).
• R auf app_embeddings: Duplikat im Peer Review und argument.findSimilar rechnen die Ähnlichkeit per SQL; den Vektor eines Entwurfs liefert der Writer (/internal/embed).
• R auf labels, labels_active: öffentliche Auslieferung (queryLabels). Die AppView hält keinen Signaturschlüssel und kann keine Labels schreiben.
• Kein DELETE.

writer

Interne Schreibseite. Code: services/writer/src/ ohne registrar/.

• R auf app_dossier_meta: Status-Gate. Den Status ändert nur der Registrar.
• R I U D auf app_embeddings: Embeddings der Argumente, ein regenerierbarer Cache (embedding/backfill.py).
• R, U(status, reason, updated_at) auf app_acceptance_queue: Zeilen beanspruchen (FOR UPDATE SKIP LOCKED) und abschliessen (atproto/acceptance.py).
• R I auf app_content_creations: Kontingent-Ledger (acceptance.py).
• R I auf app_peerreview_invitations: Einladungen sofort eintragen, ohne auf den Indexer zu warten (arguments/peer_review_assign.py).
• R, U(state, closed_at, result_uri, result_cid, approvals, rejections) auf app_peerreviews: Nachfrist schliessen, Ergebnis eintragen (arguments/peer_review_finalize.py).
• R, U(peerreview_status, indexed_at, bsky_post_uri, bsky_post_cid, bsky_post_removed) auf app_arguments: Ergebnis des Peer Reviews (peer_review_finalize.py), Crosspost und Abgleich der Spiegelposts (atproto/crosspost.py). Titel, Text und Quelle kann der Writer nicht ändern.
• R, U(langs, translations, translation_status) auf app_taxonomy_node: Übersetzung der Themen (translation/translator.py). Argumente und Kommentare übersetzt er über das PDS, nicht direkt in der DB.
• R auf app_arguments, app_comments, app_comment_translations, app_peerreview_responses: Eingaben für Annahme, Zuteilung, Übersetzung.
• registry: community_accounts ganz (inkl. pw_*, um in Community-Repos zu schreiben), v_eligible_participants, participation_check. app_response_gate für Peer-Review-Antworten.

registrar

Admin-API für Clients. Code: services/writer/src/registrar/.

• R I U auf app_dossier_meta: createDossier, updateDossier (Metadaten und Status).
• R I auf registry.community_accounts: legt das Community-Konto eines Dossiers an, inkl. verschlüsseltem Passwort.
• R I U auf registry.participants: createAccount, revokeParticipant, Prüfstufe ändern.
• R auf registry.clients (Client-Key prüfen, handle_domain), U(participation_policy, peer_review) für die Client-Einstellungen, dazu R I auf die beiden Änderungsprotokolle.
• R I D auf labels, R auf labels_active, dazu die Sequenz: signierte Labels speichern; gelöscht wird nur mit dem Inhalt oder dem Konto (VO § 9 Abs. 1).
• R auf app_arguments, app_comments, app_likes, app_peerreviews, app_peerreview_responses: Daten zum Fall (moderation.getSubject, getThread, listRecent). R auf app_profiles: Anzeigename der Autorin oder des Autors.
• U(title, body, translations, bsky_post_removed, content_purged_at) auf app_arguments und U(title, text, content_purged_at) auf app_comments: nur für die rechtlich verlangte Löschung (purgeContent).
• Kein Zugriff auf die übrigen app_*-Tabellen.

Was damit nicht mehr geht

Beispiele, die vor Migration 008 möglich waren:

Rolle	Versuch	Ergebnis
writer	UPDATE app_dossier_meta SET status = …	verweigert
writer	UPDATE app_arguments SET title = …	verweigert
writer	UPDATE app_peerreviews SET quorum = …	verweigert
writer	SELECT … FROM registry.participants	verweigert
appview	DELETE FROM app_comments …	verweigert
appview	UPDATE app_peerreviews SET state = 'closed'	verweigert
appview	SELECT pw_ciphertext FROM registry.community_accounts	verweigert
indexer	UPDATE app_dossier_meta …, DELETE FROM app_arguments …	verweigert

Entfernt wurden ausserdem Rechte, die kein Code nutzte: AppView auf app_embeddings, Indexer auf registry.v_eligible_participants, die Spalte handle von community_accounts für Indexer und AppView.

Grenzen

• Der Indexer darf die projizierten Tabellen ganz ändern, also z.B. auch app_arguments.peerreview_status. Er bildet die Firehose ab, und die Firehose ist die Quelle dieser Daten. Wer den Indexer kontrolliert, kann die Projektion verfälschen, aber nicht die Records auf dem PDS. Ein Neuaufbau des Index stellt die Projektion wieder her.
• Der Registrar darf alle Spalten von app_dossier_meta ändern, auch client_id. Dass ein Client nur eigene Dossiers ändert, prüft der Code.
• Postgres kennt keine Zeilenrechte ohne RLS. Welche Zeilen ein Dienst ändert, begrenzt weiterhin der Code (z.B. AppView: nur die eigene Einladung).

Änderungen

Braucht ein Dienst ein neues Recht oder gibt es eine neue Tabelle:

1. Grant in db/db-setup.sql im Abschnitt Roles & Grants ergänzen (frische Installationen).
2. Dieselben Grants in eine neue, idempotente Migration (Konvention). Eine Migration, die eine Tabelle anlegt, vergibt die Grants gleich mit.
3. Dieses Dokument nachführen (Übersicht und Begründung).
4. Die Soll-Liste in db/tests/grants.sql nachführen und den Test laufen lassen.

ALL TABLES und DEFAULT PRIVILEGES nicht verwenden. Der Test schlägt fehl, wenn eine Dienst-Rolle Default-Privileges hat.

Prüfen

# Ist-Rechte gegen die Soll-Liste (als Owner, hinterlässt nichts)
psql -h localhost -p 5497 -U atvoto -d atvoto -v ON_ERROR_STOP=1 -f db/tests/grants.sql
# → NOTICE: grants: ok, sonst Liste "missing" / "extra"

-- Rechte einer Tabelle von Hand ansehen
\dp app_peerreviews
-- Spaltenrechte einer Rolle
SELECT table_schema, table_name, column_name, privilege_type
FROM information_schema.column_privileges WHERE grantee = 'writer';