🥇Lomas piekļuves modeļa īstenošana, izmantojot Row Level Security PostgreSQL

Tēmas attīstība Etīde par Row Level Security ieviešanu PostgreSQL un lai sniegtu detalizētu atbildi uz komentārs.

Izmantotā stratēģija paredz «Biznesa loģikas datubāzē» koncepcijas izmantošanu, kas tika nedaudz sīkāk aprakstīta šeit — Studija par biznesa loģikas realizāciju PostgreSQL uzglabāšanas funkciju līmenī

Teorētiskā daļa ir lieliski aprakstīta dokumentācijā Postgres Pro — Rindu aizsardzības politikas. Zemāk apskatīta praktiskā realizācija konkrētai biznesa uzdevumam — piekļuves modeļa izveide datiem.

Rakstā nav nekas jauns, nav slēpta jēga vai noslēpumu. Tā ir vienkārša piezīme par praktiskās realizācijas teorētisko ideju. Ja kādu interesē — lasiet. Kam neinteresē — netērējiet savu laiku velti.

Uzdevuma formulēšana

Nepieciešams nodalīt piekļuvi dokumenta apskatei/ievadei/izmaiņām/dzēšanai atbilstoši lietotāja lomai. Ar lomu saprotams ieraksts tabulā roles kas ir saistīts ar daudzkāršu attiecību ar tabulu lietotāji. Tabulu realizācijas detaļas, ņemot vērā trivialitāti, ir izslēgtas. Arī konkrētās realizācijas detaļas, kas saistītas ar priekšmetu jomu, ir izslēgtas.

Realizācija

Izveidojam lomas, shēmas, tabulu

Datubāzu objektu izveide

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

Izveidojam funkcijas RLS realizācijai

Pārbaude, vai var veikt SELECT rindiņu

check_select

IZVEIDOJAM VAI AIZSTĀJAM FUNKCIJU store.check_select ( current_id store.docs.id%TYPE ) ATGRIEŽ BOOLEAN AS $$
DECLARE
  result BOOLEAN ;
  curr_pid INTEGER ;
  curr_stat_id INTEGER ;
  doc_man_id INTEGER ;
BEGIN 
  -- DBA ir piekļuve visiem dokumentiem
  IF SESSION_USER = 'curr_dba'
  THEN
    ATGRIEŽ TRUE ;
  END IF ;
  --------------------------------

  --Ja dokuments ir apzīmēts ar 'izdzēsts' - nedemonstrēt izvēlē
  SELECT
    is_del
  INTO
    result
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF result = TRUE
 THEN
   ATGRIEŽ FALSE ;
 END IF ;
 --------------------------------

 --Iegūt pašreizējā lietotāja id
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Iegūt dokumenta menedžera id
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Ja dokumenta menedžeris nav pašreizējais lietotājs vai menedžeris nav noteikts
 --pievienot dokumentu izvēlei
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   ATGRIEŽ TRUE  ;
 ELSE
   --Iegūt dokumenta pašreizējo statusu
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Ja statuss atļauj apskatīt dokumentu - pievienot dokumentu izvēlei                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     ATGRIEŽ TRUE ;
   ELSE
   --Pretējā gadījumā - izslēgt dokumentu no izvēles
     ATGRIEŽ FALSE ;
    END IF ;
  END IF ;
  --------------------------------

 ATGRIEŽ FALSE ;
END
$$ VALODA plpgsql DROŠĪBAS DEFINĪTORS;
ALTER FUNCTION store.check_select( store.docs.id%TYPE  ) PIEDER STORE ;
ATŲREVI EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) FROM public; 
PIEŠĶIR EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) TO service_functions;

Pārbaude par iespēju veikt INSERT rindas

check_insert

IZVEIDOJAM VAI AIZSTĀJAM FUNKCIJU store.check_insert ( current_id store.docs.id%TYPE ) ATGRIEŽ BOOLEAN AS $$
DECLARE
  curr_role_id INTEGER ;
