Implementarea modelului de acces bazat pe roluri cu utilizarea Row Level Security în PostgreSQL

Dezvoltarea temei Studiu despre implementarea Seqvenței de Securitate la Nivel de Rând în PostgreSQL și pentru un răspuns detaliat pe comentariu.

Strategia utilizată implică utilizarea conceptului de „logica de afaceri în BD”, care a fost descrisă mai detaliat aici — Studiu despre implementarea logici de afaceri la nivelul funcțiilor stocate în PostgreSQL

Partea teoretică este bine descrisă în documentație Postgres Pro — Politicile de protecție a rândurilor. Mai jos este analizată implementarea practică a unei sarcini de afaceri specifice — modelul de acces la date.

Î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

Este necesară delimitarea accesului la vizualizarea/inserarea/modificarea/ștergerea documentului în funcție de rolul utilizatorului aplicației. Prin rol se înțelege o înregistrare în tabelul roles asociat printr-o relație de tip many-to-many cu tabelul aveau adrese de email pe domeniile. Detalii despre implementarea tabelelor, din motive de trivialitate, sunt omise. De asemenea, sunt omise detaliile specifice implementării legate de domeniul de interes.

Implementarea

Creăm roluri, scheme, tabelul

Crearea obiectelor BD

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

Creăm funcții pentru implementarea RLS

Verificarea posibilității de a efectua SELECT pe linie

check_select

CREAȚI SAU ÎNLOCUIȚI FUNCȚIA store.check_select ( current_id store.docs.id%TYPE ) RETURNEAZĂ boolean AS $$
DECLARE
  result boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- DBA are acces la toate documentele
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  --Dacă documentul are eticheta 'șters' - nu se va arăta în selecție
  SELECT
    is_del
  INTO
    result
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF result = TRUE
 THEN
   RETURN FALSE ;
 END IF ;
 --------------------------------

 --Obține id-ul utilizatorului curent
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Obține id-ul managerului documentului
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Dacă managerul documentului nu este utilizatorul curent sau managerul nu este desemnat
 --adaugă documentul în selecție
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE  ;
 ELSE
   --Obține statutul curent al documentului
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Dacă statutul permite vizualizarea documentului - adaugă documentul în selecție                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE ;
   ELSE
   --În caz contrar - exclude documentul din selecție
     RETURN FALSE ;
    END IF ;
  END IF ;
  --------------------------------

 RETURN FALSE ;
END
$$ LIMBA plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_select( store.docs.id%TYPE  ) OWNER TO store ;
REVOCAȚI EXECUTAREA PE FUNCȚIA store.check_select( store.docs.id%TYPE  ) DE LA public; 
ACORDĂ EXECUTAREA PE FUNCȚIA store.check_select( store.docs.id%TYPE  ) CĂTRE service_functions; 

Verificarea posibilității de a efectua INSERT pe o linie

check_insert

CREAȚI SAU ÎNLOCUIȚI FUNCȚIA store.check_insert ( current_id store.docs.id%TYPE ) RETURNEAZĂ boolean AS $$
DECLARE
  curr_role_id integer ;
BEGIN
  --DBA poate adăuga o linie în orice caz
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Obține id-ul rolului utilizatorului curent 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Dacă rolul permite crearea unui nou document
--permite
IF curr_role_id = 3 OR curr_role_id = 5     
THEN
  RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE  ;
END
$$ LIMBA plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) OWNER TO store ;
REVOCAȚI EXECUTAREA PE FUNCȚIA store.check_insert( store.docs.id%TYPE  ) DE LA public;
ACORDĂ EXECUTAREA PE FUNCȚIA store.check_insert( store.docs.id%TYPE  ) CĂTRE service_functions; 

Verificarea posibilității de a efectua DELETE pe o linie

check_delete

