Uphononongo malunga nokuphunyezwa koKhuseleko lweNqanaba loMqolo kwiPostgreSQL

Njengomncedi woku Uphononongo malunga nokuphumeza ingqiqo yoshishino kwinqanaba le-PostgreSQL egciniweyo imisebenzi и ikakhulu impendulo eneenkcukacha phezu izimvo.

Inxalenye yethiyori ichazwe kakuhle kumaxwebhu I-PostgreSQL - Imigaqo-nkqubo yokhuselo lomqolo. Ngezantsi ukuphunyezwa okusebenzayo kwencinci umsebenzi othile weshishini - ukufihla idatha ecinyiweyo. Isiketshi esinikezelwe ekuphunyezweni Ukwenza umzekelo kusetyenziswa i-RLS zinikezelwe ngokwahlukeneyo.

Akukho nto intsha kwinqaku, akukho ntsingiselo efihliweyo okanye ulwazi oluyimfihlo. Umzobo nje malunga nokuphunyezwa okusebenzayo kombono wethiyori. Ukuba nabani na onomdla, yifunde. Ukuba awunamdla, musa ukuchitha ixesha lakho.

Џџ ѕЃ ° °

Ngaphandle kokuntywila nzulu kwindawo yesifundo, ngokufutshane, ingxaki inokuqulunqwa ngolu hlobo lulandelayo: Kukho itheyibhile ephumeza iqumrhu elithile loshishino. Imiqolo ekwitheyibhile ingacinywa, kodwa imiqolo ayinakususwa ngokwasemzimbeni, kufuneka ifihlwe.

Kuba kuthiwa: "Musa ukucima nantoni na, yinike igama kwakhona. I-Intanethi igcina YONKE INTO"

Endleleni, kuyacetyiswa ukuba ungaphindi ubhale imisebenzi ekhoyo egciniweyo esebenza neli qumrhu.

Ukuphumeza le ngcamango, itheyibhile inophawu icinyiwe. Emva koko yonke into ilula - kufuneka uqinisekise ukuba umxhasi unokubona kuphela imigca apho uphawu icinyiwe bubuxoki Yintoni indlela esetyenziselwa yona? Ukhuseleko lweNqanaba lomqolo.

Ukuphunyezwa

Yenza indima eyahlukileyo kunye neschema

CREATE ROLE repos;
CREATE SCHEMA repos;

Yenza itheyibhile ekujoliswe kuyo

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

Layita Ukhuseleko lweNqanaba lomgangatho

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 ;

Umsebenzi wenkonzo — ukucima umqolo okwitheyibhile

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;

Umsebenzi weshishini — ukucima uxwebhu

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

Iziphumo

Umxhasi uyalucima uxwebhu

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

Emva kokucinywa, umxhasi akaluboni uxwebhu

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

Kodwa kwi-database uxwebhu alucinywanga, kuphela uphawu olutshintshiweyo yi_del

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

Yeyona nto ibifuneka kwingxelo yengxaki.

Isiphumo

Ukuba isihloko sinomdla, kwisifundo esilandelayo ungabonisa umzekelo wokuphumeza imodeli esekelwe kwindima yokwahlula ukufikelela kwedatha usebenzisa i-Row Level Security.

umthombo: www.habr.com