Implementierungsstudie zur Sicherheit auf Zeilenebene in PostgreSQL

Als Ergänzung zur Etüde zur Implementierung von Geschäftslogik auf der Ebene der gespeicherten PostgreSQL-Funktionen und vor allem zur detaillierten Beantwortung des Kommentars .



Die Theorie ist in der PostgreSQL- Dokumentation - Row Security Policies gut beschrieben . Im Folgenden finden Sie eine praktische Implementierung einer kleinen spezifischen Geschäftsaufgabe - das Ausblenden gelöschter Daten. Eine Studie zur Implementierung des Rollenmodells mit RLS wird separat vorgestellt.

Der Artikel ist nichts Neues, es gibt keine versteckte Bedeutung und kein geheimes Wissen. Nur eine Skizze über die praktische Umsetzung einer theoretischen Idee. Wenn jemand interessiert ist, lesen Sie weiter. Wer nicht interessiert ist - verschwenden Sie nicht Ihre Zeit.

Formulierung des Problems

Ohne kurz auf den Themenbereich einzugehen, kann die Aufgabe wie folgt formuliert werden: Es gibt eine Tabelle, die eine bestimmte Geschäftseinheit implementiert. Zeilen in der Tabelle können gelöscht werden, aber Sie können Zeilen nicht physisch löschen. Sie müssen sie ausblenden.



Denn es heißt: „Lösche nichts, benenne es einfach um. Das Internet speichert ALLES. “



Auf dem Weg dorthin ist es ratsam, vorhandene gespeicherte Funktionen, die mit dieser Entität funktionieren, nicht neu zu schreiben.



Um dieses Konzept zu implementieren, verfügt die Tabelle über das Attribut is_deleted . Dann ist alles einfach - Sie müssen es so gestalten, dass der Client nur Zeilen sehen kann, in denen das Attribut is_deleted falsch ist. Wofür wird der Sicherheitsmechanismus auf Zeilenebene verwendet ?

Implementierung

Erstellen Sie eine separate Rolle und ein separates Schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Erstellen Sie die Zieltabelle

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

Aktivieren Sie die Sicherheit auf Zeilenebene

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öschen einer Zeile in einer 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öschen von Dokumenten

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

Client löscht Dokument

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

Nach dem Löschen sieht der Client das Dokument nicht

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

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

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

Dies war bei der Formulierung des Problems erforderlich.

Ergebnis

Wenn das Thema interessant ist, können Sie in der nächsten Etüde ein Beispiel für die Implementierung eines rollenbasierten Modells für die gemeinsame Nutzung des Datenzugriffs mithilfe der Sicherheit auf Zeilenebene zeigen.