Implementering av en rollebasert tilgangsmodell ved hjelp av Row Level Security i PostgreSQL

Utvikling av tema Etude om implementering av Row Level Security i PostgreSQL og for et utfyllende svar En funksjon har blitt kommentar.

Den brukte strategien innebærer konseptet «Forretningslogikk i DB», som ble beskrevet litt mer detaljert her — Etude om implementering av forretningslogikk på nivået av lagrede funksjoner i PostgreSQL

Teoretisk del er godt beskrevet i dokumentasjonen Postgres Pro — Rettighetsbeskyttelsespolitikk for rader. Nedenfor er den praktiske implementeringen vurdert av en bestemt forretningsoppgave — en rollemodell for datatilgang.

Artikkelen inneholder ingenting nytt, ingen skjulte betydninger eller hemmelig kunnskap. Bare en skisse over den praktiske implementeringen av en teoretisk idé. Hvis noen er interessert — les. Hvis ikke — ikke kast bort tiden din.

Definering av oppgaven

Det er nødvendig å skille mellom tilgang til visning/insetting/endring/sletting av dokumentet i henhold til brukerens rolle i applikasjonen. Med rolle menes en post i tabellen roles relatert til mange-til-mange-forhold med tabellen users. Detaljer om implementeringen av tabellene er utelatt på grunn av trivielle grunner. Spesifikke detaljer knyttet til fagområdet er også utelatt.

Implementering

Vi oppretter roller, skjema, tabellen

Oppretting av DB-objekter

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

Vi lager funksjoner for implementering av RLS

Sjekk muligheten til å utføre SELECT på raden

check_select

OPPRETT FUNKSJON store.check_select ( current_id store.docs.id%TYPE ) RETURNER boolean AS $$
DECLARE
  result boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- DBA har tilgang til alle dokumenter
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  --Hvis dokumentet har merkelapp 'slettet' - ikke vis i utvalget
  SELECT
    is_del
  INTO
    result
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF result = TRUE
 THEN
   RETURN FALSE ;
 END IF ;
 --------------------------------

 --Få id til aktuell bruker
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Få id til dokumentets manager
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Hvis dokumentets manager ikke er aktuell bruker eller manager ikke er utnevnt
 --legge dokumentet til i utvalget
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE  ;
 ELSE
   --Få nåværende status for dokumentet
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Hvis status tillater visning av dokumentet - legg til dokumentet i utvalget                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE ;
   ELSE
   --Ellers - ekskluder dokumentet fra utvalget
     RETURN FALSE ;
    END IF ;
  END IF ;
  --------------------------------

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

Sjekk muligheten for å utføre INSERT av rad

check_insert

OPPRETT FUNKSJON store.check_insert ( current_id store.docs.id%TYPE ) RETURNER boolean AS $$
DECLARE
  curr_role_id integer ;
BEGIN
  --DBA kan alltid legge til en rad
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Få id til rollen til aktuell bruker 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Hvis rollen tillater opprettelse av nytt dokument
--tillat
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  ) EIER TIL store ;
REVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) FRA public;
GRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) TIL service_functions; 

Sjekk muligheten for å utføre DELETE av rad

check_delete

OPPRETT FUNKSJON store.check_delete ( current_id store.docs.id%TYPE )
RETURNER boolean AS $$
BEGIN  
  --Bare DBA kan slette en rad 
  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  ) EIER TIL store ;
REVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE  ) FRA public;

Sjekk muligheten for å utføre UPDATE av rad.

oppdatere_bruk

OPPRETT ELLER BYTT UT FUNKSJON store.oppdatere_bruk ( nåværende_id store.docs.id%TYPE , er_del boolean )
RETURNER boolean AS $$
BEGIN  
   --Dokumenter med status 'slettet' kan ikke redigeres
   HVIS er_del 
   DA
     RETURNER FALSE ;
 ELLERS
    RETURNER TRUE ;
  SLUTT HVIS ;

