🥇Implementazione del modello di accesso con l'uso della Row Level Security in PostgreSQL

Sviluppo del tema Studio sull'implementazione della Row Level Security in PostgreSQL e per una risposta dettagliata in commento.

La strategia utilizzata implica l'adozione del concetto di "Business logic in DB", che è stato descritto in modo più dettagliato qui — Studio sull'implementazione della logica di business a livello di funzioni memorizzate in PostgreSQL

La parte teorica è ben descritta nella documentazione Postgres Pro — Politiche di protezione delle righe. Di seguito viene esaminata l'implementazione pratica di un compito specifico di business — un modello di accesso ai dati basato sui ruoli.

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

È necessario limitare l'accesso alla visualizzazione/inserimento/modifica/cancellazione di un documento in base al ruolo dell'utente dell'applicazione. Con ruolo si intende una voce nella tabella roles associata alla tabella con una relazione molti-a-molti. usersI dettagli dell'implementazione delle tabelle, per via della loro banalità, sono stati trascurati. Anche i dettagli specifici dell'implementazione relativi al dominio sono stati omessi.

Implementazione

Creiamo ruoli, schemi, tabelle

Creazione di oggetti del DB

CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
  id integer ,         --id documento
  man_id integer , --id del manager del documento
  stat_id integer ,  --id dello stato del documento
  ...
  is_del BOOLEAN DEFAULT FALSE 
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;

Creiamo funzioni per implementare RLS

Verifica della possibilità di eseguire SELECT su una riga

check_select

CREARE O SOSTITUIRE LA FUNZIONE store.check_select ( current_id store.docs.id%TYPE ) RESTITUISCE boolean AS $$
DICHIARARE
  result boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
INIZIO 
  -- Il DBA ha accesso a tutti i documenti
  SE SESSION_USER = 'curr_dba'
  ALLORA
    RESTITUISCI TRUE ;
  FINE SE ;
  --------------------------------

  --Se il documento ha l'etichetta 'cancellato' - non mostrarlo nella selezione
  SELEZIONA
    is_del
  IN
    result
  DA
    store.docs
  DOVE
    id = current_id ;
 SE result = TRUE
 ALLORA
   RESTITUISCI FALSE ;
 FINE SE ;
 --------------------------------

 --Ottenere l'id dell'utente corrente
 SELEZIONA
   service_function.get_curr_pid ()
 IN
   curr_pid ;
 --------------------------------

 --Ottenere l'id del manager del documento
 SELEZIONA
   man_id
 IN
   doc_man_id
 DA
   store.docs
 DOVE
   id = current_id ;
 --------------------------------

 --Se il manager del documento non è l'utente corrente o il manager non è assegnato
 --aggiungi il documento alla selezione
 SE doc_man_id != curr_pid O doc_man_id È NULL
 ALLORA
   RESTITUISCI TRUE  ;
 ALTRO
   --Ottenere lo stato corrente del documento
   SELEZIONA
     stat_id                                         
   IN
     curr_statid
   DA
     store.docs
   DOVE
     id = current_id ;
    
   --Se lo stato consente di visualizzare il documento - aggiungi il documento alla selezione                     
   SE curr_statid = 4 O curr_statid = 9
   ALLORA
     RESTITUISCI TRUE ;
   ALTRO
   --In caso contrario - escludi il documento dalla selezione
     RESTITUISCI FALSE ;
    FINE SE ;
  FINE SE ;
  --------------------------------

 RESTITUISCI FALSE ;
FINE
$$ LINGUAGGIO plpgsql SECURITY DEFINER;
ALTERA FUNZIONE store.check_select( store.docs.id%TYPE  ) PROPRIETÀ A store ;
REVOCARE ESECUZIONE SULLA FUNZIONE store.check_select( store.docs.id%TYPE  ) DA pubblico; 
CONCEDERE ESECUZIONE SULLA FUNZIONE store.check_select( store.docs.id%TYPE  ) A service_functions;

Verifica della possibilità di eseguire INSERT di una riga

check_insert

CREARE O SOSTITUIRE LA FUNZIONE store.check_insert ( current_id store.docs.id%TYPE ) RESTITUISCE boolean AS $$
DICHIARARE
  curr_role_id integer ;
INIZIO
  --DBA può aggiungere righe in ogni caso
  SE SESSION_USER = 'curr_dba'
  ALLORA
    RESTITUISCI TRUE ;
  FINE SE ;
  --------------------------------

 --Ottenere l'id del ruolo dell'utente corrente 
 SELEZIONA
   service_functions.current_rid()
  IN
    curr_role_id ;
 --------------------------------

--Se il ruolo consente la creazione di un nuovo documento
--consentire
SE curr_role_id = 3 O curr_role_id = 5     
ALLORA
  RESTITUISCI TRUE ;
