DevOps Wissensdatenbank
DevOps Wissensdatenbank

Erweiterte rollenbasierte Zugriffskontrolle in PostgreSQL: Row-Level Security und Richtlinienmanagement

Meistern Sie PostgreSQL Row-Level Security (RLS) für feingranulare Zugriffskontrolle, Multi-Tenant-Isolierung und richtlinienbasierte Autorisierung. Vollständiger Leitfaden mit Implementierungsmustern aus der Praxis.

27 Aufrufe

Fortgeschrittene PostgreSQL-rollenbasierte Zugriffskontrolle (RBAC): Row-Level Security und Richtlinienmanagement

Einführung

PostgreSQL Row-Level Security (RLS) bietet eine feingranulare Zugriffskontrolle auf Zeilenebene und ermöglicht es Ihnen, komplexe Autorisierungsregeln direkt in der Datenbank zu erzwingen. Dies erübrigt das Filtern auf der Anwendungsebene und gewährleistet die Datensicherheit, unabhängig davon, wie Anwendungen auf die Datenbank zugreifen.

Warum Row-Level Security?

Probleme traditioneller Ansätze

Filtern auf Anwendungsebene:

# Bei jeder Abfrage muss an das Filtern gedacht werden
query = "SELECT * FROM documents WHERE tenant_id = %s"
results = db.execute(query, [current_user_tenant_id])

Probleme:
- Filter werden leicht vergessen (Sicherheitslücken)
- Code-Duplizierung in der gesamten Anwendung
- Kein Schutz bei direktem Datenbankzugriff
- Komplexe Audit-Anforderungen

Vorteile von Row-Level Security

  • Automatische Erzwingung: Richtlinien werden transparent angewendet
  • Zentralisierte Regeln: Sicherheitslogik an einem Ort
  • Multi-Tenant-Isolierung: Perfekt für SaaS-Anwendungen
  • Audit-Compliance: Integrierte Sicherheitsgarantien
  • Leistung: Effiziente Abfrageplanung

Grundlegendes RLS-Setup

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()
);

-- Row-Level Security 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 (Policies) 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("SET app.user_id = %s", [current_user_id])

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

Multi-Tenant SaaS-Anwendung

Vollständiges Multi-Tenant-Setup

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

-- Benutzer-Tabelle 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(
                    "SET app.tenant_id = %s",
                    [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('SET app.tenant_id = $1', [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();
});

Fortgeschrittene Richtlinienmuster

1. Hierarchische Zugriffskontrolle

-- Benutzer können Dokumente ihrer Abteilung und öffentliche Dokumente 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 (Shared Access Lists)

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'
        )
    );

Umgehen von RLS für administrative Aufgaben

Option 1: BYPASSRLS Rollen-Attribut

-- Superuser-ähnlichen Zugriff gewähren
CREATE ROLE admin_user WITH LOGIN PASSWORD 'sicheres_passwort';
ALTER ROLE admin_user BYPASSRLS;

-- Admin kann alle Zeilen unabhängig von Richtlinien sehen

Option 2: Freizügige Richtlinien für Admins

-- Freizügige 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 temporär deaktivieren

-- Für Wartungsskripte
BEGIN;
    ALTER TABLE documents DISABLE ROW LEVEL SECURITY;
    -- Administrative Operationen durchführen
    UPDATE documents SET archived = true WHERE created_at < NOW() - INTERVAL '1 year';
    ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
COMMIT;

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_user_id());

-- Benutzer kann nur Zeilen SELEKTIEREN, bei denen die owner_id übereinstimmt

WITH CHECK-Klausel

Steuert, welche neuen/aktualisierten Zeilen zulässig sind:

CREATE POLICY insert_own ON documents
    FOR INSERT
    WITH CHECK (owner_id = current_user_id());

-- Benutzer kann nur Zeilen EINFÜGEN, bei denen die owner_id übereinstimmt

Kombinierte Richtlinien

