Etid o implementaciji Row Level Security u PostgreSQL

Kot dodatak k Etid o implementaciji poslovne logike na nivou sačuvanih funkcija PostgreSQL in pretežno za detaljan odgovor na комментарий.

Teorijski deo je odlično opisan u dokumentaciji Postgres Pro — Politike zaštite redova. Ispod je razmatrana praktična realizacija male konkretne poslovne zadatke — skrivanja obrisanih podataka. Etud je posvećen realizaciji Rolnog modela uz korišćenje RLS predstavljen je posebno.

U članku nema ničeg novog, nema skrivenog smisla ili tajnih znanja. Samo skica o praktičnoj realizaciji teorijske ideje. Ako nekoga zanima — čitajte. Onima kojima nije zanimljivo — nemojte trošiti vreme uzalud.

Definisanje zadatka

Ne ulazeći duboko u predmetnu oblast, zadatak se može kratko formulirati kao: ima se tabela koja realizuje neku poslovnu entitet. Redovi u tabeli mogu se brisati, ali fizički brisanje redova nije dozvoljeno, trebaju se skrivati.

Jer je rečeno — "Ništa ne briši, samo preimenuj. Internet čuva SVE"

Usput, poželjno je ne prepisivati već postojeće čuvane funkcije koje rade s ovom entitetom.

Za realizaciju ove koncepcije, tabela ima atribut is_deleted. Zatim je sve jednostavno — potrebno je učiniti da klijent može videti samo redove u kojima je atribut is_deleted lažan. Za šta se koristi mehanizam Row Level Security.

Implementacija

Pravimo posebnu ulogu i šemu

CREATE ROLE repos;
CREATE SCHEMA repos;

Pravimo ciljnu tabelu

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

Aktiviramo 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 reda u 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;

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 dokumenta ne vidi

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

Ali u Bazi podataka dokument nije obrisan, samo je promenjen 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 traženo u postavljenom zadatku.

Zaključek

Če bo tema zanimiva, lahko v naslednjem etudu pokažemo primer izvajanja rolnega modela delitve dostopa do podatkov z uporabo Row Level Security.

Izvor: habr.com