He haʻawina e pili ana i ka hoʻokō ʻana i ka Row Level Security ma PostgreSQL

Ma keʻano he hoʻokō He haʻawina e pili ana i ka hoʻokō ʻana i ka loiloi ʻoihana ma ke kiʻekiʻe o nā hana mālama ʻia ʻo PostgreSQL и nui no ka pane kiko'ī maluna o ka manaʻo.

Ua wehewehe maikaʻi ʻia ka ʻāpana theoretical ma ka palapala PostgreSQL - Nā kulekele pale lālani. Aia ma lalo kahi hoʻokō pono o kahi liʻiliʻi ʻoihana ʻoihana kikoʻī - hūnā i ka ʻikepili i holoi ʻia. Sketch i hoʻolaʻa ʻia no ka hoʻokō Hoʻohālike kūlana me RLS hoike kaawale.

ʻAʻohe mea hou ma ka ʻatikala, ʻaʻohe manaʻo huna a ʻike huna. He kiʻi kiʻi wale nō e pili ana i ka hoʻokō pono ʻana o kahi manaʻo theoretical. Inā hoihoi kekahi, e heluhelu. Inā ʻaʻole ʻoe hoihoi, mai hoʻopau i kou manawa.

Ka hoʻokumu ʻana i ka pilikia

Me ka luʻu ʻole ʻana i ke kumuhana, pōkole, hiki ke hoʻokumu ʻia ka pilikia penei: Aia kahi papaʻaina e hoʻokō i kekahi ʻoihana ʻoihana. Hiki ke holoi ʻia nā lālani ma ka papaʻaina, akā ʻaʻole hiki ke holoi kino ʻia nā lālani, pono e hūnā ʻia.

No ka mea, ua ʻōlelo ʻia: "Mai holoi i kekahi mea, e hoʻololi i ka inoa. Ke kūʻai aku nei ka Pūnaewele i nā mea āpau"

Ma ke ala, ʻoi aku ka maikaʻi ʻaʻole e kākau hou i nā hana i mālama ʻia e hana pū me kēia hui.

No ka hoʻokō ʻana i kēia manaʻo, aia i ka pākaukau ka ʻano ua_hoʻopau ʻia. A laila maʻalahi nā mea a pau - pono ʻoe e hōʻoia e ʻike wale ka mea kūʻai aku i nā laina i loaʻa ai ke ʻano ua_hoʻopau ʻia wahahee He aha ka mīkini i hoʻohana ʻia? Palekana pae lalani.

Ka hoʻokō

E hana i kahi hana a me ka hoʻolālā ʻokoʻa

CREATE ROLE repos;
CREATE SCHEMA repos;

E hana i ka papa kuhikuhi

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

E huli Palekana pae pae

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 ;

Hana lawelawe - ka holoi ʻana i kahi lālani ma ka pākaukau

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;

Hana ʻoihana - ka holoi ʻana i kahi palapala

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

Nā hualoaʻa

Holoi ka mea kūʻai aku i ka palapala

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

Ma hope o ka holoi ʻana, ʻaʻole ʻike ka mea kūʻai aku i ka palapala

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

Akā i loko o ka waihona ʻaʻole holoi ʻia ka palapala, hoʻololi wale ʻia ke ʻano is_del

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

ʻO ia ka mea i koi ʻia i ka ʻōlelo pilikia.

ʻO ka hopena

Inā hoihoi ke kumuhana, ma ka haʻawina aʻe hiki iā ʻoe ke hōʻike i kahi laʻana o ka hoʻokō ʻana i kahi kumu hoʻohālike no ka hoʻokaʻawale ʻana i ka ʻikepili me ka hoʻohana ʻana i ka Row Level Security.

Source: www.habr.com