Implementace modelu řízení přístupu s využitím Row Level Security v PostgreSQL

Rozvoj tématu Studie implementace Row Level Security v PostgreSQL a pro podrobnou odpověď na komentář.

Použitá strategie předpokládá koncept „Podniková logika v DB“, což bylo podrobněji popsáno zde — Studie o implementaci obchodní logiky na úrovni uložených funkcí PostgreSQL

Teoretická část je výborně popsána v dokumentaci Postgres Pro — Politiky ochrany řádků. Níže je uvedena praktická implementace konkrétní obchodní úlohy — role modelu přístupu k datům.

V článku není nic nového, žádný skrytý smysl ani tajné znalosti. Jen skica o praktické realizaci teoretické myšlenky. Pokud má někdo zájem — čtete. Kdo nemá zájem — neztrácejte zbytečně čas.

Formulace úkolu

Je nutné rozlišit přístup k prohlížení/vkládání/změně/odstranění dokumentu podle role uživatele aplikace. Pod rolí se rozumí záznam v tabulce role spojené vztahy mnoha-k-mnohým s tabulkou users. Detaily implementace tabulek, z důvodu triviálnosti, byly vynechány. Také byly vynechány konkrétní detaily implementace týkající se předmětné oblasti.

Implementace

Vytváříme role, schémata, tabulku

Vytvoření objektů DB

CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
  id integer ,         --id dokumentu
  man_id integer , --id manažera dokumentu
  stat_id integer ,  --id stavu dokumentu
  ...
  is_del BOOLEAN DEFAULT FALSE 
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;

Vytváříme funkce pro implementaci RLS

Ověření možnosti provést SELECT řádku

check_select

VYTVOŘIT NEBO NAHRADIT FUNKCI store.check_select ( current_id store.docs.id%TYPE ) VRACÍ boolean AS $$
DECLARE
  výsledek boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- DBA má přístup ke všem dokumentům
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  --Pokud je dokument označen jako 'smazaný' - nezobrazovat v výběru
  SELECT
    is_del
  INTO
    výsledek
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF výsledek = TRUE
 THEN
   RETURN FALSE ;
 END IF ;
 --------------------------------

 --Získat id aktuálního uživatele
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Získat id manažera dokumentu
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Pokud manažer dokumentu není aktuální uživatel nebo manažer není přidělen
 --přidat dokument do výběru
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE  ;
 ELSE
   --Získat aktuální stav dokumentu
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Pokud stav umožňuje zobrazení dokumentu - přidat dokument do výběru                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE ;
   ELSE
   --Jinak - vyloučit dokument z výběru
     RETURN FALSE ;
    END IF ;
  END IF ;
  --------------------------------

 RETURN FALSE ;
END
$$ JAZYK plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_select( store.docs.id%TYPE  ) VLASTNÍK TO store ;
ODEBRAT EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) FROM public; 
UDĚLIT EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) TO service_functions; 

Kontrola možnosti provést INSERT řádku

check_insert

VYTVOŘIT NEBO NAHRADIT FUNKCI store.check_insert ( current_id store.docs.id%TYPE ) VRACÍ boolean AS $$
DECLARE
  curr_role_id integer ;
BEGIN
  --DBA může přidávat řádek za jakýchkoli okolností
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Získat id role aktuálního uživatele 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Pokud role umožňuje možnost vytvoření nového dokumentu
--povolit
IF curr_role_id = 3 OR curr_role_id = 5     
THEN
  RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE  ;
END
$$ JAZYK plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) VLASTNÍK TO store ;
ODEBRAT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) FROM public;
UDĚLIT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) TO service_functions; 

Kontrola možnosti provést DELETE řádku

check_delete

VYTVOŘIT NEBO NAHRADIT FUNKCI store.check_delete ( current_id store.docs.id%TYPE )
VRACÍ boolean AS $$
BEGIN  
  --Pouze DBA může odstranit řádek 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  RETURN FALSE ;
END
$$ JAZYK plpgsql
SECURITY DEFINER;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE  ) VLASTNÍK TO store ;
ODEBRAT EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE  ) FROM public;

Kontrola možnosti provést UPDATE řádku.

aktualizace_použití

