Etīde par Row Level Security ieviešanu PostgreSQL

Kā papildinājumu Studija par biznesa loģikas realizāciju PostgreSQL uzglabāšanas funkciju līmenī un galvenokārt detalizētai atbildei uz komentārs.

Teorētiskā daļa ir lieliski aprakstīta dokumentācijā Postgres Pro — Rindu aizsardzības politikas. Zemāk tiks aplūkota praktiskā maza konkrētā biznesa uzdevuma — dzēsto datu slēpšanas. Studija, kas veltīta Lomu modeļa realizācijai, izmantojot RLS ir atsevišķi izklāstīta.

Rakstā nav nekas jauns, nav slēpta jēga vai noslēpumu. Tā ir vienkārša piezīme par praktiskās realizācijas teorētisko ideju. Ja kādu interesē — lasiet. Kam neinteresē — netērējiet savu laiku velti.

Uzdevuma formulēšana

Nedziļinoties dziļi tēmā, īsi, uzdevumu var formulēt šādi: ir tabula, kas realizē noteiktu biznesa entītiju. Rindas tabulā var tikt dzēstas, bet fiziski dzēst rindas nav atļauts, tās ir jāslēpj.

Jo ir teikts — “Neko nemācīties, tikai pārdēvēt. Internets saglabā VISU”

Ideāli vēlams nebūt pārrakstīt jau esošās glabāšanas funkcijas, kas strādā ar šo entītiju.

Šīs koncepcijas īstenošanai tabulai ir atribūts is_deleted. Tālāk viss ir vienkārši — ir jāpanāk, lai klients redzētu tikai rindas, kurās atribūts is_deleted ir nepatiesi. Tāpēc tiek izmantots mehānisms Rindu līmeņa drošība.

Realizācija

Izveidojam atsevišķu lomu un shēmu

CREATE ROLE repos;
CREATE SCHEMA repos;

Izveidojam mērķa tabulu

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

Ieslēdzam Rindu līmeņa drošību

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;

Servisa funkcija — rinda dzēšana tabulā

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;

Biznesa funkcija — dokumenta dzēšana

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

Rezultāti

Klients dzēš dokumentu

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

Pēc dzēšanas klients dokumentu neredz

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

Bet datu bāzē dokuments netika dzēsts, tikai atribūts mainīts is_del

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

Kas arī bija prasītajā uzdevumā.

Rezultāts

Ja, ja ir interesanta tēma, nākamajā etūdē varam parādīt piekļuves datiem sadalīšanas lomu modeļa īstenošanas piemēru, izmantojot Rindu līmeņa drošību.

Avots: habr.com