Studie implementace Row Level Security v PostgreSQL

Jako doplněk k Studie o implementaci obchodní logiky na úrovni uložených funkcí PostgreSQL a především pro podrobnou odpověď na komentář.

Teoretická část je výborně popsána v dokumentaci Postgres Pro — Politiky ochrany řádků. Níže je uvedena praktická realizace malé konkrétní obchodní úlohy — skrytí smazaných dat. Esej se věnuje implementaci rolového modelu s využitím RLS je prezentována samostatně.

V článku není nic nového, žádný skrytý smysl ani tajné znalosti. Jen skica o praktické realizaci teoretické myšlenky. Pokud má někdo zájem — čtete. Kdo nemá zájem — neztrácejte zbytečně čas.

Formulace úkolu

Při nezabíhání do hloubky dané problematiky lze úkol stručně formulovat následujícím způsobem: existuje tabulka implementující určitý obchodní objekt. Řádky v tabulce mohou být odstraňovány, ale fyzicky je nelze mazat, je třeba je skrýt.

Neboť je řečeno — „Nic nemazat, jen přejmenovávat. Internet uchovává VŠE“

Současně je žádoucí nepřepisovat již existující uložené funkce pracující s tímto objektem.

Pro implementaci tohoto konceptu má tabulka atribut is_deleted. Dále je vše jednoduché — je třeba zajistit, aby klient mohl vidět pouze řádky, ve kterých je atribut is_deleted nepravdivý. Pro co se používá mechanismus Row Level Security.

Implementace

Vytváříme samostatnou roli a schéma

CREATE ROLE repos;
CREATE SCHEMA repos;

Vytváříme cílovou tabulku

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

Aktivujeme 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 ;

Služební funkce — odstranění řádku v tabulce

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;

Obchodní funkce — odstranění 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;

Výsledky

Klient odstranuje dokument

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

Po odstranění klient dokument nevidí

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

Ale v databázi dokument nebyl odstraněn, pouze byl změněn 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)

Což bylo požadováno v zadání úkolu.

Výsledek

Pokud bude téma zajímavé, v dalším etudu můžeme ukázat příklad implementace rolového modelu dělení přístupu k datům za použití Row Level Security.

Zdroj: habr.com