Ib txoj kev tshawb fawb txog kev siv Row Level Security hauv PostgreSQL

Raws li ib tug ntxiv rau Ib txoj kev tshawb fawb txog kev ua lag luam logic ntawm qib PostgreSQL khaws cia ua haujlwm и feem ntau yog rau cov lus teb kom ntxaws rau tawm tswv yim.

Ib feem theoretical tau piav qhia zoo hauv cov ntaub ntawv PostgreSQL - Txoj cai tiv thaiv kab. Hauv qab no yog ib qho kev siv ntawm ib qho me me kev ua lag luam tshwj xeeb - zais cov ntaub ntawv tshem tawm. Sketch mob siab rau kev siv Tus qauv tsim qauv siv RLS nthuav tawm nyias.

Tsis muaj dab tsi tshiab hauv tsab xov xwm, tsis muaj lub ntsiab lus zais lossis kev paub zais cia. Tsuas yog ib daim duab qhia txog kev siv lub tswv yim theoretical. Yog leej twg txaus siab nyeem. Yog tias koj tsis txaus siab, tsis txhob nkim koj lub sijhawm.

Nqe lus ntawm qhov teeb meem

Yog tias tsis dhia tob rau hauv qhov chaw kawm, luv luv, qhov teeb meem tuaj yeem tsim tau raws li hauv qab no: Muaj ib lub rooj uas siv ib qho chaw ua lag luam. Kab hauv lub rooj tuaj yeem raug tshem tawm, tab sis kab tsis tuaj yeem raug tshem tawm ntawm lub cev; lawv yuav tsum muab zais.

Rau nws yog hais tias: "Tsis txhob rho tawm ib yam dab tsi, tsuas yog rename nws. Hauv Internet khaws txhua yam "

Raws li txoj kev, nws raug nquahu kom tsis txhob rov sau dua cov haujlwm uas twb muaj lawm uas ua haujlwm nrog qhov chaw no.

Txhawm rau siv lub tswv yim no, lub rooj muaj tus cwj pwm yog_deleted. Tom qab ntawd txhua yam yog qhov yooj yim - koj yuav tsum xyuas kom meej tias tus neeg siv tuaj yeem pom tsuas yog cov kab uas tus cwj pwm yog_deleted cuav Lub mechanism siv rau dab tsi? Kab Qib Kev Ruaj Ntseg.

Kev siv

Tsim ib lub luag hauj lwm thiab schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Tsim lub hom phiaj lub rooj

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

Peb suav nrog Qib Kev Ruaj Ntseg

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 ;

Kev ua haujlwm - rho tawm ib kab hauv lub rooj

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;

Kev ua lag luam muaj nuj nqi - rho tawm ib daim ntawv

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

Результаты

Tus neeg siv yuav deletes cov ntaub ntawv

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

Tom qab tshem tawm, tus neeg siv yuav tsis pom daim ntawv

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

Tab sis nyob rau hauv lub database cov ntaub ntawv tsis yog deleted, tsuas yog tus cwj pwm hloov yog_del

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

Qhov twg yog qhov yuav tsum tau muaj nyob rau hauv cov lus qhia txog teeb meem.

Qhov no

Yog tias lub ncauj lus nthuav dav, hauv txoj kev tshawb fawb tom ntej no koj tuaj yeem ua piv txwv ntawm kev siv lub luag haujlwm raws li tus qauv rau kev sib cais cov ntaub ntawv nkag mus siv Row Level Security.

Tau qhov twg los: www.hab.com