VYTVOŘIT NEBO NAHRADIT FUNKCI store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
VRACÍ boolean AS $$
ZAČÍT  
   --Dokumenty se statusem 'smazáno' - nelze upravovat
   IF is_del 
   THEN
     VRÁTIT FALSE ;
 ELSE
    VRÁTIT TRUE ;
  END IF ;

KONEC
$$ JAZYK plpgsql BEZPEČNOSTNÍ DEFINER;
ZMIĚNIT FUNKCI store.update_using( store.docs.id%TYPE ,  boolean  ) VLASTNÍK NA store ;
ODEBRAT PROVÁDĚNÍ NA FUNKCI store.update_using( store.docs.id%TYPE ,  boolean  ) OD public;
UDĚLIT PROVÁDĚNÍ NA FUNKCI store.update_using( store.docs.id%TYPE  ) PRO service_functions;

aktualizace_zkontrolovat

VYTVOŘIT NEBO NAHRADIT FUNKCI store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
VRACÍ boolean AS $$
DECLARE
  current_rid integer ;
  current_statid integer ;
ZAČÍT                

  --DBA může prohlížet řádek 
  IF SESSION_USER = 'curr_dba'
  THEN
    VRÁTIT TRUE ;
  END IF ;
  --------------------------------

 --Získat id role aktuálního uživatele 
 SELECT
   service_functions.current_rid()
  DO
    curr_role_id ;
 --------------------------------                            

 --Smazání dokumentu - změna znaku 
 IF is_deleted
 THEN
   --Pokud je role uživatele ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      DO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      --Dokument ve stavu *** nelze smazat 
      IF current_status_id = 11
      THEN
         VRÁTIT FALSE ;
      ELSE
      --Dokument lze smazat v jiných stavech
        VRÁTIT TRUE ;
      END IF ;

    --Jinak, pokud je role uživatele ***
    ELSIF current_role_id = 5            
    THEN
      --Všechny stavy dokumentu 
      VRÁTIT TRUE ;
    ELSE
      --Jiní uživatelé nemohou dokumenty mazat
      VRÁTIT FALSE ;
    END IF ;
 ELSE      
   --Aktualizace dokumentu povolena
    VRÁTIT TRUE ;
END IF ;

VRÁTIT FALSE ;
END
$$ JAZYK plpgsql BEZPEČNOSTNÍ DEFINER;
ZMIĚNIT FUNKCI store.update_with_check( storg.docs.id%TYPE ,  boolean   ) VLASTNÍK NA store ;
ODEBRAT PROVÁDĚNÍ NA FUNKCI store.update_with_check( storg.docs.id%TYPE ,  boolean   )  OD public;
UDĚLIT PROVÁDĚNÍ NA FUNKCI store.update_with_check( store.docs.id%TYPE  ) PRO service_functions;

Aktivace politiky Row Level Security pro tabulku.

POVOLIT BEZPEČNOST ŘÁDKŮ

ZMIĚNIT TABULKU store.docs POVOLIT BEZPEČNOST ŘÁDKŮ ;

VYTVOŘIT POLITIKU doc_select NA store.docs PRO SELECT PRO service_functions POUŽITÍ ( (SELECT store.check_select(id)) );
VYTVOŘIT POLITIKU doc_insert NA store.docs PRO INSERT PRO service_functions S ODVĚTCHA ( (SELECT store.check_insert(id)) );
VYTVOŘIT POLITIKU docs_delete NA store.docs PRO DELETE PRO service_functions POUŽITÍ ( (SELECT store.check_delete(id)) );

VYTVOŘIT POLITIKU doc_update_using NA store.docs PRO UPDATE PRO service_functions POUŽITÍ ( (SELECT store.update_using(id , is_del )) );
VYTVOŘIT POLITIKU doc_update_check NA store.docs PRO UPDATE PRO service_functions  S ODVĚTCHA ( (SELECT store.update_with_check(id , is_del )) );

Výsledek

To funguje.

Navrhovaná strategie umožnila přenést implementaci modelu rolí z úrovně obchodních funkcí na úroveň ukládání dat.

Funkce mohou být použity jako šablona pro implementaci složitějších modelů skrývání dat, pokud to vyžadují obchodní potřeby.

Zdroj: habr.com