He rangahau mo te whakatinana Haumaru Taumata Haupae i PostgreSQL

Hei apiti ki He rangahau mo te whakatinana i te arorau pakihi i te taumata o nga mahi rongoa a PostgreSQL и te nuinga mo te whakautu taipitopito i runga i kōrero.

He pai te korero mo te waahanga ariā i roto i nga tuhinga PostgreSQL - Kaupapa here tiaki rarangi. Kei raro ko te whakatinanatanga mahi o te iti mahi pakihi motuhake - huna i nga raraunga kua mukua. Huanga i whakatapua ki te whakatinanatanga Te whakatauira ma te whakamahi i te RLS ka whakaatuhia motuhake.

Kaore he mea hou i roto i te tuhinga, kaore he tikanga huna, he matauranga ngaro. He huahua noa mo te whakatinanatanga a-ringa o te whakaaro ariā. Mena kei te pirangi tetahi, panuihia. Ki te kore koe e aro, kaua e moumou taima.

Kaupapa raru

Ki te kore e ruku hohonu ki roto i te kaupapa, poto, ka taea te whakatakoto i te raruraru penei: He tepu kei te whakatinana i tetahi hinonga pakihi. Ka taea te muku i nga rarangi o te ripanga, engari kaore e taea te whakakore tinana nga rarangi, me huna.

No te mea e kiia ana: "Kaua e muku tetahi mea, whakaingoatia noa. Kei te toa Ipurangi nga mea katoa"

I runga i te huarahi, he mea pai kia kaua e tuhi ano i nga mahi penapena e mahi tahi ana me tenei hinonga.

Hei whakatinana i tenei ariā, kei te ripanga te huanga kua_muku. Na he mea ngawari nga mea katoa - me mohio koe ka taea e te kiritaki te kite i nga raina anake kei roto i te huanga kua_muku teka He aha te tikanga e whakamahia ana? Haumaru Taumata Haupae.

Реализация

Waihangahia he mahi motuhake me te aronuinga

CREATE ROLE repos;
CREATE SCHEMA repos;

Waihangatia te ripanga i whäia

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

Whakauru matou Haumarutanga Taumata Haupae

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 ;

Mahi ratonga — te whakakore i tetahi rarangi i te ripanga

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;

Mahi pakihi — te whakakore i tetahi tuhinga

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

Результаты

Ka mukua e te kiritaki te tuhinga

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

I muri i te mukunga, karekau te kiritaki e kite i te tuhinga

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

Engari i roto i te patengi raraunga kaore i te whakakorehia te tuhinga, ko te huanga anake ka hurihia is_del

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

Ko tehea te mea i hiahiatia i roto i te korero rapanga.

Ko te hua

Mena he pai te kaupapa, i roto i te rangahau e whai ake nei ka taea e koe te whakaatu i tetahi tauira o te whakatinana i tetahi tauira i runga i te turanga mo te wehe i te uru raraunga ma te whakamahi i te Haumaru Taumata Haupae.

Source: will.com