FINE SE ;
--------------------------------
RESTITUISCI FALSE  ;
FINE
$$ LINGUAGGIO plpgsql SECURITY DEFINER;
ALTERA FUNZIONE store.check_insert( store.docs.id%TYPE  ) PROPRIETÀ A store ;
REVOCARE ESECUZIONE SULLA FUNZIONE store.check_insert( store.docs.id%TYPE  ) DA pubblico;
CONCEDERE ESECUZIONE SULLA FUNZIONE store.check_insert( store.docs.id%TYPE  ) A service_functions;

Verifica della possibilità di eseguire DELETE di una riga

check_delete

CREARE O SOSTITUIRE LA FUNZIONE store.check_delete ( current_id store.docs.id%TYPE )
RESTITUISCE boolean AS $$
INIZIO  
  --Solo il DBA può eliminare una riga 
  SE SESSION_USER = 'curr_dba'
  ALLORA
    RESTITUISCI TRUE ;
  FINE SE ;
  --------------------------------

  RESTITUISCI FALSE ;
FINE
$$ LINGUAGGIO plpgsql
SECURITY DEFINER;
ALTERA FUNZIONE store.check_delete( store.docs.id%TYPE  ) PROPRIETÀ A store ;
REVOCARE ESECUZIONE SULLA FUNZIONE store.check_delete( store.docs.id%TYPE  ) DA pubblico;

Verifica della possibilità di eseguire UPDATE di una riga.

update_using

CREA O SOSTITUISCI LA FUNZIONE store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
BEGIN  
   --I documenti con stato 'eliminato' non possono essere modificati
   IF is_del 
   THEN
     RETURN FALSE ;
 ELSE
    RETURN TRUE ;
  END IF ;

END
$$ LINGUAGGIO plpgsql SECURITY DEFINER;
ALTERA FUNZIONE store.update_using(  store.docs.id%TYPE ,  boolean  ) PROPRIETÀ A store ;
RITIRA ESECUZIONE SULLA FUNZIONE store.update_using(  store.docs.id%TYPE ,  boolean  ) DA public;
CONCEDE ESECUZIONE SULLA FUNZIONE store.update_using( store.docs.id%TYPE  ) A service_functions;

update_check

CREA O SOSTITUISCI LA FUNZIONE store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
DECLARE
  current_rid integer ;
  current_statid integer ;
BEGIN                

  --Il DBA può visualizzare la riga 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Ottenere l'id del ruolo dell'utente corrente 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------                            

 --Eliminazione del documento - modifica dell'attributo 
 IF is_deleted
 THEN
   --Se il ruolo dell'utente ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      INTO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      --Il documento nello stato *** non può essere eliminato 
      IF current_status_id = 11
      THEN
         RETURN FALSE ;
      ELSE
      --Si può eliminare il documento in altri stati
        RETURN TRUE ;
      END IF ;

    --Altrimenti, se il ruolo dell'utente ***
    ELSIF current_role_id = 5            
    THEN
      --Tutti gli stati del documento 
      RETURN TRUE ;
    ELSE
      --Altri utenti non possono eliminare documenti
      RETURN FALSE ;
    END IF ;
 ELSE      
   --L'aggiornamento del documento è consentito
    RETURN TRUE ;
END IF ;

RETURN FALSE ;
END
$$ LINGUAGGIO plpgsql SECURITY DEFINER;
ALTERA FUNZIONE store.update_with_check( storg.docs.id%TYPE ,  boolean   ) PROPRIETÀ A store ;
RITIRA ESECUZIONE SULLA FUNZIONE store.update_with_check( storg.docs.id%TYPE ,  boolean   )  DA public;
CONCEDE ESECUZIONE SULLA FUNZIONE store.update_with_check( store.docs.id%TYPE  ) A service_functions;

Attivazione della politica di Sicurezza a Livello di Riga per la tabella.

ABILITA SICUREZZA A LIVELLO DI RIGA

ALTERA TABLE store.docs ABILITA SICUREZZA A LIVELLO DI RIGA ;

CREA POLITICA doc_select SU store.docs PER SELECT A service_functions UTILIZZANDO ( (SELECT store.check_select(id)) );
CREA POLITICA doc_insert SU store.docs PER INSERT A service_functions CON CONTROLLI ( (SELECT store.check_insert(id)) );
CREA POLITICA docs_delete SU store.docs PER DELETE A service_functions UTILIZZANDO ( (SELECT store.check_delete(id)) );

CREA POLITICA doc_update_using SU store.docs PER UPDATE A service_functions UTILIZZANDO ( (SELECT store.update_using(id , is_del )) );
CREA POLITICA doc_update_check SU store.docs PER UPDATE A service_functions  CON CONTROLLI ( (SELECT store.update_with_check(id , is_del )) );

Risultato

Funziona.

La strategia proposta ha consentito di trasferire l'implementazione del modello dei ruoli dal livello delle funzioni aziendali al livello di memorizzazione dei dati.

Le funzioni possono essere utilizzate come modello per implementare modelli più avanzati di occultamento dei dati, se richiesto dai requisiti aziendali.

Fonte: habr.com