Ucwaningo mayelana nokusebenzisa i-Row Level Security ku-PostgreSQL

Njengomphelelisi woku Ucwaningo lokuqalisa i-logic yebhizinisi ezingeni lemisebenzi egciniwe ye-PostgreSQL и ikakhulukazi ukuze uthole impendulo enemininingwane on ukuhlaziya.

Ingxenye yethiyori ichazwe kahle emibhalweni I-PostgreSQL - Izinqubomgomo zokuvikela umugqa. Ngezansi ukuqaliswa okungokoqobo kokuncane umsebenzi othile webhizinisi - ukufihla idatha esusiwe. Umdwebo onikezelwe ekusetshenzisweni Ukumodela kusetshenziswa i-RLS yethulwe ngokwehlukana.

Akukho okusha esihlokweni, akukho ncazelo efihliwe noma ulwazi oluyimfihlo. Umdwebo nje omayelana nokusetshenziswa okungokoqobo kombono wethiyori. Uma kukhona onentshisekelo, yifunde. Uma ungenayo intshisekelo, ungachithi isikhathi sakho.

Ukwakheka kwenkinga

Ngaphandle kokungena ngokujulile endaweni yesifundo, kafushane, inkinga ingahlelwa ngale ndlela elandelayo: Kunethebula elisebenzisa ibhizinisi elithile. Imigqa yethebula ingasuswa, kodwa imigqa ayikwazi ukususwa ngokoqobo; kufanele ifihliwe.

Ngoba kuthiwa: “Ungasusi lutho, vele uyiqambe kabusha. I-inthanethi igcina YONKE INTO"

Ngokuhamba kwesikhathi, kuyatuseka ukuthi ungaphinde ubhale imisebenzi egciniwe ekhona esebenza naleli bhizinisi.

Ukuze kusetshenziswe lo mqondo, ithebula linesibaluli kususiwe_kususiwe. Khona-ke yonke into ilula - udinga ukuqinisekisa ukuthi iklayenti angabona imigqa kuphela lapho imfanelo kususiwe_kususiwe amanga Iyini indlela esetshenziswayo? Ukuphepha Kwezinga Lomugqa.

Ukuqaliswa

Dala indima ehlukile ne-schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Dala ithebula eliqondiwe

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

Sifaka Ukuphepha Kwezinga Lomugqa

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 wesevisi — ukususa umugqa etafuleni

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 webhizinisi — ukususa idokhumenti

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

Imiphumela

Iklayenti lisusa idokhumenti

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

Ngemva kokususwa, iklayenti aliyiboni idokhumenti

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

Kodwa ku-database idokhumenti ayisusiwe, kuphela isici esishintshiwe yi_del

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

Okuyikhona obekudingeka esitatimendeni senkinga.

Umphumela

Uma isihloko sithakasela, ocwaningweni olulandelayo ungabonisa isibonelo sokuqalisa imodeli esekelwe indima yokuhlukanisa ukufinyelela kwedatha usebenzisa i-Row Level Security.

Source: www.habr.com