CREAȚI SAU ÎNLOCUIȚI FUNCȚIA store.check_delete ( current_id store.docs.id%TYPE )
RETURNEAZĂ boolean AS $$
BEGIN  
  --Numai DBA poate șterge o linie 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  RETURN FALSE ;
END
$$ LIMBA plpgsql
SECURITY DEFINER;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE  ) OWNER TO store ;
REVOCAȚI EXECUTAREA PE FUNCȚIA store.check_delete( store.docs.id%TYPE  ) DE LA public;

Verificarea posibilității de a efectua UPDATE pe o linie.

actualizare_utilizare

CREATE OR REPLACE FUNCTION store.actualizare_utilizare ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
BEGIN  
   --Documentele cu statut 'șters' nu pot fi editate
   IF is_del 
   THEN
     RETURN FALSE ;
 ELSE
    RETURN TRUE ;
  END IF ;

END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.actualizare_utilizare(  store.docs.id%TYPE ,  boolean  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.actualizare_utilizare(  store.docs.id%TYPE ,  boolean  ) FROM public;
GRANT EXECUTE ON FUNCTION store.actualizare_utilizare( store.docs.id%TYPE  ) TO service_functions;

verificare_actualizare

CREATE OR REPLACE FUNCTION store.actualizare_cu_verificare ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
DECLARE
  current_rid integer ;
  current_statid integer ;
BEGIN                

  --DBA poate vizualiza rândul 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Obține id-ul rolului utilizatorului curent 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------                            

 --Ștergerea documentului - modificarea semnului 
 IF is_deleted
 THEN
   --Dacă rolul utilizatorului ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      INTO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      --Documentul în statutul *** nu poate fi șters 
      IF current_status_id = 11
      THEN
         RETURN FALSE ;
      ELSE
      --Se poate șterge documentul în alte staturi
        RETURN TRUE ;
      END IF ;

    --Altfel, dacă rolul utilizatorului ***
    ELSIF current_role_id = 5            
    THEN
      --Toate staturile documentului 
      RETURN TRUE ;
    ELSE
      --Alți utilizatori nu pot șterge documente
      RETURN FALSE ;
    END IF ;
 ELSE      
   --Actualizarea documentului este permisă
    RETURN TRUE ;
END IF ;

RETURN FALSE ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.actualizare_cu_verificare( storg.docs.id%TYPE ,  boolean   ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.actualizare_cu_verificare( storg.docs.id%TYPE ,  boolean   )  FROM public;
GRANT EXECUTE ON FUNCTION store.actualizare_cu_verificare( store.docs.id%TYPE  ) TO service_functions;

Activarea politicii de Securitate pe Nivel de Rând pentru tabel.

ACTIVAȚI SECURITATEA PE NIVEL DE RÂND

ALTER TABLE store.docs ACTIVAȚI SECURITATEA PE NIVEL DE RÂND ;

CREATE POLICY doc_select ON store.docs FOR SELECT TO service_functions USING ( (SELECT store.check_select(id)) );
CREATE POLICY doc_insert ON store.docs FOR INSERT TO service_functions WITH CHECK ( (SELECT store.check_insert(id)) );
CREATE POLICY docs_delete ON store.docs FOR DELETE TO service_functions USING ( (SELECT store.check_delete(id)) );

CREATE POLICY doc_update_using ON store.docs FOR UPDATE TO service_functions USING ( (SELECT store.actualizare_utilizare(id , is_del )) );
CREATE POLICY doc_update_check ON store.docs FOR UPDATE TO service_functions  WITH CHECK ( (SELECT store.actualizare_cu_verificare(id , is_del )) );

Rezultatul

Funcționează.

Strategia propusă a permis transferarea implementării modelului de roluri de la nivelul funcțiilor de afaceri la nivelul stocării datelor.

Funcțiile pot fi utilizate ca șablon pentru implementarea unor modele mai sofisticate de ascundere a datelor, dacă cerințele de afaceri impun acest lucru.

Sursa: habr.com