Genomförande av en rollbaserad åtkomstmodell med hjälp av Row Level Security i PostgreSQL

Utveckling av ämnet Studie om genomförande av Row Level Security i PostgreSQL och för ett utförligt svar till kommentar.

Den använda strategin innebär konceptet "Affärslogik i databasen", vilket beskrivits mer detaljerat här — Studie om genomförande av affärslogik på nivån av lagrade funktioner i PostgreSQL

Den teoretiska delen är utmärkt beskriven i dokumentationen Postgres Pro — Radernas skyddspolicyer. Nedan diskuteras en praktisk tillämpning av en specifik affärsuppgift — en rollbaserad åtkomstmodell till data.

Artikeln innehåller ingenting nytt, inga dolda betydelser eller hemliga insikter. Det är bara en skiss av praktisk tillämpning av en teoretisk idé. Om någon är intresserad — läs. Om inte — slösa inte din tid.

Uppgiftsställande

Det är nödvändigt att begränsa åtkomst till att visa/infoga/ändra/ta bort dokument i enlighet med användarens roll i applikationen. Med roll avses en post i tabellen roles kopplad med en många-till-många-relation till tabellen users. Detaljer om genomförande av tabeller utelämnas på grund av trivialitet. Specifika detaljer relaterade till ämnesområdet har också utelämnats.

Implementering

Vi skapar roller, scheman och tabeller

Skapande av databasobjekt

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

Vi skapar funktioner för att genomföra RLS

Kontroll av möjligheten att utföra SELECT på en rad

check_select

SKAPA ELLER ERSÄTT FUNKTION store.check_select ( current_id store.docs.id%TYPE ) RETURNERAR boolean AS $$
DECLARERA
  resultat boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BÖRJA 
  -- DBA har åtkomst till alla dokument
  IF SESSION_USER = 'curr_dba'
  DÅ
    RETURERA TRUE ;
  SLUTA ;
  --------------------------------

  --Om dokumentet har etiketten 'borttaget' - visa inte i urvalet
  VÄLJ
    is_del
  IN
    resultat
  FRÅN
    store.docs
  WHERE
    id = current_id ;
 IF resultat = TRUE
 THEN
   RETURERA FALSE ;
 SLUTA ;
 --------------------------------

 --Hämta id för nuvarande användare
 VÄLJ
   service_function.get_curr_pid ()
 IN
   curr_pid ;
 --------------------------------

 --Hämta id för dokumentets ansvarige
 VÄLJ
   man_id
 IN
   doc_man_id
 FRÅN
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Om dokumentets ansvarige inte är nuvarande användare eller ansvarig inte är angiven
 --lägg till dokumentet i urvalet
 IF doc_man_id != curr_pid OR doc_man_id ÄR NULL
 THEN
   RETURERA TRUE  ;
 ANNARS
   --Hämta dokumentets nuvarande status
   VÄLJ
     stat_id                                         
   IN
     curr_statid
   FRÅN
     store.docs
   WHERE
     id = current_id ;
    
   --Om status tillåter att dokumetet visas - lägg till dokumentet i urvalet                     
   IF curr_statid = 4 ELLER curr_statid = 9
   DÅ
     RETURERA TRUE ;
   ANNARS
   --Annars - uteslut dokumentet från urvalet
     RETURERA FALSE ;
    SLUTA ;
  SLUTA ;
  --------------------------------

 RETURERA FALSE ;
SLUTA
$$ SPRÅK plpgsql SECURITY DEFINER;
ALTERERA FUNKTION store.check_select( store.docs.id%TYPE  ) ÄGARE TILL store ;
ÅTERKALLA UTFÖRANDE PÅ FUNKTION store.check_select( store.docs.id%TYPE  ) FRÅN public; 
BEVAKA UTFÖRANDE PÅ FUNKTION store.check_select( store.docs.id%TYPE  ) TILL service_functions; 

Kontrollera möjligheten att utföra INSERT av en rad

check_insert

SKAPA ELLER ERSÄTT FUNKTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNERAR boolean AS $$
DECLARERA
  curr_role_id integer ;
BÖRJA
  --DBA kan alltid lägga till en rad
  IF SESSION_USER = 'curr_dba'
  DÅ
    RETURERA TRUE ;
  SLUTA ;
  --------------------------------

 --Hämta id för nuvarande användarroll 
 VÄLJ
   service_functions.current_rid()
  IN
    curr_role_id ;
 --------------------------------

--Om rollen tillåter att skapa ett nytt dokument
--tillåt
IF curr_role_id = 3 ELLER curr_role_id = 5     
DÅ
  RETURERA TRUE ;
SLUTA ;
--------------------------------
RETURERA FALSE  ;
SLUTA
$$ SPRÅK plpgsql SECURITY DEFINER;
ALTERERA FUNKTION store.check_insert( store.docs.id%TYPE  ) ÄGARE TILL store ;
ÅTERKALLA UTFÖRANDE PÅ FUNKTION store.check_insert( store.docs.id%TYPE  ) FRÅN public;
BEVAKA UTFÖRANDE PÅ FUNKTION store.check_insert( store.docs.id%TYPE  ) TILL service_functions; 

