Daraasad ku saabsan hirgelinta Amniga Heerka Safka ee PostgreSQL

Kaabayaal ahaan Daraasad ku saabsan hirgelinta macquulka ganacsiga ee heerka PostgreSQL ee hawlaha kaydsan и inta badan jawaab faahfaahsan on faallo.

Qaybta aragtida ayaa si fiican loogu sharraxay dukumeentiyada PostgreSQL - Siyaasadaha ilaalinta safka. Hoos waxaa ku yaal hirgelin wax ku ool ah oo yar hawl ganacsi oo gaar ah - qarinta xogta la tirtiray. Naqshad u heellan hirgelinta Ku-dayasha doorka iyadoo la adeegsanayo RLS si gaar ah loo soo bandhigay.

Ma jiro wax ku cusub maqaalka, ma jiro macno qarsoon ama aqoon qarsoon. Kaliya sawir ku saabsan hirgelinta dhabta ah ee fikradda aragtida. Haddii cidi danaynayso, akhri. Haddii aadan xiisaynaynin, waqtigaaga ha lumin.

Abuurista dhibaatada

Iyadoo aan si qoto dheer loo gelin mawduuca, si kooban, dhibaatada waxaa loo qaabayn karaa sida soo socota: Waxaa jira miis fulinaya qayb ganacsi oo gaar ah. Safafka shaxda waa la tirtiri karaa, laakiin safafka jidh ahaan lama tirtiri karo, waa in la qariyaa.

Waayo, waxa la yidhi: "Waxba ha tirtirin, kaliya magac u bixi. Internetku wax walba wuu kaydiyaa"

Jidka dhexdiisa, waxaa lagu talinayaa in aan dib loo qorin hawlaha kaydsan ee jira ee la shaqeeya hay'addan.

Si loo hirgeliyo fikraddan, miisku wuxuu leeyahay sifo waa la tirtiray. Markaa wax walba waa sahlan yihiin - waxaad u baahan tahay inaad hubiso in macmiilku uu arki karo oo keliya khadadka ay ku jirto sifo waa la tirtiray been ah Waa maxay habka loo isticmaalo? Amniga Heerka Safka.

Реализация

Samee door iyo qorshe gaar ah

CREATE ROLE repos;
CREATE SCHEMA repos;

Samee miiska bartilmaameedka

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

Waxaan ka mid nahay Amniga Heerka Safka

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 ;

Shaqada adeegga - tirtirida safka miiska

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;

Shaqada ganacsiga - tirtirida dukumeenti

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

Результаты

Macmiilku wuu tirtiraa dukumeentiga

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

Ka dib markii la tirtiro, macmiilku ma arko dukumeentiga

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

Laakin keydka xogta dukumeentigu lama tirtirin, kaliya sifada ayaa la bedelay waa_del

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

Taas oo ah waxa looga baahnaa bayaanka dhibaatada.

Natiijada

Haddii mawduucu xiiso leeyahay, daraasadda soo socota waxaad ku tusi kartaa tusaale ahaan hirgelinta habka door-ku-salaysan ee kala-saarista helitaanka xogta iyadoo la adeegsanayo Amniga Heerka Safka.

Source: www.habr.com