Erweiterte PostgreSQL-Rollenbasierte Zugriffskontrolle: Zeilen-Sicherheit und Richtlinienverwaltung

PostgreSQL-Zeilen-Sicherheit (RLS) ermöglicht Ihrer Datenbank, Zeilen basierend auf der aktuellen Rolle und dem Sitzungskontext zu filtern. Wenn Ihre Anwendung mehrere Mandanten bedient oder Admin-, Manager- und normale Benutzerzugriffe mischt, kann RLS verhindern, dass ein vergessenes WHERE tenant_id = ... zu einem Datenleck wird.

RLS ersetzt keine Anwendungsautorisierung. Es bietet ein Sicherheitsnetz auf Datenbankebene für die sensibelste Grenze: welche Zeilen eine Verbindung lesen oder schreiben kann.

Warum Zeilen-Sicherheit?

Probleme des traditionellen Ansatzes

Anwendungsschicht-Filterung:

# Jede Abfrage muss sich merken zu filtern
query = "SELECT * FROM documents WHERE tenant_id = %s"
ergebnisse = db.execute(query, [aktueller_benutzer_mandanten_id])

Probleme:

Leicht vergessene Filter (Sicherheitslücken)
Code-Duplizierung in der gesamten Anwendung
Kein Schutz für direkten Datenbankzugriff
Komplexe Prüfanforderungen

Vorteile der Zeilen-Sicherheit

Automatische Durchsetzung: Richtlinien werden transparent angewendet
Zentralisierte Regeln: Sicherheitslogik an einem Ort
Mandantenisolierung: Perfekt für SaaS-Anwendungen
Prüfkonformität: Integrierte Sicherheitsgarantien
Leistung: Effiziente Abfrageplanung

Grundlegende RLS-Einrichtung

Schritt 1: RLS auf Tabelle aktivieren

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    owner_id INTEGER NOT NULL,
    department VARCHAR(50),
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Zeilen-Sicherheit aktivieren
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Schritt 2: Rollen erstellen

-- Verschiedene Benutzerrollen erstellen
CREATE ROLE regular_user;
CREATE ROLE department_manager;
CREATE ROLE admin;

-- Tabellenzugriff gewähren
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO regular_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO department_manager;
GRANT ALL ON documents TO admin;

Schritt 3: Richtlinien erstellen

-- Richtlinie: Benutzer können ihre eigenen Dokumente sehen
CREATE POLICY user_own_documents ON documents
    FOR SELECT
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- Richtlinie: Benutzer können Dokumente einfügen, die ihnen gehören
CREATE POLICY user_insert_own ON documents
    FOR INSERT
    TO regular_user
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- Richtlinie: Benutzer können ihre eigenen Dokumente aktualisieren
CREATE POLICY user_update_own ON documents
    FOR UPDATE
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER)
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

Schritt 4: Sitzungsvariablen setzen

Im Anwendungscode:

import psycopg2

# Als Rolle verbinden
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="regular_user",
    password="password"
)

# Benutzerkontext setzen
cursor = conn.cursor()
cursor.execute("SELECT set_config('app.user_id', %s, false)", [str(aktueller_benutzer_id)])

# Abfragen werden jetzt automatisch gefiltert
cursor.execute("SELECT * FROM documents")
# Gibt nur Dokumente zurück, bei denen owner_id = aktueller_benutzer_id

Multi-Mandanten-SaaS-Anwendung

Vollständige Multi-Mandanten-Einrichtung

-- Mandantentabelle erstellen
CREATE TABLE tenants (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Benutzertabelle erstellen
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    tenant_id INTEGER REFERENCES tenants(id),
    role VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Anwendungsdatentabelle erstellen
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    customer_name VARCHAR(100),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- RLS aktivieren
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Richtlinie: Benutzer sehen nur Bestellungen ihres Mandanten
CREATE POLICY tenant_isolation ON orders
    FOR ALL
    TO public
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::INTEGER);

Anwendungsintegration

Django-Beispiel:

from django.db import connection

class TenantMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        if request.user.is_authenticated:
            with connection.cursor() as cursor:
                cursor.execute(
                    "SELECT set_config('app.tenant_id', %s, false)",
                    [str(request.user.tenant_id)]
                )
        
        response = self.get_response(request)
        return response

Node.js-Beispiel:

const { Pool } = require('pg');
const pool = new Pool();

async function setTenantContext(tenantId) {
    const client = await pool.connect();
    try {
        await client.query('SELECT set_config($1, $2, false)', ['app.tenant_id', String(tenantId)]);
        return client;
    } catch (err) {
        client.release();
        throw err;
    }
}

// Verwendung
app.use(async (req, res, next) => {
    if (req.user) {
        req.dbClient = await setTenantContext(req.user.tenantId);
    }
    next();
});

