Studium przypadku implementacji Row Level Security w PostgreSQL

Jako dodatek do Studium dotyczące wdrażania logiki biznesowej na poziomie funkcji przechowywanych PostgreSQL. i głównie dla rozwiniętej odpowiedzi na komentarz.

Część teoretyczna jest doskonale opisana w dokumentacji Postgres Pro — Polityka ochrony wierszy. Poniżej przedstawiono praktyczną realizację małego konkretnego zadania biznesowego — ukrywania danych usuniętych. Studium poświęcone realizacji Modelu ról z wykorzystaniem RLS przedstawione jest osobno.

Artykuł nie zawiera nic nowego, nie ma ukrytego sensu ani tajemnej wiedzy. To po prostu szkic dotyczący praktycznej realizacji teoretycznej idei. Jeśli kogoś to interesuje — niech czyta. Kto nie jest zainteresowany — niech nie marnuje swojego czasu.

Sformułowanie zadania

Nie wdrażając się głęboko w temat, w skrócie, zadanie można sformułować następująco: istnieje tabela realizująca pewną biznesową encję. Wiersze w tabeli mogą być usuwane, ale fizycznie nie można ich usuwać, należy je ukrywać.

Iż powiedziano — „Nic nie usuwaj, tylko zmieniaj nazwy. Internet wszystko pamięta”

Dodatkowo, pożądane jest, aby nie przepisywać już istniejących funkcji składowych działających z daną encją.

Aby zrealizować tę koncepcję, tabela ma atrybut is_deleted. Dalej wszystko jest proste — należy zrobić tak, aby klient widział tylko wiersze, w których atrybut is_deleted jest fałszywy. Do tego celu wykorzystywany jest mechanizm Row Level Security.

Realizacja

Tworzymy osobną rolę i schemat

CREATE ROLE repos;
CREATE SCHEMA repos;

Tworzymy docelową tabelę

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

Włączamy 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;

Funkcja serwisowa — usuwanie wiersza z tabeli

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;

Funkcja biznesowa — usuwanie dokumentu

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

Wyniki

Klient usuwa dokument

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

Po usunięciu, klient dokumentu nie widzi

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

Ale w BDR dokument nie został usunięty, tylko zmieniono atrybut is_del

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

Co było wymagane w postawionym zadaniu.

Podsumowanie

Jeśli temat będzie interesujący, w następnym studium można przedstawić przykład realizacji modelu ról rozdzielenia dostępu do danych z wykorzystaniem Row Level Security.

Źródło: habr.com