Studio sull'implementazione della Row Level Security in PostgreSQL

Come complemento a Studio sull'implementazione della logica di business a livello di funzioni memorizzate in PostgreSQL e principalmente per una risposta dettagliata in commento.

La parte teorica è ben descritta nella documentazione Postgres Pro — Politiche di protezione delle righe. Di seguito viene esaminata l'implementazione pratica di un piccolo compito aziendale specifico — nascondere i dati eliminati. Questo studio è dedicato all'implementazione di un modello di ruolo utilizzando RLS presentato separatamente.

Nell'articolo non c'è nulla di nuovo, nessun significato nascosto e nessuna conoscenza segreta. È semplicemente un abbozzo sulla realizzazione pratica di un'idea teorica. Se qualcuno è interessato — legga. Chi non è interessato — non perda tempo.

Definizione del compito

Senza entrare troppo nel dettaglio dell'argomento, brevemente, la questione può essere formulata nel seguente modo: c'è una tabella che implementa una qualche entità aziendale. Le righe nella tabella possono essere eliminate, ma non possono essere fisicamente rimosse; devono essere nascoste.

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

Idealmente, è preferibile non riscrivere le funzioni già esistenti 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 è falso. A questo serve il meccanismo Row Level Security.

Implementazione

Creiamo un ruolo e uno schema separati

CREATE ROLE repos;
CREATE SCHEMA repos;

Creiamo una tabella target

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

Abilitiamo 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 di una 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 di un 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 un 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 database il documento non è stato eliminato, solo l'attributo is_del

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

Questo era esattamente ciò che si richiedeva nella formulazione del problema.

Risultato

Se l'argomento è interessante, nel prossimo studio possiamo mostrare un esempio di implementazione di un modello di ruolo per la separazione dell'accesso ai dati utilizzando il Row Level Security.

Fonte: habr.com