Implementering af rollebaseret adgangsmodel ved hjælp af Row Level Security i PostgreSQL

Udvikling af emnet Studie af implementering af Row Level Security i PostgreSQL og til et udførligt svar og få en fungerende kommando. kommentar.

Den anvendte strategi indebærer brug af konceptet "Forretningslogik i databasen", som er beskrevet lidt nærmere her — Studie af implementering af forretningslogik på niveau med gemte funktioner i PostgreSQL

Den teoretiske del er fremragende beskrevet i dokumentationen Postgres Pro — Rækkebeskyttelsespolitikker. Nedenfor gennemgås den praktiske implementering af en specifik forretningsopgave — en rollebaseret model for adgang til data.

I artiklen er der ikke noget nyt, ingen skjult mening eller hemmelig viden. Bare en skitse af praktisk implementering af en teoretisk idé. Hvis nogen er interesseret - læs. Hvis ikke - spild ikke din tid.

Problemformulering

Det er nødvendigt at begrænse adgangen til at se/indsætte/ændre/slette dokumentet i overensstemmelse med applikationsbrugerens rolle. Med rolle menes der en post i tabellen roles forbundet med et mange-til-mange-forhold til tabellen brugere. Detaljerne om implementeringen af tabellerne er udeladt af trivialitet. Specifikke detaljer om implementeringen relateret til emneområdet er også udeladt.

Implementering

Vi opretter roller, skemaer, tabellen

Oprettelse af databaseobjekter

CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
  id integer ,         --id på dokumentet
  man_id integer , --id på dokumentets manager
  stat_id integer ,  --id på 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 opretter funktioner for implementering af RLS

Kontrol af muligheden for at udføre SELECT på rækken

check_select

OPRET ELLER ERSTAT FUNKTION store.check_select ( current_id store.docs.id%TYPE ) RETURNERER boolean AS $$
ERKLÆR
  resultat boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- DBA har adgang til alle dokumenter
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  -- Hvis dokumentet har mærket 'slettet', skal det ikke vises i udvælgelsen
  VÆLG
    is_del
  IN I
    store.docs
  HVOR
    id = current_id ;
 IF resultat = TRUE
 THEN
   RETURN FALSE ;
 END IF ;
 --------------------------------

 -- Få id for den aktuelle bruger
 VÆLG
   service_function.get_curr_pid ()
 IN I
   curr_pid ;
 --------------------------------

 -- Få id for dokumentets manager
 VÆLG
   man_id
 IN I
   store.docs
  HVOR
   id = current_id ;
 --------------------------------

 -- Hvis dokumentets manager ikke er den aktuelle bruger, eller manageren ikke er tildelt
 -- tilføj dokumentet i udvælgelsen
 IF doc_man_id != curr_pid ELLER doc_man_id ER NULL
 THEN
   RETURN TRUE  ;
 ELSE
   -- Få den aktuelle status for dokumentet
   VÆLG
     stat_id                                         
   IN I
     store.docs
   HVOR
     id = current_id ;
    
   -- Hvis status tillader at se dokumentet - tilføj dokumentet i udvælgelsen                     
   IF curr_statid = 4 ELLER curr_statid = 9
   THEN
     RETURN TRUE ;
   ELLERS
   -- Ellers - udeluk dokumentet fra udvælgelsen
     RETURN FALSE ;
    END IF ;
  END IF ;
  --------------------------------

 RETURN FALSE ;
END
$$ SPROG plpgsql SIKKERHED DEFINER;
ALTER FUNCTION store.check_select( store.docs.id%TYPE  ) EJER TIL store ;
FJERN EXECUTE PÅ FUNKTION store.check_select( store.docs.id%TYPE  ) FRA public; 
GIV EXECUTE PÅ FUNKTION store.check_select( store.docs.id%TYPE  ) TIL service_functions; 

Kontrol af muligheden for at udføre INSERT af en række

check_insert

OPRET ELLER ERSTAT FUNKTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNERER boolean AS $$
ERKLÆR
  curr_role_id integer ;
BEGIN
  -- DBA kan altid tilføje en række
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 -- Få id for den aktuelle brugers rolle 
 VÆLG
   service_functions.current_rid()
  IN I
    curr_role_id ;
 --------------------------------

-- Hvis rollen tillader oprettelse af et nyt dokument
-- tillad
IF curr_role_id = 3 ELLER curr_role_id = 5     
THEN
  RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE  ;
END
$$ SPROG plpgsql SIKKERHED DEFINER;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) EJER TIL store ;
FJERN EXECUTE PÅ FUNKTION store.check_insert( store.docs.id%TYPE  ) FRA public;
GIV EXECUTE PÅ FUNKTION store.check_insert( store.docs.id%TYPE  ) TIL service_functions; 

Kontrol af muligheden for at udføre DELETE af en række

check_delete

OPRET ELLER ERSTAT FUNKTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNERER boolean AS $$
BEGIN  
  -- Kun DBA kan slette en række 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  RETURN FALSE ;
END
$$ SPROG plpgsql
SIKKERHED DEFINER;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE  ) EJER TIL store ;
FJERN EXECUTE PÅ FUNKTION store.check_delete( store.docs.id%TYPE  ) FRA public;

Kontrol af muligheden for at udføre UPDATE af en række.

update_using

CREATE OR REPLACE FUNCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean  )
RETURNS boolean AS $$
BEGIN  
   --Dokumenter med status 'slettet' kan ikke redigeres
   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  ) 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

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 kan se rækken 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Få id for den aktuelle brugers rolle 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------                            

 --Sletning af dokument - ændrer status 
 IF is_deleted
 THEN
   --Hvis brugerens rolle ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      INTO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      --Dokumentet i status *** kan ikke slettes 
      IF current_status_id = 11
      THEN
         RETURN FALSE ;
      ELSE
      --Dokumentet kan slettes i andre statusser
        RETURN TRUE ;
      END IF ;

    --Ellers, hvis brugerens rolle ***
    ELSIF current_role_id = 5            
    THEN
      --Alle dokumentstatusser 
      RETURN TRUE ;
    ELSE
      --Andre brugere kan ikke slette dokumenter
      RETURN FALSE ;
    END IF ;
 ELSE      
   --Opdatering af dokumentet er tilladt
    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;

Aktivering af Row Level Security-politik for tabellen.

ENABLE ROW LEVEL SECURITY

ALTER TABLE store.docs ENABLE ROW LEVEL SECURITY ;

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 )) );

Resultat

Dette fungerer.

Den foreslåede strategi gjorde det muligt at overføre implementeringen af rollemønstret fra forretningsfunktionsniveau til datalagringsniveauet.

Funktioner kan bruges som en skabelon til implementering af mere raffinerede dataskjulingsmodeller, hvis forretningskravene kræver det.

Kilde: habr.com