En studie om implementering av Row Level Security i PostgreSQL

Som ett komplement till En studie om implementering av affärslogik på nivån för PostgreSQL-lagrade funktioner и främst för ett detaljerat svar på kommentar.

Den teoretiska delen är väl beskriven i dokumentationen PostgreSQL - Radskyddspolicyer. Nedan är en praktisk implementering av en liten specifik affärsuppgift - dölja raderad data. Skiss tillägnad implementering Rollmodellering med RLS presenteras separat.

Det finns inget nytt i artikeln, det finns ingen dold mening eller hemlig kunskap. Bara en skiss om det praktiska genomförandet av en teoretisk idé. Om någon är intresserad, läs den. Om du inte är intresserad, slösa inte bort din tid.

Problem uttalande

Utan att djupdyka i ämnesområdet, kortfattat, kan problemet formuleras på följande sätt: Det finns en tabell som implementerar en viss affärsenhet. Rader i tabellen kan tas bort, men rader kan inte tas bort fysiskt, de måste döljas.

För det sägs: "Radera inte någonting, döp bara om det. Internet lagrar ALLT"

Längs vägen är det tillrådligt att inte skriva om befintliga lagrade funktioner som fungerar med denna enhet.

För att implementera detta koncept har tabellen attributet is_deleted. Då är allt enkelt - du måste se till att klienten bara kan se linjerna där attributet is_deleted falsk Vad används mekanismen till? Säkerhet på radnivå.

genomförande

Skapa en separat roll och schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Skapa måltabellen

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

Sätta på Säkerhet på radnivå

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 — radera en rad 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;

Affärsfunktion — radera ett 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;

Resultat

Klienten tar bort dokumentet

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

Efter raderingen ser klienten inte dokumentet

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

Men i databasen raderas inte dokumentet, bara attributet ändras is_del

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

Vilket är vad som krävdes i problemformuleringen.

Totalt

Om ämnet är intressant kan du i nästa studie visa ett exempel på implementering av en rollbaserad modell för att separera dataåtkomst med hjälp av Row Level Security.

Källa: will.com