Étude zur Implementierung von Row Level Security in PostgreSQL

Als Ergänzung zu Studienbericht über die Implementierung der Geschäftslogik auf Ebene von gespeicherten Funktionen in PostgreSQL und hauptsächlich für eine umfassende Antwort auf Kommentar.

Der theoretische Teil ist in der Dokumentation hervorragend beschrieben Postgres Pro — Datensicherheitspolitiken. Im Folgenden wird die praktische Umsetzung einer kleinen konkreten Geschäftsanwendung – das Verbergen gelöschter Daten – behandelt. Das Studium widmet sich der Umsetzung eines Rollenmodells mit RLS wird separat vorgestellt.

Der Artikel enthält nichts Neues, es gibt keine versteckte Bedeutung oder geheimes Wissen. Es ist einfach eine Skizze zur praktischen Umsetzung einer theoretischen Idee. Wer interessiert ist — lesen Sie. Wer nicht interessiert ist — verschwenden Sie nicht Ihre Zeit.

Aufgabenstellung

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

Denn gesagt wurde – „Lösche nichts, benenne nur um. Das Internet bewahrt ALLES“

Es ist wünschenswert, vorhandene gespeicherte Funktionen, die mit dieser Einheit arbeiten, nicht neu zu schreiben.

Für die Umsetzung dieses Konzepts 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

Wir erstellen eine separate Rolle und ein Schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Wir erstellen die Ziel Tabelle

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

Aktivieren von 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_del );
GRANT ALL ON TABLE repos.file to dba_role ;
GRANT USAGE ON SCHEMA repos TO dba_role ;

Servicefunktion – Zeilen in der Tabelle löschen

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 – Dokument löschen

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 das Dokument

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

Nach der Löschung sieht der Dokumentenkunde nichts

WÄHLEN Sie business_functions.getCFile"( (WÄHLEN Sie json_build_object( 'CId', 3 )) ) ;
-----------------
(0 Zeilen)

Aber in der Datenbank wurde das Dokument nicht gelöscht, sondern 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.

Fazit

Wenn das Thema interessant ist, kann im nächsten Beispiel eine Implementierung eines Rollenmodells zur Zugriffskontrolle auf Daten unter Verwendung von Row Level Security gezeigt werden.

Quelle: habr.com