🥇 Rollimudeli rakendamine rea tasandi turvalisuse abil PostgreSQL-is

Teema arendamine Etüüd Row Level Security rakendamisest PostgreSQL-is ja detailse vastuse jaoks järgnevaga kommentaar.

Kasutatud strateegia hõlmab „Ärilogika andmebaasis“ kontseptsiooni, millest on veidi rohkem rääkida siin — Etüüd ärilogika rakendamisest PostgreSQL-i salvestatud funktsioonide tasemel

Teoreetiline osa on hästi kirjeldatud dokumentatsioonis Postgres Pro — Ridade kaitse poliitikad. Allpool on käsitletud praktilist rakendamist konkreetse äriülesande — andmete juurde pääsemise rollimudelit.

Artiklis ei ole midagi uut, ei ole varjatud mõtet ega salajasi teadmisi. Lihtsalt joonistamine teoreetilise idee praktilisest elluviimisest. Kui kedagi huvitab — lugege. Kui ei huvita — ärge kulutage oma aega raisku.

Ülesande seadmine

On vajalik piirata juurdepääsu dokumendi vaatamisele/sisestamisele/muutmisele/kustutamisele vastavalt rakenduse kasutaja rollile. Rolli all mõistetakse kirjet tabelis roles millel on paljusid-mitmeid suhteid tabeliga users. Tabelite rakenduse üksikasjad on jäetud välja nende triviaalsete iseloomu tõttu. Samuti on välja jäetud spetsiifilised rakenduse üksikasjad, mis on seotud teemaga.

Rakendus

Loome rolle, skeeme, tabeli

Andmebaasi objektide loomine

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

Loome funktsioone RLS-i rakendamiseks

Kontrollime võimalust täita SELECT rida

check_select

CREATE OR REPLACE FUNCTION store.check_select ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
  result boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- DBA-l on juurdepääs kõigile dokumentidele
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  --Kui dokument on märgistatud kui 'kustutatud', siis ei kuvata seda valikus
  SELECT
    is_del
  INTO
    result
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF result = TRUE
 THEN
   RETURN FALSE ;
 END IF ;
 --------------------------------

 --Saada praeguse kasutaja id
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Saada dokumendi halduri id
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Kui dokumendi haldur ei ole praegune kasutaja või haldurit ei ole määratud
 --lisatakse dokument valikusse
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE  ;
 ELSE
   --Saada dokumendi praegune staatuse id
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Kui staatuse id lubab dokumenti näha, siis lisatakse dokument valikusse                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE ;
   ELSE
   --Muul juhul - välistada dokument valikust
     RETURN FALSE ;
    END IF ;
  END IF ;
  --------------------------------

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

Kontrolli INSERT-i ridade täitmise võimalust

check_insert

CREATE OR REPLACE FUNCTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
  curr_role_id integer ;
BEGIN
  --DBA võib rida igal juhul lisada
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Hankige praeguse kasutaja rolli ID 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Kui roll lubab uue dokumendi loomist
--luba
IF curr_role_id = 3 OR curr_role_id = 5     
THEN
  RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE  ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) TO service_functions;

Kontrolli DELETE-i ridade täitmise võimalust

check_delete

CREATE OR REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNS boolean AS $$
BEGIN  
  --Ainult DBA võib rida kustutada 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  RETURN FALSE ;
END
$$ LANGUAGE plpgsql
SECURITY DEFINER;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE  ) FROM public;

Kontrolli UPDATE-i ridade täitmise võimalust.

update_using

LOOJA VÕI ASENDAGE FUNKTSIOON store.update_using ( current_id store.docs.id%TYPE , is_del boolean  )
RETURNS boolean AS $$
BEGIN  
   --Dokumendid, mille staatuseks on 'kustutatud' - neid ei tohi muuta
   IF is_del 
   THEN
     RETURN FALSE ;
 ELSE
    RETURN TRUE ;
  END IF ;

END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.update_using(  store.docs.id%TYPE ,  boolean  ) OWNERSHIP 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

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

  --DBA saab rida vaadata 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Saada jumala id praeguse kasutaja jaoks 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------                            

 --Dokumendi kustutamine - staatuse muutmine 
 IF is_deleted
 THEN
   --Kui kasutaja roll ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      INTO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      --Dokument staatuses *** ei tohi kustutada 
      IF current_status_id = 11
      THEN
         RETURN FALSE ;
      ELSE
      --Dokumendi kustutamine on lubatud teistes staatustes
        RETURN TRUE ;
      END IF ;

    --Vastasel juhul, kui kasutaja roll ***
    ELSIF current_role_id = 5            
    THEN
      --Kõik dokumendi staatused 
      RETURN TRUE ;
    ELSE
      --Teised kasutajad ei saa dokumente kustutada
      RETURN FALSE ;
    END IF ;
 ELSE      
   --Dokumendi uuendamine on lubatud
    RETURN TRUE ;
END IF ;

RETURN FALSE ;
END
$$ 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;

Ridade krüptimispoliitika lubamine tabelis.

LUBA RIDA KRÜPTIMIST

ALTER TABLE store.docs LUBA RIDA KRÜPTIMIST ;

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.update_using(id , is_del )) );
CREATE POLICY doc_update_check ON store.docs FOR UPDATE TO service_functions  WITH CHECK ( (SELECT store.update_with_check(id , is_del )) );