Boithuto mabapi le ho kenya ts'ebetsong Row Level Security ho PostgreSQL

E le tlatsetso ho Thuto ea ho kenya ts'ebetsong logic ea khoebo boemong ba PostgreSQL mesebetsi e bolokiloeng и haholo bakeng sa karabo e qaqileng mabapi le tlhaloso.

Karolo ea theory e hlalositsoe hantle litokomaneng PostgreSQL - Melao ea tšireletso ea mela. Ka tlase ke ts'ebetsong e sebetsang ea e nyenyane mosebetsi o itseng oa khoebo - ho pata data e hlakotsoeng. Sketch e inehetseng ho ts'ebetsong Ho etsa mohlala ho sebelisa RLS e hlahisoang ka thoko.

Ha ho letho le lecha sehloohong sena, ha ho na moelelo o patiloeng kapa tsebo ea lekunutu. Sekhechana feela mabapi le ts'ebetsong e sebetsang ea mohopolo oa theory. Haeba ho na le motho ea thahasellang, e bale. Haeba ha u thahaselle, u se ke ua senya nako ea hau.

Mokhoa oa bothata

Ntle le ho kenella ka botebo sebakeng sa thuto, ka bokhutšoanyane, bothata bo ka rarolloa ka tsela e latelang: Ho na le tafole e sebelisang setsi se itseng sa khoebo. Mela e tafoleng e ka hlakoloa, empa mela e ke ke ea hlakoloa, e tlameha ho patoa.

Hobane ho thoe: "U se ke ua hlakola letho, mpa u le reha hape. Marang-rang a boloka TSOHLE"

Ha u ntse u le tseleng, ho eletsoa ho se ngole bocha mesebetsi e bolokiloeng e sebetsang le setheo sena.

Ho kenya ts'ebetsong mohopolo ona, tafole e na le tšobotsi e_hlakotsoe. Joale ntho e 'ngoe le e' ngoe e bonolo - u lokela ho etsa bonnete ba hore mofani a ka bona feela mela eo tšobotsi eo e leng ho eona e_hlakotsoe bohata Mochini o sebelisetsoa eng? Tšireletso ea Boemo ba Row.

Ts'ebetsong

Theha karolo e fapaneng le schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Theha tafole e reretsoeng

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

Re kenyeletsa Ts'ireletso ea Boemo ba Row

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 ;

Mosebetsi oa tšebeletso — ho phumula mola tafoleng

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;

Mosebetsi oa khoebo — ho phumula tokomane

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

Liphetho

Moreki o hlakola tokomane

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

Kamora ho hlakoloa, moreki ha a bone tokomane eo

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

Empa ho database tokomane ha e hlakoloe, ke tšobotsi feela e fetotsoeng ke_del

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

E leng sona se neng se hlokahala polelong ea bothata.

Phello

Haeba sehlooho se thahasellisa, thutong e latelang u ka bontša mohlala oa ho kenya ts'ebetsong mohlala o thehiloeng ho arola phihlello ea data ka ho sebelisa Row Level Security.

Source: www.habr.com