Studie af implementering af Row Level Security i PostgreSQL

Som et supplement til Studie af implementering af forretningslogik på niveau med gemte funktioner i PostgreSQL og primært til en udvidet besvarelse og få en fungerende kommando. kommentar.

Den teoretiske del er fremragende beskrevet i dokumentationen Postgres Pro — Rækkebeskyttelsespolitikker. Nedenfor gennemgås den praktiske implementering af en lille konkret forretningsopgave — skjulning af fjernede data. Studiet dedikeret til implementeringen af en rollemodel ved brug af RLS præsenteres separat.

I artiklen er der ikke noget nyt, ingen skjult mening eller hemmelig viden. Bare en skitse af praktisk implementering af en teoretisk idé. Hvis nogen er interesseret - læs. Hvis ikke - spild ikke din tid.

Problemformulering

Uden at dykke dybt ned i emnet kan opgaven kort beskrives som følger: der er en tabel, der implementerer en forretningsenhed. Rækker i tabellen kan slettes, men fysiske sletninger er ikke mulige; de skal skjules.

For det er sagt — "Slet ikke noget, omdøb blot. Internettet gemmer ALT"

Samtidig er det ønskeligt ikke at omskrive allerede eksisterende gemte funktioner, der arbejder med denne enhed.

For at implementere dette koncept har tabellen en attribut is_deleted. Herefter er det simpelt — det er nødvendigt at sikre, at kunden kun kan se de rækker, hvor attributten is_deleted er falsk. Til dette bruges mekanismen Row Level Security.

Implementering

Vi opretter en separat rolle og et skema

CREATE ROLE repos;
CREATE SCHEMA repos;

Opretter måltavlen

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

Aktiverer Row Level Security

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 ;

Servicefunktion — 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 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;

Resultater

Kunden sletter dokumentet

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

Efter sletningen kan kunden ikke se dokumentet

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

Men i databasen er dokumentet ikke slettet, kun attributten is_del

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

Som krævet i opgaveformuleringen.

Resultat

Hvis emnet er interessant, kan næste studie vise et eksempel på implementeringen af en rollemodel for adskillelse af dataadgang ved brug af Row Level Security.

Kilde: habr.com