Studiu despre implementarea Seqvenței de Securitate la Nivel de Rând în PostgreSQL

Ca o completare la Studiu despre implementarea logici de afaceri la nivelul funcțiilor stocate în PostgreSQL și în principal pentru un răspuns extins pe comentariu.

Partea teoretică este bine descrisă în documentație Postgres Pro — Politicile de protecție a rândurilor. Mai jos este discutată implementarea practică a unei mici sarcini de afaceri specifice — ascunderea datelor șterse. Studiul este dedicat implementării unui model de rol folosind RLS prezentat separat.

În articol nu este nimic nou, nu există sens ascuns și cunoștințe secrete. Doar o schiță despre implementarea practică a unei idei teoretice. Dacă este cineva interesat — citiți. Pentru cei care nu sunt interesați — nu pierdeți timpul în zadar.

Formularea problemei

Fără a aprofunda prea mult în domeniu, pe scurt, sarcina poate fi formulată astfel: există un tabel care implementează o anumită entitate de afaceri. Rândurile din tabel pot fi șterse, dar ștergerea fizică a rândurilor nu este permisă, trebuie să le ascundem.

Căci este spus — „Nu șterge nimic, doar redenumește. Internetul păstrează TOT”

În paralel, ar fi bine să nu rescriem funcțiile stocate existente care lucrează cu această entitate.

Pentru implementarea acestei concepții, tabelul are un atribut is_deleted. Apoi totul devine simplu — trebuie să facem astfel încât clientul să poată vedea doar rândurile în care atributul is_deleted este fals. Pentru aceasta se folosește mecanismul Row Level Security.

Implementarea

Creăm un rol și un schema separate

CREATE ROLE repos;
CREATE SCHEMA repos;

Creăm tabelul țintă

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

Activăm 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;

Funcția de serviciu — ștergerea unui rând în tabel

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;

Funcția de afaceri — ștergerea unui document

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

Rezultate

Clientul șterge documentul

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

După ștergere, clientul documentului nu vede

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

Dar în BD documentul nu este șters, doar atributul a fost modificat is_del

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

Ceea ce era necesar în formularea sarcinii.

Rezultatul

Dacă subiectul este interesant, în următorul studiu putem prezenta un exemplu de implementare a modelului de roluri pentru restricționarea accesului la date folosind Row Level Security.

Sursa: habr.com