SLUTT
$$ SPRÅK plpgsql SIKKERHET DEFINERER;
ENDRE FUNKSJON store.oppdatere_bruk( store.docs.id%TYPE ,  boolean  ) EIER TIL store ;
FJERN UTFØRELSE PÅ FUNKSJON store.oppdatere_bruk( store.docs.id%TYPE ,  boolean  ) FRA offentlig;
GI UTFØRELSE PÅ FUNKSJON store.oppdatere_bruk( store.docs.id%TYPE  ) TIL service_functions;

oppdatere_sjekk

OPPRETT ELLER BYTT UT FUNKSJON store.oppdatere_med_sjekk ( nåværende_id store.docs.id%TYPE , er_del boolean )
RETURNER boolean AS $$
DECLARE
  nåværende_rid integer ;
  nåværende_statid integer ;
BEGIN                

  --DBA kan se raden 
  HVIS SESJON_BRUKER = 'curr_dba'
  DA
    RETURNER TRUE ;
  SLUTT HVIS ;
  --------------------------------

 --Hent id for rollen til den nåværende brukeren 
 SELECT
   service_functions.nåværende_rid()
  INN I
    curr_role_id ;
 --------------------------------                            

 --Sletting av dokument - endring av flagg 
 HVIS er_slettet
 DA
   --Hvis brukerens rolle ***
   HVIS nåværende_rolle_id = 3        
   DA
      SELECT
        stat_id                                          
      INN I
        curr_statid
      FRA
        store.docs
      HVOR
        id = nåværende_id ;

      --Dokumentet med status *** kan ikke slettes 
      HVIS nåværende_status_id = 11
      DA
         RETURNER FALSE ;
      ELLERS
      --Kan slette dokumentet i andre statuser
        RETURNER TRUE ;
      SLUTT HVIS ;

    --Ellers, hvis brukerens rolle ***
    ELLER HVIS nåværende_rolle_id = 5            
    DA
      --Alle statusene til dokumentet 
      RETURNER TRUE ;
    ELLER
      --Andre brukere kan ikke slette dokumenter
      RETURNER FALSE ;
    SLUTT HVIS ;
 ELLERS      
   --Oppdatering av dokumentet er tillatt
    RETURNER TRUE ;
SLUTT HVIS ;

RETURNER FALSE ;
SLUTT
$$ SPRÅK plpgsql SIKKERHET DEFINERER;
ENDRE FUNKSJON store.oppdatere_med_sjekk( storg.docs.id%TYPE ,  boolean   ) EIER TIL store ;
FJERN UTFØRELSE PÅ FUNKSJON store.oppdatere_med_sjekk( storg.docs.id%TYPE ,  boolean   ) FRA offentlig;
GI UTFØRELSE PÅ FUNKSJON store.oppdatere_med_sjekk( store.docs.id%TYPE  ) TIL service_functions;

Aktivering av Row Level Security-policy for tabellen.

AKTIVER ROW LEVEL SECURITY

ENDRE TABELL store.docs AKTIVER ROW LEVEL SECURITY ;

OPPRETT POLICY doc_select PÅ store.docs FOR SELEKTERING TIL service_functions BRUKER ( (SELECT store.check_select(id)) );
OPPRETT POLICY doc_insert PÅ store.docs FOR SETTING TIL service_functions MED SJEKK ( (SELECT store.check_insert(id)) );
OPPRETT POLICY docs_delete PÅ store.docs FOR SLETTING TIL service_functions BRUKER ( (SELECT store.check_delete(id)) );

OPPRETT POLICY doc_update_using PÅ store.docs FOR OPPDATERING TIL service_functions BRUKER ( (SELECT store.oppdatere_bruk(id , er_del )) );
OPPRETT POLICY doc_update_check PÅ store.docs FOR OPPDATERING TIL service_functions MED SJEKK ( (SELECT store.oppdatere_med_sjekk(id , er_del )) );

Resultat

Det fungerer.

Den foreslåtte strategien gjorde det mulig å flytte implementeringen av rollemodellen fra nivået for forretningsfunksjoner til nivået for datalagring.

Funksjoner kan brukes som en mal for implementering av mer sofistikerte modeller for dataskjuling, hvis forretningskravene tilsier det.

Kilde: habr.com