Badanie dotyczące wdrażania zabezpieczeń na poziomie wiersza w PostgreSQL

Jako uzupełnienie Badanie dotyczące implementacji logiki biznesowej na poziomie funkcji przechowywanych w PostgreSQL и głównie za szczegółową odpowiedź na komentarz.

Część teoretyczna jest dobrze opisana w dokumentacji PostgreSQL - Zasady ochrony wierszy. Poniżej praktyczna realizacja małego konkretne zadanie biznesowe - ukrywanie usuniętych danych. Szkic poświęcony realizacji Modelowanie ról z wykorzystaniem RLS prezentowane osobno.

W artykule nie ma nic nowego, nie ma ukrytego znaczenia ani tajemnej wiedzy. Wystarczy szkic dotyczący praktycznej realizacji pomysłu teoretycznego. Jeśli ktoś jest zainteresowany, niech przeczyta. Jeśli nie jesteś zainteresowany, nie trać czasu.

Stwierdzenie problemu

Nie wnikając głębiej w temat, w skrócie problem można sformułować następująco: Istnieje tabela, która implementuje określony podmiot gospodarczy. Wiersze w tabeli można usunąć, ale wierszy nie można usunąć fizycznie; muszą być ukryte.

Mówi się bowiem: „Nie usuwaj niczego, po prostu zmień nazwę. Internet przechowuje WSZYSTKO”

Po drodze zaleca się, aby nie przepisywać istniejących funkcji przechowywanych, które współpracują z tym obiektem.

Aby wdrożyć tę koncepcję, tabela ma atrybut jest usunięty. Wtedy wszystko jest proste - trzeba zadbać o to, aby klient widział tylko linie, w których znajduje się atrybut jest usunięty FAŁSZ Do czego służy mechanizm? Zabezpieczenia na poziomie wiersza.

realizacja

Utwórz oddzielną rolę i schemat

CREATE ROLE repos;
CREATE SCHEMA repos;

Utwórz tabelę docelową

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

Zawieramy Bezpieczeństwo na poziomie wiersza

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 — usunięcie wiersza w 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;

Biznesowa funkcja — usunięcie 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 nie widzi dokumentu

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

Ale w bazie danych dokument nie jest usuwany, zmienia się jedynie atrybut is_del

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

Tego właśnie wymagano w opisie problemu.

Łączny

Jeżeli temat jest ciekawy, w kolejnym opracowaniu można pokazać przykład wdrożenia opartego na rolach modelu rozdzielania dostępu do danych z wykorzystaniem Row Level Security.

Źródło: www.habr.com