Studie zur Umsetzung von Row Level Security in PostgreSQL

Als Ergänzung zu Studie zur Implementierung von Geschäftslogik auf der Ebene von PostgreSQL-Stored Functions und hauptsächlich für eine ausführliche Antwort findet man ein Kommentar.

Der theoretische Teil ist hervorragend in der Dokumentation dargestellt Postgres Pro — Zeilenvergabepolitiken. Im Folgenden wird die praktische Umsetzung eines kleinen konkreten Geschäftsziels – der Verbergung von entfernten Daten. Das Etüde widmet sich der Umsetzung des Rollenmodells unter Verwendung von RLS wird separat präsentiert.

Der Artikel bietet nichts Neues, keine versteckten Bedeutungen oder geheimen Kenntnisse. Es ist einfach eine Skizze zur praktischen Umsetzung einer theoretischen Idee. Wer interessiert ist – lesen Sie weiter. Wer nicht interessiert ist – vergeuden Sie Ihre Zeit nicht.

Problemstellung

Ohne tief in das Fachgebiet einzutauchen, kann die Aufgabe kurz wie folgt formuliert werden: es gibt eine Tabelle, die eine bestimmte Geschäftseinheit implementiert. Die Zeilen in der Tabelle können gelöscht werden, aber physisch dürfen die Zeilen nicht entfernt werden, sie müssen verborgen werden.

Denn es wird gesagt – 'Lösche nichts, benenne nur um. Das Internet speichert ALLES.'

Nebenbei sollte man bestehende gespeicherte Funktionen, die mit dieser Einheit arbeiten, nicht neu schreiben.

Um dieses Konzept umzusetzen, hat die Tabelle ein Attribut is_deleted. Danach ist alles einfach – es muss sichergestellt werden, dass der Kunde nur die Zeilen sehen kann, in denen das Attribut is_deleted falsch ist. Zu diesem Zweck wird der Mechanismus Row Level Security verwendet.

Implementierung

Erstellen Sie eine separate Rolle und ein Schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Erstellen Sie die Ziel-Tabelle

CREATE TABLE repos.file
(
...
is_del BOOLEAN DEFAULT FALSE
);
CREATE SCHEMA repos

Aktivieren wir Row Level Security

ALTER TABLE repos.file  ENABLE ROW LEVEL SECURITY ;
CREATE POLICY file_invisible_deleted  ON repos.file FOR ALL TO dba_role USING ( NOT is_deleted );
GRANT ALL ON TABLE repos.file to dba_role ;
GRANT USAGE ON SCHEMA repos TO dba_role ;

Servicefunktion — Löschung einer Zeile in der Tabelle

CREATE OR REPLACE repos.delete( curr_id repos.file.id%TYPE)
RETURNS integer AS $$
BEGIN
...
UPDATE repos.file
SET is_del = TRUE 
WHERE id = curr_id ; 
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Geschäftsfunktion — Löschung eines Dokuments

CREATE OR REPLACE business_functions.deleteDoc( doc_for_delete JSON )
RETURNS JSON AS $$
BEGIN
...
PERFORM  repos.delete( doc_id ) ;
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Ergebnisse

Der Kunde löscht ein Dokument

SELECT business_functions.delCFile( (SELECT json_build_object( 'CId', 3 )) );

Nach der Löschung sieht der Dokumentenkunde nichts mehr

SELECT business_functions.getCFile"( (SELECT json_build_object( 'CId', 3 )) ) ;
-----------------
(0 Zeilen)

Aber in der Datenbank wurde das Dokument nicht gelöscht, nur das Attribut geändert is_del

psql -d my_db
SELECT  id, name , is_del FROM repos.file ;
id |  name  | is_del
--+---------+------------
 1 |  test_1 | t
(1 Zeile)

Was in der Aufgabenstellung gefordert war.

Zusammenfassung

Wenn das Thema interessant ist, kann im nächsten Etüde ein Beispiel für die Implementierung eines rollenspezifischen Modells zur Datenzugriffstrennung unter Verwendung von Row Level Security gezeigt werden.

Quelle: habr.com