En undersøgelse om implementering af Row Level Security i PostgreSQL

Som et supplement til En undersøgelse om implementering af forretningslogik på niveau med PostgreSQL-lagrede funktioner и primært for et detaljeret svar på kommentar.

Den teoretiske del er godt beskrevet i dokumentationen PostgreSQL — Rækkebeskyttelsespolitikker. Nedenfor er en praktisk implementering af en lille specifik forretningsopgave - skjule slettede data. Skitse dedikeret til implementering Rollemodellering ved hjælp af RLS præsenteret separat.

Der er intet nyt i artiklen, der er ingen skjult mening eller hemmelig viden. Bare en skitse om den praktiske implementering af en teoretisk idé. Hvis nogen er interesseret, så læs den. Hvis du ikke er interesseret, så spild ikke din tid.

Formulering af problemet

Uden at dykke dybt ned i emneområdet, kort, kan problemstillingen formuleres som følger: Der er en tabel, der implementerer en bestemt forretningsenhed. Rækker i tabellen kan slettes, men rækker kan ikke slettes fysisk, de skal skjules.

For det siges: “Slet ikke noget, bare omdøb det. Internettet gemmer ALT"

Undervejs er det tilrådeligt ikke at omskrive eksisterende lagrede funktioner, der fungerer med denne enhed.

For at implementere dette koncept har tabellen attributten er_slettet. Så er alt simpelt - du skal sørge for, at klienten kun kan se de linjer, hvori attributten er_slettet falsk Hvad bruges mekanismen til? Sikkerhed på rækkeniveau.

implementering

Opret en separat rolle og skema

CREATE ROLE repos;
CREATE SCHEMA repos;

Opret måltabellen

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

Tænde for Sikkerhed på række niveau

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 ;

Service funktion — sletning af en række i tabellen

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;

Forretningsfunktion — sletning af et dokument

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

Fund

Klienten sletter dokumentet

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

Efter sletning kan klienten ikke se dokumentet

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

Men i databasen slettes dokumentet ikke, kun attributten ændres is_del

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

Hvilket var det, der krævedes i problemformuleringen.

Total

Hvis emnet er interessant, kan du i næste undersøgelse vise et eksempel på implementering af en rollebaseret model til adskillelse af dataadgang ved hjælp af Row Level Security.

Kilde: www.habr.com