BEGIN
  --DBA var pievienot rindu jebkurā gadījumā
  IF SESSION_USER = 'curr_dba'
  THEN
    ATGRIEŽ TRUE ;
  END IF ;
  --------------------------------

 --Iegūt pašreizējā lietotāja lomas id 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Ja loma atļauj jauna dokumenta izveidi
--atļaut
IF curr_role_id = 3 OR curr_role_id = 5     
THEN
  ATGRIEŽ TRUE ;
END IF ;
--------------------------------
ATGRIEŽ FALSE  ;
END
$$ VALODA plpgsql DROŠĪBAS DEFINĪTORS;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) PIEDER STORE ;
ATŲREVI EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) FROM public;
PIEŠĶIR EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) TO service_functions;

Pārbaude par iespēju veikt DELETE rindas

check_delete

IZVEIDOJAM VAI AIZSTĀJAM FUNKCIJU store.check_delete ( current_id store.docs.id%TYPE )
ATGRIEŽ BOOLEAN AS $$
BEGIN  
  --Tikai DBA var dzēst rindu 
  IF SESSION_USER = 'curr_dba'
  THEN
    ATGRIEŽ TRUE ;
  END IF ;
  --------------------------------

  ATGRIEŽ FALSE ;
END
$$ VALODA plpgsql
DROŠĪBAS DEFINĪTORS;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE  ) PIEDER STORE ;
ATŲREVI EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE  ) FROM public;

Pārbaude par iespēju veikt UPDATE rindas.

update_using

IZVEIDOJIET VAI NOSTAČIET FUNKCIJU store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
SĀKT
   --Dokumenti ar statusu 'dzēsts' netiek rediģēti
   JA is_del 
   TAD
     ATGRIEZT FALSE ;
 CITADIEN
    ATGRIEZT TRUE ;
  BEIGAS

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

update_check

IZVEIDOJIET VAI NOSTAČIET FUNKCIJU store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
DECLARE
  current_rid integer ;
  current_statid integer ;
SĀKT                

  --DBA var apskatīt rindu 
  JA SESSION_USER = 'curr_dba'
  TAD
    ATGRIEZT TRUE ;
  BEIGAS
  --------------------------------

 --Iegūt current_rid
 SELECT
   service_functions.current_rid()
  IEVEST
    curr_role_id ;
 --------------------------------                            

 --Dokumenta izdzēšana - statusa maiņa 
 JA is_deleted
 TAD
   --Ja lietotāja loma ***
   JA current_role_id = 3        
   TAD
      SELECT
        stat_id                                          
      IEVEST
        curr_statid
      NO
        store.docs
      KUR
        id = current_id ;

      --Dokuments statusā *** nevar tikt izdzēsts 
      JA current_status_id = 11
      TAD
         ATGRIEZT FALSE ;
      CITADIEN
      --Var izdzēst dokumentu citos statusos
        ATGRIEZT TRUE ;
      BEIGAS

    --Citādi, ja loma ***
    ELSIF current_role_id = 5            
    TAD
      --Visi dokumenta statusi 
      ATGRIEZT TRUE ;
    CITADIEN
      --Citi lietotāji nevar izdzēst dokumentus
      ATGRIEZT FALSE ;
    BEIGAS
 ELSI      
   --Dokumenta atjaunināšana ir atļauta
    ATGRIEZT TRUE ;
BEIGAS

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

Row Level Security politikas aktivizācija tabulai.

IESPĒJOT RINDU LĪMEŅA DROŠĪBU

ALTER TABLE store.docs IESPĒJOT RINDU LĪMEŅA DROŠĪBU ;

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

IZVEIDOT POLITIKU doc_update_using ON store.docs FOR UPDATE TO service_functions USING ( (SELECT store.update_using(id , is_del )) );
IZVEIDOT POLITIKU doc_update_check ON store.docs FOR UPDATE TO service_functions WITH CHECK ( (SELECT store.update_with_check(id , is_del )) );