Erweiterte Richtlinienmuster

1. Hierarchische Zugriffskontrolle

-- Benutzer können Dokumente ihrer Abteilung und öffentliche sehen
CREATE POLICY department_access ON documents
    FOR SELECT
    TO regular_user
    USING (
        department = current_setting('app.user_department', true)
        OR is_public = true
        OR owner_id = current_setting('app.user_id')::INTEGER
    );

2. Zeitbasierter Zugriff

CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    content TEXT,
    valid_from TIMESTAMP NOT NULL,
    valid_until TIMESTAMP NOT NULL
);

ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

-- Richtlinie: Benutzer können nur aktive Abonnements sehen
CREATE POLICY active_subscriptions ON subscriptions
    FOR SELECT
    TO public
    USING (
        user_id = current_setting('app.user_id')::INTEGER
        AND NOW() BETWEEN valid_from AND valid_until
    );

3. Rollenbasierter Zugriff mit komplexer Logik

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    owner_id INTEGER,
    team_id INTEGER,
    visibility VARCHAR(20) -- 'private', 'team', 'public'
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Komplexe Sichtbarkeitsrichtlinie
CREATE POLICY project_visibility ON projects
    FOR SELECT
    TO public
    USING (
        CASE current_setting('app.user_role', true)
            WHEN 'admin' THEN true
            WHEN 'manager' THEN (
                team_id = current_setting('app.user_team_id')::INTEGER
                OR visibility = 'public'
            )
            ELSE (
                owner_id = current_setting('app.user_id')::INTEGER
                OR visibility = 'public'
            )
        END
    );

4. Gemeinsame Zugriffslisten

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    owner_id INTEGER NOT NULL
);

CREATE TABLE document_shares (
    document_id INTEGER REFERENCES documents(id),
    shared_with_user_id INTEGER,
    permission VARCHAR(20), -- 'read', 'write'
    PRIMARY KEY (document_id, shared_with_user_id)
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Richtlinie: Benutzer können eigene oder geteilte Dokumente sehen
CREATE POLICY document_access ON documents
    FOR SELECT
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
        )
    );

-- Richtlinie: Benutzer können eigene Dokumente oder solche mit Schreibberechtigung ändern
CREATE POLICY document_modify ON documents
    FOR UPDATE
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
              AND permission = 'write'
        )
    );

Administrativer Zugriff und RLS

Tabellenbesitzer und Superuser können RLS umgehen, es sei denn, Sie erzwingen es. Behandeln Sie administrative Pfade als separate Designentscheidung, nicht als nachträglichen Gedanken.

Option 1: BYPASSRLS-Rollenattribut

CREATE ROLE admin_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE admin_user BYPASSRLS;

Verwenden Sie dies sparsam. BYPASSRLS ist mächtig und sollte nicht normalen Anwendungsrollen gewährt werden.

Option 2: Permissive Richtlinien für Admins

-- Permissive Richtlinie für Admin-Rolle erstellen
CREATE POLICY admin_all_access ON documents
    FOR ALL
    TO admin
    USING (true)
    WITH CHECK (true);

Option 3: RLS für Tabellenbesitzer erzwingen

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY zwingt auch Tabellenbesitzer, Richtlinien zu befolgen. Das ist normalerweise sicherer für anwendungsbesessene Tabellen. Verwenden Sie für Wartungsarbeiten eine separate kontrollierte Rolle, anstatt RLS in einem Ad-hoc-Skript zu deaktivieren.

Richtlinientypen: USING vs WITH CHECK

USING-Klausel

Steuert, welche vorhandenen Zeilen sichtbar/änderbar sind:

CREATE POLICY select_own ON documents
    FOR SELECT
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- Benutzer kann nur Zeilen SELECTieren, bei denen owner_id übereinstimmt

WITH CHECK-Klausel

Steuert, welche neuen/aktualisierten Zeilen erlaubt sind:

CREATE POLICY insert_own ON documents
    FOR INSERT
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- Benutzer kann nur Zeilen INSERTieren, bei denen owner_id übereinstimmt

Kombinierte Richtlinien

CREATE POLICY update_own ON documents
    FOR UPDATE
    USING (owner_id = current_setting('app.user_id')::INTEGER)
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

Leistungsüberlegungen

1. Richtlinien-Spalten indizieren

-- Indizes für in Richtlinien verwendete Spalten erstellen
CREATE INDEX idx_documents_owner ON documents(owner_id);
CREATE INDEX idx_documents_tenant ON documents(tenant_id);
CREATE INDEX idx_documents_department ON documents(department);

2. Abfragepläne analysieren

EXPLAIN ANALYZE
SELECT * FROM documents
WHERE title LIKE '%report%';

-- Prüfen, ob der Richtlinienfilter effizient angewendet wird

3. Vorsicht bei Unterabfragen in Richtlinien

