ProHoster > блог > адміністраванне > Эцюд па рэалізацыі Row Level Secutity у PostgreSQL

Эцюд па рэалізацыі Row Level Secutity у PostgreSQL

У якасці дадатку да Эцюд па рэалізацыя бізнес-логікі на ўзроўні захоўваемых функцый PostgreSQL и у асноўным для разгорнутага адказу на каментар.

Тэарэтычная частка выдатна апісана ў дакументацыі Postgres Pro - Палітыкі абароны радкоў. Ніжэй разгледжана практычная рэалізацыя маленькай канкрэтнай бізнес задачы - утойвання выдаленых дадзеных. Эцюд прысвечаны рэалізацыі Ролевай мадэлі з выкарыстаннем RLS прадстаўлены асобна.

Эцюд па рэалізацыі Row Level Secutity у PostgreSQL

У артыкуле нічога новага, няма схаванага сэнсу і таемных ведаў. Проста замалёўка аб практычнай рэалізацыі тэарэтычнай ідэі. Калі каму цікава - чытайце. Каму не цікава - не марнуйце свой час дарма.

Пастаноўка задачы

Не апускаючыся глыбока ў прадметную вобласць, коратка, задачу можна сфармуляваць наступным чынам: маецца табліца якая рэалізуе нейкую бізнэс сутнасць. Радкі ў табліцы могуць выдаляцца, але фізічна выдаляць радкі нельга, трэба іх хаваць.

Бо сказана - «Нічога не выдаляй, толькі пераназывай. Інтэрнэт захоўвае ЎСЕ»

Адначасна, пажадана не перапісваць ужо наяўныя захоўваемыя функцыі якія працуюць з дадзенай сутнасцю.

Для рэалізацыі дадзенай канцэпцыі табліца мае атрыбут. is_deleted. Далей усё проста - неабходна зрабіць так, што б кліент мог бачыць толькі радкі ў якіх атрыбут is_deleted кладзены. Для чаго і выкарыстоўваецца механізм Row Level Security.

Рэалізацыя

Ствараем асобную ролю і схему

CREATE ROLE repos;
CREATE SCHEMA repos;

Ствараем мэтавую табліцу

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

Уключаем Бяспека ўзроўню радка

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 ;

Сэрвісная функцыя - выдаленне радка ў табліцы

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;

Бізнэс функцыя - выдаленне дакумента

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

Вынікі

Кліент выдаляе дакумент

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

Пасля выдалення, кліент дакумента не бачыць

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

Але ў БД дакумент не выдалены, толькі зменены атрыбут is_del

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

Што і патрабавалася ў пастаноўцы задачы.

Вынік

Калі тэма будзе цікавая, у наступным эцюдзе можна паказаць прыклад рэалізацыі ролевай мадэлі падзелу доступу да дадзеных з выкарыстаннем Row Level Security.

Крыніца: habr.com

Дадаць каментар