Realizacja modelu dostępu w oparciu o Row Level Security w PostgreSQL

Rozwój tematu Studium przypadku implementacji Row Level Security w PostgreSQL i dla rozszerzonej odpowiedzi na komentarz.

Użyta strategia zakłada zastosowanie koncepcji „Logika biznesowa w bazie danych”, co zostało opisane nieco szerzej tutaj — Studium dotyczące wdrażania logiki biznesowej na poziomie funkcji przechowywanych PostgreSQL.

Część teoretyczna jest doskonale opisana w dokumentacji Postgres Pro — Polityka ochrony wierszy. Poniżej omówiono praktyczną realizację konkretnego zadania biznesowego — model dostępu do danych oparty na rolach.

Artykuł nie zawiera nic nowego, nie ma ukrytego sensu ani tajemnej wiedzy. To po prostu szkic dotyczący praktycznej realizacji teoretycznej idei. Jeśli kogoś to interesuje — niech czyta. Kto nie jest zainteresowany — niech nie marnuje swojego czasu.

Sformułowanie zadania

Należy ograniczyć dostęp do przeglądania/wstawiania/zmiany/usuwania dokumentu zgodnie z rolą użytkownika aplikacji. Pod rolą rozumie się wpis w tabeli roles związaną relacją wiele-do-wielu z tabelą users. Szczegóły realizacji tabel z uwagi na trywialność zostały pominięte. Również pominięto konkretne szczegóły realizacji związane z dziedziną.

Realizacja

Tworzymy role, schemy, tabelę

Tworzenie obiektów Bazy Danych

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

Tworzymy funkcje do realizacji RLS

Sprawdzenie możliwości wykonania SELECT wiersza

check_select

UTWÓRZ LUB ZASTĄP FUNKCJĘ store.check_select ( current_id store.docs.id%TYPE ) ZWRACA boolean AS $$
DEKLARUJ
  wynik boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- DBA ma dostęp do wszystkich dokumentów
  IF SESSION_USER = 'curr_dba'
  THEN
    ZWRÓĆ PRAWDA ;
  END IF ;
  --------------------------------

  --Jeśli dokument ma etykietę 'usunięty' - nie pokazuj w wyborze
  SELECT
    is_del
  INTO
    wynik
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF wynik = PRAWDA
 THEN
   ZWRÓĆ FAŁSZ ;
 END IF ;
 --------------------------------

 --Pobierz id aktualnego użytkownika
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Pobierz id menedżera dokumentu
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Jeśli menedżer dokumentu nie jest aktualnym użytkownikiem lub menedżer nie jest przypisany
 --dodaj dokument do wyboru
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   ZWRÓĆ PRAWDA  ;
 ELSE
   --Pobierz aktualny status dokumentu
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Jeśli status pozwala na przeglądanie dokumentu - dodaj dokument do wyboru                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     ZWRÓĆ PRAWDA ;
   ELSE
   --W przeciwnym razie - wyklucz dokument z wyboru
     ZWRÓĆ FAŁSZ ;
    END IF ;
  END IF ;
  --------------------------------

 ZWRÓĆ FAŁSZ ;
END
$$ JĘZYK plpgsql DEFINER SECURITY;
ALTER FUNKCJĘ store.check_select( store.docs.id%TYPE  ) WŁAŚCICIEL DO store ;
ODWOŁAJ WYKONANIE FUNKCJI store.check_select( store.docs.id%TYPE  ) OD public; 
PRZYZNAJ WYKONANIE FUNKCJI store.check_select( store.docs.id%TYPE  ) DO service_functions; 

Sprawdzenie możliwości wykonania INSERT wiersza

check_insert

UTWÓRZ LUB ZASTĄP FUNKCJĘ store.check_insert ( current_id store.docs.id%TYPE ) ZWRACA boolean AS $$
DEKLARUJ
  curr_role_id integer ;
BEGIN
  --DBA może dodać wiersz w każdym przypadku
  IF SESSION_USER = 'curr_dba'
  THEN
    ZWRÓĆ PRAWDA ;
  END IF ;
  --------------------------------

 --Pobierz id roli aktualnego użytkownika 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Jeśli rola pozwala na utworzenie nowego dokumentu
--zezwól
IF curr_role_id = 3 OR curr_role_id = 5     
THEN
  ZWRÓĆ PRAWDA ;
END IF ;
--------------------------------
ZWRÓĆ FAŁSZ  ;
END
$$ JĘZYK plpgsql DEFINER SECURITY;
ALTER FUNKCJĘ store.check_insert( store.docs.id%TYPE  ) WŁAŚCICIEL DO store ;
ODWOŁAJ WYKONANIE FUNKCJI store.check_insert( store.docs.id%TYPE  ) OD public;
PRZYZNAJ WYKONANIE FUNKCJI store.check_insert( store.docs.id%TYPE  ) DO service_functions; 

Sprawdzenie możliwości wykonania DELETE wiersza

check_delete

