Studie om genomförande av Row Level Security i PostgreSQL

Som ett komplement till Studie om genomförande av affärslogik på nivån av lagrade funktioner i PostgreSQL och främst för en utförlig respons till kommentar.

Den teoretiska delen är utmärkt beskriven i dokumentationen Postgres Pro — Radernas skyddspolicyer. Nedan diskuteras en praktisk implementering av en liten specifik affärsuppgift — att dölja borttagna data. Studien ägnas åt implementeringen av en rollmodell med användning av RLS som presenteras separat.

Artikeln innehåller ingenting nytt, inga dolda betydelser eller hemliga insikter. Det är bara en skiss av praktisk tillämpning av en teoretisk idé. Om någon är intresserad — läs. Om inte — slösa inte din tid.

Uppgiftsställande

Utan att fördjupa oss alltför mycket i ämnesområdet kan vi kort formulera uppgiften på följande sätt: det finns en tabell som representerar en affärsenhet. Rader i tabellen kan tas bort, men rader får inte tas bort fysiskt, de måste döljas.

Ty det är sagt — "Ta aldrig bort någonting, bara döp om det. Internet kommer att spara ALLT"

Det är dessutom önskvärt att inte skriva om redan existerande lagrade funktioner som arbetar med denna enhet.

För att genomföra detta koncept har tabellen en attribut is_deleted. Vidare är allt enkelt — vi måste se till att klienten endast kan se rader där attributet is_deleted är falskt. För detta används mekanismen Row Level Security.

Implementering

Vi skapar en särskild roll och schema

CREATE ROLE repos;
CREATE SCHEMA repos;

Vi skapar den måltavlan

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

Vi aktiverar 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;

Tjänstefunktion — ta bort 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 — ta bort dokumentet

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 borttagningen ser klienten av dokumentet inte

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

Men i databasen har dokumentet inte tagits bort, endast attributet har ändrats is_del

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

Vilket var vad som krävdes i uppgiften.

Resultat

Om ämnet är intressant kan vi i nästa studie visa ett exempel på implementering av en rollmodell för delad datatillgång med användning av Row Level Security.

Källa: habr.com