Unterabfragen sind manchmal das richtige Modell für gemeinsamen Zugriff, benötigen aber Indizes und Tests. Eine Richtlinie, die für jede Kandidatenzeile eine andere Tabelle abfragt, kann teuer werden.

Riskant ohne Indizes:

CREATE POLICY slow_policy ON documents
    USING (
        id IN (
            SELECT document_id
            FROM shares
            WHERE user_id = current_setting('app.user_id')::INTEGER
        )
    );

Oft klarer mit unterstützenden Indizes:

CREATE POLICY faster_policy ON documents
    USING (
        EXISTS (
            SELECT 1 FROM shares 
            WHERE document_id = documents.id 
              AND user_id = current_setting('app.user_id')::INTEGER
        )
    );

Erstellen Sie Indizes wie shares(user_id, document_id) und überprüfen Sie den Plan mit EXPLAIN (ANALYZE, BUFFERS).

Überwachung und Prüfung

Aktive Richtlinien anzeigen

-- Alle RLS-Richtlinien auflisten
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
ORDER BY tablename, policyname;

Richtlinienänderungen prüfen

Verfolgen Sie Richtlinien-DDL in Migrationen und Datenbank-Prüfprotokollen. Wenn Ihre Umgebung Ereignis-Trigger verwendet, testen Sie diese sorgfältig gegen Ihre PostgreSQL-Version und schließen Sie CREATE POLICY, ALTER POLICY und DROP POLICY-Ereignisse ein. Verlassen Sie sich nicht auf ein ungetestetes Trigger-Snippet für Compliance-Nachweise.

Richtlinienwirksamkeit testen

-- Testfunktion erstellen
CREATE OR REPLACE FUNCTION test_user_access(
    test_user_id INTEGER,
    test_tenant_id INTEGER
)
RETURNS TABLE(document_id INTEGER, title VARCHAR) AS $$
BEGIN
    -- Sitzungsvariablen setzen
    PERFORM set_config('app.user_id', test_user_id::text, false);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, false);
    
    -- Zugängliche Dokumente zurückgeben
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;

-- Zugriff testen
SELECT * FROM test_user_access(123, 5);

Fehlerbehebung

Problem 1: Richtlinien werden nicht angewendet

Prüfen, ob RLS aktiviert ist:

SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' AND tablename = 'documents';

Aktivieren, falls deaktiviert:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Problem 2: Sitzungsvariablen nicht gesetzt

Aktuelle Einstellungen debuggen:

SHOW app.user_id;
SHOW app.tenant_id;

-- Oder current_setting verwenden
SELECT current_setting('app.user_id', true);

Problem 3: Leistungsverschlechterung

Langsame Richtlinien identifizieren:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM documents WHERE title = 'test';

-- Nach "Filter"-Operationen mit Richtlinienbedingungen suchen

Lösung: Richtlinien vereinfachen oder Indizes hinzufügen

Problem 4: Unerwarteter Zugriff verweigert

Als bestimmten Benutzer testen:

SET ROLE regular_user;
SELECT set_config('app.user_id', '123', false);

SELECT * FROM documents;
-- Zeigt, was dieser Benutzer tatsächlich sehen kann

RESET ROLE;

Best Practices

Sitzungsvariablen immer setzen: Stellen Sie sicher, dass current_setting()-Werte befüllt sind
Richtlinien-Spalten indizieren: Erstellen Sie Indizes für alle in USING/WITH CHECK verwendeten Spalten
Gründlich testen: Überprüfen Sie Richtlinien mit verschiedenen Rollen und Szenarien
Richtlinien einfach halten: Komplexe Logik beeinträchtigt die Leistung
EXPLAIN verwenden: Analysieren Sie Abfragepläne mit aktiviertem RLS
Richtlinien dokumentieren: Kommentieren Sie die Richtlinienlogik für die Wartbarkeit
Aufgaben trennen: Verwenden Sie verschiedene Richtlinien für SELECT, INSERT, UPDATE, DELETE
Leistung überwachen: Richten Sie Warnungen für langsame Abfragen ein

Fazit

Beginnen Sie mit einer einfachen Richtlinie: Mandantenisolierung für Ihre risikoreichsten Tabellen. Setzen Sie den Sitzungskontext mit set_config, indizieren Sie jede von Richtlinien verwendete Spalte und testen Sie Lese- und Schreibvorgänge als echte Anwendungsrollen.

Sobald die grundlegende Grenze funktioniert, fügen Sie Eigentümer-, Manager- oder gemeinsame Zugriffsrichtlinien nur dort hinzu, wo das Produkt sie benötigt. Kleine, getestete Richtlinien sind einfacher zu durchschauen als eine clevere Regel, die versucht, Ihr gesamtes Berechtigungsmodell zu kodieren.