Kafukufuku wokhazikitsa Row Level Security mu PostgreSQL

Monga chothandizira Kafukufuku wogwiritsa ntchito malingaliro abizinesi pamlingo wa PostgreSQL ntchito zosungidwa и makamaka yankho latsatanetsatane pa ndemanga.

Gawo lanthanthi likufotokozedwa bwino muzolemba PostgreSQL - Ndondomeko zachitetezo cha mizere. Pansipa pali kukhazikitsidwa kothandiza kwa kakang'ono ntchito yeniyeni yabizinesi - kubisa deta yochotsedwa. Sketch yoperekedwa pakukhazikitsa Kutengera chitsanzo pogwiritsa ntchito RLS zoperekedwa padera.

Palibe chatsopano m'nkhaniyi, palibe tanthauzo lobisika kapena chidziwitso chachinsinsi. Chidule chabe chokhudza kukhazikitsidwa kwa lingaliro lachidziwitso. Ngati wina ali ndi chidwi, werengani. Ngati mulibe chidwi, musataye nthawi yanu.

Kupanga kwa vuto

Popanda kudumphira mozama mu phunziroli, mwachidule, vutoli likhoza kupangidwa motere: Pali tebulo lomwe limakhazikitsa bizinesi inayake. Mizere patebulo ikhoza kuchotsedwa, koma mizere singachotsedwe mwakuthupi; iyenera kubisika.

Pakuti akuti: “Osachotsa kalikonse, ingotchulanso dzina. Intaneti imasunga ZONSE"

M'kupita kwanthawi, ndibwino kuti musalembenso ntchito zomwe zasungidwa zomwe zimagwira ntchito ndi bungweli.

Kuti akwaniritse lingaliro ili, tebulo lili ndi tanthauzo cha_chachotsedwa. Ndiye chirichonse chiri chophweka - muyenera kuonetsetsa kuti kasitomala akhoza kuwona mizere yokhayo yomwe khalidwelo cha_chachotsedwa zabodza Kodi njira yogwiritsiridwa ntchito ndi chiyani? Row Level Security.

Реализация

Pangani gawo losiyana ndi schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Pangani tebulo lazomwe mukufuna

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

Yatsani Row Level Chitetezo

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 ;

Ntchito ya utumiki - Kuchotsa mzere patebulo

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;

Ntchito ya bizinesi - kufufuta chikalata

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

Zotsatira

Wogula amachotsa chikalatacho

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

Pambuyo kufufutidwa, kasitomala sawona chikalata

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

Koma mu nkhokwe chikalatacho sichichotsedwa, mawonekedwe okhawo amasinthidwa ndi_del

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

Zomwe zinali zofunika m'nkhani yamavuto.

Zotsatira

Ngati mutuwo uli wosangalatsa, mu phunziro lotsatira mukhoza kusonyeza chitsanzo chotsatira chitsanzo cholekanitsa kupeza deta pogwiritsa ntchito Row Level Security.

Source: www.habr.com