Un studiu nantu à l'implementazione di Row Level Security in PostgreSQL

Cum'è un cumplementu à Un studiu nantu à l'implementazione di a logica cummerciale à u livellu di e funzioni almacenate PostgreSQL и principalmente per una risposta dettagliata nantu cummentariu.

A parte teorica hè ben descritta in a documentazione PostgreSQL - Politiche di prutezzione di fila. Quì sottu hè una implementazione pratica di un picculu compitu specificu di l'affari - nasconde i dati eliminati. Sketch dedicatu à l'implementazione Modellu di rolu cù RLS presentatu separatamente.

Ùn ci hè nunda di novu in l'articulu, ùn ci hè micca significatu oculatu o cunniscenza secreta. Solu un sketch nantu à l'implementazione pratica di una idea teorica. Sè qualchissia hè interessatu, leghjite. Se ùn site micca interessatu, ùn perde micca u vostru tempu.

Formulazione di u prublema

Senza immersione in u sughjettu, in breve, u prublema pò esse formulatu cusì: Ci hè una tavola chì implementa una certa entità cummerciale. E fila in a tavula ponu esse sguassate, ma e fila ùn ponu micca esse sguassate fisicamente; devenu esse oculate.

Perchè hè dettu: "Ùn sguassate nunda, solu rinominallu. L'Internet guarda TUTTI"

In u caminu, hè cunsigliu per ùn riscrive e funzioni almacenate esistenti chì travaglianu cù questa entità.

Per implementà stu cuncettu, a tavola hà l'attributu hè_sguassatu. Allora tuttu hè simplice - avete bisognu di assicurà chì u cliente pò vede solu e linee in quale l'attributu hè_sguassatu falsu Chì ghjè u mecanismu utilizatu per? Sicurezza di u Livellu di Fila.

Реализация

Crea un rolu è schema separatu

CREATE ROLE repos;
CREATE SCHEMA repos;

Crea a tavola di destinazione

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

Accende Sicurezza di Livellu di Fila

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 ;

Funzione di serviziu - sguassà una fila in a tavula

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;

Funzione cummerciale - sguassà un documentu

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

Risultati

U cliente sguassate u documentu

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

Dopu a eliminazione, u cliente ùn vede micca u documentu

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

Ma in a basa di dati u documentu ùn hè micca sguassatu, solu l'attributu hè cambiatu is_del

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

Chì hè ciò chì era necessariu in a dichjarazione di u prublema.

U risultatu

Se u tema hè interessante, in u prossimu studiu pudete dimustrà un esempiu di implementazione di un mudellu basatu in u rolu per separà l'accessu di dati utilizendu Row Level Security.

Source: www.habr.com