Studio sull'implementazione della Row Level Security in PostgreSQL

Come integrazione a Studio sulla realizzazione della logica di business a livello di funzioni memorizzate in PostgreSQL e principalmente per una risposta dettagliata con commento.

La parte teorica è ben descritta nella documentazione Postgres Pro — Politiche di protezione delle righe. Di seguito è riportata l'implementazione pratica di un piccolo caso d'uso specifico — nascondere i dati rimossi. Studio dedicato all'implementazione di un modello di ruolo utilizzando RLS è presentato separatamente.

Nell'articolo non c'è nulla di nuovo, nessun significato nascosto e conoscenze segrete. Solo un abbozzo su un'implementazione pratica di un'idea teorica. Se a qualcuno interessa — leggete. A chi non interessa — non sprecate il vostro tempo.

Definizione del compito

Senza addentrarsi profondamente nell'argomento, in breve, la questione può essere formulata nel seguente modo: è presente una tabella che implementa una certa entità aziendale. Le righe nella tabella possono essere eliminate, ma non è possibile rimuovere fisicamente le righe, devono essere nascoste.

Poiché è stato detto — «Non eliminare niente, solo rinomina. Internet conserva TUTTO»

Nel contempo, è consigliabile non riscrivere già esistenti funzioni memorizzate che lavorano con questa entità.

Per implementare questo concetto, la tabella ha un attributo is_deleted. Poi tutto è semplice: è necessario fare in modo che il cliente possa vedere solo le righe in cui l'attributo is_deleted è impostato. A questo scopo viene utilizzato il meccanismo Row Level Security.

Implementazione

Creiamo un ruolo e uno schema separati

CREATE ROLE repos;
CREATE SCHEMA repos;

Creiamo la tabella di destinazione

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

Attiviamo 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 ;

Funzione di servizio — eliminazione della riga nella tabella

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 aziendale — eliminazione del documento

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

Il cliente elimina il documento

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

Dopo l'eliminazione, il cliente del documento non vede

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

Ma nel DB il documento non è stato eliminato, solo l'attributo è stato modificato is_del

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

Che era esattamente ciò che era richiesto nella formulazione del compito.

Risultato

Se l'argomento risulta interessante, nel prossimo studio possiamo mostrare un esempio di implementazione del modello di ruoli per la separazione dell'accesso ai dati utilizzando la Sicurezza a Livello di Riga.

Fonte: habr.com