CREATE POLICY update_own ON documents
    FOR UPDATE
    USING (owner_id = current_user_id())       -- Kann nur eigene Zeilen aktualisieren
    WITH CHECK (owner_id = current_user_id()); -- Kann die Eigentümerschaft nicht ändern

Performance-Überlegungen

1. Spalten in Richtlinien 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 '%bericht%';

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

3. Unterabfragen in Richtlinien vermeiden

Schlecht - Unterabfrage wird für jede Zeile ausgeführt:

CREATE POLICY slow_policy ON documents
    USING (
        id IN (SELECT document_id FROM shares WHERE user_id = current_user_id())
    );

Besser - EXISTS verwenden:

CREATE POLICY faster_policy ON documents
    USING (
        EXISTS (
            SELECT 1 FROM shares 
            WHERE document_id = documents.id 
              AND user_id = current_user_id()
        )
    );

Bestens - JOIN in der Anwendung oder Materialized View verwenden:

CREATE MATERIALIZED VIEW user_accessible_documents AS
SELECT d.*, s.user_id AS shared_with
FROM documents d
LEFT JOIN shares s ON d.id = s.document_id;

CREATE INDEX ON user_accessible_documents(shared_with);

Überwachung und Auditierung

Aktive Richtlinien anzeigen

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

Auditierung von Richtlinienänderungen

-- Audit-Log für Richtlinienänderungen erstellen
CREATE TABLE policy_audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    policy_name VARCHAR(100),
    action VARCHAR(20),
    changed_by VARCHAR(50),
    changed_at TIMESTAMP DEFAULT NOW()
);

-- Trigger-Funktion zum Protokollieren von Änderungen
CREATE OR REPLACE FUNCTION log_policy_changes()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO policy_audit_log (table_name, policy_name, action, changed_by)
    SELECT 
        objid::regclass::text,
        object_identity,
        'CREATED',
        current_user
    FROM pg_event_trigger_ddl_commands()
    WHERE command_tag = 'CREATE POLICY';
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER log_policy_create
    ON ddl_command_end
    WHEN TAG IN ('CREATE POLICY')
    EXECUTE FUNCTION log_policy_changes();

Wirksamkeit von Richtlinien 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, true);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, true);

    -- Zugängliche Dokumente zurückgeben
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 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: Performance-Einbußen

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 verweigerter Zugriff

Als spezifischer Benutzer testen:

SET ROLE regular_user;
SET app.user_id = '123';

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

RESET ROLE;

Best Practices

  1. Sitzungsvariablen immer setzen: Sicherstellen, dass current_setting()-Werte befüllt sind.
  2. Spalten in Richtlinien indizieren: Indizes für alle Spalten erstellen, die in USING/WITH CHECK verwendet werden.
  3. Gründlich testen: Richtlinien mit verschiedenen Rollen und Szenarien verifizieren.
  4. Richtlinien einfach halten: Komplexe Logik beeinträchtigt die Performance.
  5. EXPLAIN verwenden: Abfragepläne mit aktiviertem RLS analysieren.
  6. Richtlinien dokumentieren: Richtlinienlogik für die Wartbarkeit kommentieren.
  7. Zuständigkeiten trennen: Unterschiedliche Richtlinien für SELECT, INSERT, UPDATE, DELETE verwenden.
  8. Performance überwachen: Benachrichtigungen für langsame Abfragen einrichten.

Fazit

PostgreSQL Row-Level Security bietet eine robuste, datenbankgestützte Zugriffskontrolle:

  • Automatisches Filtern: Sicherheit wird transparent angewendet.
  • Multi-Tenant-Isolierung: Ideal für SaaS-Anwendungen.
  • Flexible Richtlinien: Unterstützung komplexer Autorisierungsregeln.
  • Zentralisierte Sicherheit: Logik in der Datenbank, nicht verstreut im Code.

Beginnen Sie mit einer einfachen Mandantentrennung, testen Sie die Auswirkungen auf die Performance und erweitern Sie das System dann nach Bedarf um anspruchsvollere Autorisierungsmuster.