Kontrollera möjligheten att utföra DELETE av en rad

check_delete

SKAPA ELLER ERSÄTT FUNKTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNERAR boolean AS $$
BÖRJA  
  --Endast DBA kan ta bort en rad 
  IF SESSION_USER = 'curr_dba'
  DÅ
    RETURERA TRUE ;
  SLUTA ;
  --------------------------------

  RETURERA FALSE ;
SLUTA
$$ SPRÅK plpgsql
SECURITY DEFINER;
ALTERERA FUNKTION store.check_delete( store.docs.id%TYPE  ) ÄGARE TILL store ;
ÅTERKALLA UTFÖRANDE PÅ FUNKTION store.check_delete( store.docs.id%TYPE  ) FRÅN public;

Kontrollera möjligheten att utföra UPDATE av en rad.

uppdatera_använder

SKAPA ELLER ERSÄTT FUNKTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean SOM $$
BÖRJA  
   --Dokumenten med status 'borttagen' - kan inte redigeras
   OM is_del 
   DÅ
     RETURN FALSE ;
 ANNARS
    RETURN TRUE ;
  SLUT OM ;

SLUT
$$ SPRÅK plpgsql SÄKERHETSFÖRDELARE;
ALTER FUNKTION store.update_using( store.docs.id%TYPE , boolean ) ÄGARE TILL store ;
ÅTERKALLA KÖRNING PÅ FUNKTION store.update_using( store.docs.id%TYPE , boolean ) FRÅN offentlig;
GE KÖRNING PÅ FUNKTION store.update_using( store.docs.id%TYPE ) TILL service_functions;

uppdatera_kontroll

SKAPA ELLER ERSÄTT FUNKTION store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean SOM $$
DEKLARERA
  current_rid heltal ;
  current_statid heltal ;
BÖRJA                

  --DBA kan visa raden
  OM SESSION_USER = 'curr_dba'
  DÅ
    RETURN TRUE ;
  SLUT OM ;
  --------------------------------

 --Hämta id för rollen för den aktuella användaren
 VÄLJ
   service_functions.current_rid()
  IN
    curr_role_id ;
 --------------------------------                            

 --Borttagning av dokument - ändra indikeringsflagga
 OM is_deleted
 DÅ
   --Om användarrollen ***
   OM current_role_id = 3        
   DÅ
      VÄLJ
        stat_id                                          
      IN
        curr_statid
      FRÅN
        store.docs
      VARFÖR
        id = current_id ;

      --Dokument i status *** kan inte tas bort
      OM current_status_id = 11
      DÅ
         RETURN FALSE ;
      ANNARS
      --Kan ta bort dokument i andra statusar
        RETURN TRUE ;
      SLUT OM ;

    --Annars, om användarrollen ***
    ELSIF current_role_id = 5            
    DÅ
      --Alla dokumentstatusar
      RETURN TRUE ;
    ANNARS
      --Andra användare kan inte ta bort dokument
      RETURN FALSE ;
    SLUT OM ;
 ANNARS      
   --Uppdatering av dokumentet är tillåten
    RETURN TRUE ;
SLUT OM ;

RETURN FALSE ;
SLUT
$$ SPRÅK plpgsql SÄKERHETSFÖRDELARE;
ALTER FUNKTION store.update_with_check( store.docs.id%TYPE , boolean ) ÄGARE TILL store ;
ÅTERKALLA KÖRNING PÅ FUNKTION store.update_with_check( store.docs.id%TYPE , boolean ) FRÅN offentlig;
GE KÖRNING PÅ FUNKTION store.update_with_check( store.docs.id%TYPE ) TILL service_functions;

Aktivera politiken för radnivåsäkerhet för tabellen.

AKTIVERA RADNIVÅSÄKERHET

ALTER TABELL store.docs AKTIVERA RADNIVÅSÄKERHET ;

SKAPA POLICY doc_select PÅ store.docs FÖR SELECT TILL service_functions ANVÄND ( (VÄLJ store.check_select(id)) );
SKAPA POLICY doc_insert PÅ store.docs FÖR INSERT TILL service_functions MED KONTROLL ( (VÄLJ store.check_insert(id)) );
SKAPA POLICY docs_delete PÅ store.docs FÖR DELETE TILL service_functions ANVÄND ( (VÄLJ store.check_delete(id)) );

SKAPA POLICY doc_update_using PÅ store.docs FÖR UPDATE TILL service_functions ANVÄND ( (VÄLJ store.update_using(id , is_del )) );
SKAPA POLICY doc_update_check PÅ store.docs FÖR UPDATE TILL service_functions MED KONTROLL ( (VÄLJ store.update_with_check(id , is_del )) );

Resultat

Det fungerar.

Den föreslagna strategin möjliggjorde överföringen av implementeringen av rollmodellen från affärsfunktionsnivån till datalagringsnivån.

Funktioner kan användas som mall för att implementera mer avancerade modeller för dataskydd, om affärskraven kräver det.

Källa: habr.com