Studija o implementaciji Row Level Security u PostgreSQL

Kao dodatak Studija o implementaciji poslovne logike na razini pohranjenih funkcija PostgreSQL i uglavnom za detaljan odgovor na komentar.

Teorijski dio je iznimno dobro opisan u dokumentaciji Postgres Pro — Politike zaštite redova. U nastavku se razmatra praktična implementacija male konkretne poslovne zadaće — skrivanje obrisanih podataka. Studija posvećena implementaciji Uloge sa korištenjem RLS predstavljena je odvojeno.

U članku nema ništa novo, nema skrivenog smisla ili tajnih saznanja. Samo skica o praktičnoj implementaciji teorijske ideje. Ako vas zanima — čitajte. Ako ne — ne gubite vrijeme.

Postavljanje zadatka

Ne zalazeći duboko u predmetnu oblast, kratak opis zadatka mogao bi se formulirati na sljedeći način: imamo tablicu koja implementira neku poslovnu entitet. Redovi u tablici mogu se brisati, ali fizički brisanje redova nije dopušteno, potrebno ih je sakriti.

Jer rečeno je — „Ništa ne brišite, samo preimenujte. Internet pamti SVE”

Usput, poželjno je ne prekrajati već postojeće pohranjene funkcije koje rade s tom entitetom.

Za implementaciju ovog koncepta, tablica ima atribut is_deleted. Dalje je sve jednostavno — potrebno je osigurati da klijent može vidjeti samo redove u kojima je atribut is_deleted lažan. Zato se koristi mehanizam Row Level Security.

Implementacija

Kreiramo posebnu ulogu i shemu

CREATE ROLE repos;
CREATE SCHEMA repos;

Kreiramo ciljnu tablicu

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

Omogućujemo 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;

Servisna funkcija — brisanje redova u tablici

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;

Poslovna funkcija — brisanje dokumenta

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

Rezultati

Klijent briše dokument

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

Nakon brisanja, klijent ne vidi dokument

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

Ali u bazi podataka dokument nije obrisan, samo je promijenjen atribut is_del

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

Što je i bilo zatraženo u opisu zadatka.

Zaključak

Ako će tema biti zanimljiva, u sljedećoj studiji može se pokazati primjer implementacije rolne modele podjele pristupa podacima koristeći Row Level Security.

Izvor: habr.com