ProHoster > Blog > Utongi > Chidzidzo chekushandisa Row Level Chengetedzo muPostgreSQL

Chidzidzo chekushandisa Row Level Chengetedzo muPostgreSQL

Semubatsiri ku Chidzidzo chekushandisa bhizinesi pfungwa padanho rePostgreSQL rakachengetwa mabasa ΠΈ zvikuru nokuda kwemhinduro ine udzame pamusoro commentary.

Chikamu che theoretical chinotsanangurwa zvakanaka muzvinyorwa PostgreSQL - Row protection policy. Pasi pane kushandiswa kunoshanda kwechidiki rakananga bhizinesi basa - kuviga data rakadzimwa. Sketch yakatsaurirwa kuita Role modelling uchishandisa RLS zvakaratidzwa zvakasiyana.

Chidzidzo chekushandisa Row Level Chengetedzo muPostgreSQL

Hapana chitsva muchinyorwa, hapana chakavanzika chirevo kana ruzivo rwakavanzika. Ingori dhizaini nezvekuita kwekuita kweiyo theoretical pfungwa. Kana paine munhu anofarira, verenga. Kana usiri kufarira, usatambise nguva yako.

Kugadzirwa kwedambudziko

Pasina kunyura zvakadzama munharaunda yenyaya, muchidimbu, dambudziko rinogona kuumbwa sezvizvi: Pane tafura inoshandisa rimwe sangano rebhizimisi. Mitsara iri patafura inogona kudzimwa, asi mitsetse haigone kudzimwa; inofanira kuvanzwa.

Nekuti zvinonzi: "Usadzima chero chinhu, ingoitumidza zita. IInternet inochengetedza ZVESE"

Munzira, zvinokurudzirwa kusanyora zvakare mabasa akachengetwa anoshanda nesangano iri.

Kuti ishandise pfungwa iyi, tafura ine hunhu is_deted. Zvadaro zvinhu zvose zviri nyore - unofanira kuva nechokwadi chokuti mutengi anogona kuona chete mitsetse umo hunhu is_deted nhema Ndeipi nzira inoshandiswa? Row Level Security.

Kutevedzera

Gadzira chikamu chakasiyana uye schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Gadzira tafura yechinangwa

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

Isu tinosanganisira Row Chikamu Chengetedzo

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 ;

Basa rebasa - kudzima mutsara mutafura

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;

Bhizinesi basa - kudzima gwaro

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

Mhinduro

Mutengi anodzima gwaro

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

Mushure mekudzima, mutengi haaone gwaro

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

Asi mune dhatabhesi gwaro haribviswe, hunhu chete hunoshandurwa is_del

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

Izvo ndizvo zvaidiwa muchirevo chedambudziko.

Mugumisiro

Kana musoro wacho uchinakidza, muchidzidzo chinotevera unokwanisa kuratidza muenzaniso wekushandisa muenzaniso wekuparadzanisa kuwana data uchishandisa Row Level Security.

Source: www.habr.com

Voeg