UTWÓRZ LUB ZASTĄP FUNKCJĘ store.check_delete ( current_id store.docs.id%TYPE )
ZWRACA boolean AS $$
BEGIN  
  --Tylko DBA może usunąć wiersz 
  IF SESSION_USER = 'curr_dba'
  THEN
    ZWRÓĆ PRAWDA ;
  END IF ;
  --------------------------------

  ZWRÓĆ FAŁSZ ;
END
$$ JĘZYK plpgsql
DEFINER SECURITY;
ALTER FUNKCJĘ store.check_delete( store.docs.id%TYPE  ) WŁAŚCICIEL DO store ;
ODWOŁAJ WYKONANIE FUNKCJI store.check_delete( store.docs.id%TYPE  ) OD public;

Sprawdzenie możliwości wykonania UPDATE wiersza.

aktualizuj_używając

STWÓRZ LUB ZASTĄP FUNKCJĘ store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
ZWRAĆ BOOL AS $$
BEGIN  
   --Dokumenty mające status 'usunięty' nie mogą być edytowane
   IF is_del 
   THEN
     ZWRÓĆ FAŁSZ ;
 ELSE
    ZWRÓĆ PRAWDA ;
  END IF ;

END
$$ JĘZYK plpgsql BEZPIECZNOŚĆ DEFINER;
ZMIANA FUNKCJI store.update_using( store.docs.id%TYPE , boolean ) WŁAŚCICIEL DO store ;
ODWOLAJ WYKONANIE FUNKCJI store.update_using( store.docs.id%TYPE , boolean ) Z public;
PRZYZNAJ WYKONANIE FUNKCJI store.update_using( store.docs.id%TYPE ) DO service_functions;

aktualizuj_sprawdź

STWÓRZ LUB ZASTĄP FUNKCJĘ store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
ZWRAĆ BOOL AS $$
DECLARE
  current_rid integer ;
  current_statid integer ;
BEGIN                

  --DBA może przeglądać wiersz 
  IF SESSION_USER = 'curr_dba'
  THEN
    ZWRÓĆ PRAWDA ;
  END IF ;
  --------------------------------

 --Uzyskaj id roli bieżącego użytkownika 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------                            

 --Usunięcie dokumentu - zmiana flagi 
 IF is_deleted
 THEN
   --Jeśli rola użytkownika ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      INTO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      --Dokument w statusie *** nie może być usunięty 
      IF current_status_id = 11
      THEN
         ZWRÓĆ FAŁSZ ;
      ELSE
      --Można usunąć dokument w innych statusach
        ZWRÓĆ PRAWDA ;
      END IF ;

    --W przeciwnym razie, jeśli rola użytkownika ***
    ELSIF current_role_id = 5            
    THEN
      --Wszystkie statusy dokumentu 
      ZWRÓĆ PRAWDA ;
    ELSE
      --Inni użytkownicy nie mogą usuwać dokumentów
      ZWRÓĆ FAŁSZ ;
    END IF ;
 ELSE      
   --Aktualizacja dokumentu jest dozwolona
    ZWRÓĆ PRAWDA ;
END IF ;

ZWRÓĆ FAŁSZ ;
END
$$ JĘZYK plpgsql BEZPIECZNOŚĆ DEFINER;
ZMIANA FUNKCJI store.update_with_check( storg.docs.id%TYPE ,  boolean ) WŁAŚCICIEL DO store ;
ODWOLAJ WYKONANIE FUNKCJI store.update_with_check( storg.docs.id%TYPE ,  boolean )  Z public;
PRZYZNAJ WYKONANIE FUNKCJI store.update_with_check( store.docs.id%TYPE ) DO service_functions;

Włączenie polityki Row Level Security dla tabeli.

WŁĄCZ RÓW LEVEL SECURITY

ZMIANA TABELI store.docs WŁĄCZ RÓW LEVEL SECURITY ;

STWÓRZ POLITYKĘ doc_select NA store.docs DLA WYBORU DO service_functions UŻYWAJĄC ( (SELECT store.check_select(id)) );
STWÓRZ POLITYKĘ doc_insert NA store.docs DLA WSTAWIANIA DO service_functions Z KONTROLĄ ( (SELECT store.check_insert(id)) );
STWÓRZ POLITYKĘ docs_delete NA store.docs DLA USUNIĘCIA DO service_functions UŻYWAJĄC ( (SELECT store.check_delete(id)) );

STWÓRZ POLITYKĘ doc_update_using NA store.docs DLA AKTUALIZACJI DO service_functions UŻYWAJĄC ( (SELECT store.update_using(id , is_del )) );
STWÓRZ POLITYKĘ doc_update_check NA store.docs DLA AKTUALIZACJI DO service_functions Z KONTROLĄ ( (SELECT store.update_with_check(id , is_del )) );

Podsumowanie

To działa.

Zaproponowana strategia umożliwiła przeniesienie realizacji modelu ról z poziomu funkcji biznesowych na poziom przechowywania danych.

Funkcje mogą być używane jako szablon do wdrażania bardziej złożonych modeli ukrywania danych, jeśli wymaga tego zapotrzebowanie biznesowe.

Źródło: habr.com