🥇Implementierung des Rollenzugriffsmodells unter Verwendung von Row Level Security in PostgreSQL

Entwicklung des Themas Étude zur Implementierung von Row Level Security in PostgreSQL und für eine umfassende Antwort auf Kommentar.

Die verwendete Strategie basiert auf dem Konzept "Business-Logik in der Datenbank", was hier etwas ausführlicher beschrieben wurde - Studienbericht über die Implementierung der Geschäftslogik auf Ebene von gespeicherten Funktionen in PostgreSQL

Der theoretische Teil ist in der Dokumentation hervorragend beschrieben Postgres Pro — Datensicherheitspolitiken. Im Folgenden wird die praktische Umsetzung einer spezifischen Geschäftsanforderung - das rollenbasierte Zugriffsmodell - behandelt.

Der Artikel enthält nichts Neues, es gibt keine versteckte Bedeutung oder geheimes Wissen. Es ist einfach eine Skizze zur praktischen Umsetzung einer theoretischen Idee. Wer interessiert ist — lesen Sie. Wer nicht interessiert ist — verschwenden Sie nicht Ihre Zeit.

Aufgabenstellung

Es ist notwendig, den Zugriff auf das Anzeigen / Einfügen / Ändern / Löschen von Dokumenten entsprechend der Rolle des Benutzers der Anwendung zu unterscheiden. Unter einer Rolle wird ein Eintrag in der Tabelle verstanden roles die durch eine viele-zu-viele Beziehung mit der Tabelle verbunden ist. users. Die Details zur Implementierung der Tabellen wurden aus Gründen der Einfachheit ausgelassen. Ebenso wurden spezifische Implementierungsdetails in Bezug auf den Anwendungsbereich weggelassen.

Implementierung

Wir erstellen Rollen, Schemata, eine Tabelle

Erstellung von Datenbankobjekten

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

Wir erstellen Funktionen zur Implementierung von RLS

Überprüfung der Möglichkeit, eine Zeile auszuwählen

check_select

ERSTELLEN ODER ERSETZEN DER FUNKTION store.check_select ( current_id store.docs.id%TYPE ) GIBT BOOLEAN ZURÜCK AS $$
DECLARE
  result BOOLEAN;
  curr_pid INTEGER;
  curr_stat_id INTEGER;
  doc_man_id INTEGER;
BEGIN
  -- DBA hat Zugriff auf alle Dokumente
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE;
  END IF;
  --------------------------------

  --Wenn das Dokument das Label 'gelöscht' hat, nicht in der Auswahl anzeigen
  SELECT
    is_del
  INTO
    result
  FROM
    store.docs
  WHERE
    id = current_id;
 IF result = TRUE
 THEN
   RETURN FALSE;
 END IF;
 --------------------------------

 --Aktuelle Benutzer-ID abrufen
 SELECT
   service_function.get_curr_pid()
 INTO
   curr_pid;
 --------------------------------

 --ID des Dokumentenmanagers abrufen
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id;
 --------------------------------

 --Wenn der Dokumentenmanager nicht der aktuelle Benutzer oder kein Manager zugewiesen ist
 --das Dokument zur Auswahl hinzufügen
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE;
 ELSE
   --Aktuellen Dokumentenstatus abrufen
   SELECT
     stat_id                                        
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id;
    
   --Wenn der Status das Ansehen des Dokuments erlaubt - Dokument zur Auswahl hinzufügen                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE;
   ELSE
   --Sonst - Dokument von der Auswahl ausschließen
     RETURN FALSE;
    END IF;
  END IF;
  --------------------------------

 RETURN FALSE;
END
$$ SPRACHE plpgsql SICHERHEITSDEFINIERER;
ÄNDERN FUNKTION store.check_select( store.docs.id%TYPE ) EIGENTÜMER ZU store;
WIDERRUFEN AUSFÜHRUNG FÜR FUNKTION store.check_select( store.docs.id%TYPE ) VON public;
Gewähren Sie AUSFÜHRUNG FÜR FUNKTION store.check_select( store.docs.id%TYPE ) AN service_functions;

Überprüfung der Möglichkeit, eine Zeile INSERT zu erstellen

check_insert

ERSTELLEN ODER ERSETZEN DER FUNKTION store.check_insert ( current_id store.docs.id%TYPE ) GIBT BOOLEAN ZURÜCK AS $$
DECLARE
  curr_role_id INTEGER;
BEGIN
  --DBA kann in jedem Fall eine Zeile hinzufügen
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE;
  END IF;
  --------------------------------

 --ID der Rolle des aktuellen Benutzers abrufen 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id;
 --------------------------------

--Wenn die Rolle die Erstellung eines neuen Dokuments erlaubt
--erlauben
IF curr_role_id = 3 OR curr_role_id = 5     
THEN
  RETURN TRUE;
END IF;
--------------------------------
RETURN FALSE;
END
$$ SPRACHE plpgsql SICHERHEITSDEFINIERER;
ÄNDERN FUNKTION store.check_insert( store.docs.id%TYPE ) EIGENTÜMER ZU store;
WIDERRUFEN AUSFÜHRUNG FÜR FUNKTION store.check_insert( store.docs.id%TYPE ) VON public;
Gewähren Sie AUSFÜHRUNG FÜR FUNKTION store.check_insert( store.docs.id%TYPE ) AN service_functions;

Überprüfung der Möglichkeit, eine Zeile DELETE auszuführen

check_delete

ERSTELLEN ODER ERSETZEN DER FUNKTION store.check_delete ( current_id store.docs.id%TYPE )
GIBT BOOLEAN ZURÜCK AS $$
BEGIN  
  --Nur DBA kann eine Zeile löschen 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE;
  END IF;
  --------------------------------

  RETURN FALSE;
END
$$ SPRACHE plpgsql
SICHERHEITSDEFINIERER;
ÄNDERN FUNKTION store.check_delete( store.docs.id%TYPE ) EIGENTÜMER ZU store;
WIDERRUFEN AUSFÜHRUNG FÜR FUNKTION store.check_delete( store.docs.id%TYPE ) VON public;

Überprüfung der Möglichkeit, eine Zeile UPDATE auszuführen.

update_using

ERSTELLEN ODER ERSETZEN FUNKTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
GIBT boolean ZURÜCK AS $$
BEGIN  
   --Dokumente mit dem Status ' gelöscht ' können nicht bearbeitet werden
   WENN is_del 
   DANN
     ZURÜCKGEBEN FALSE ;
 SONST
    ZURÜCKGEBEN TRUE ;
  END IF ;

END
$$ SPRACHE plpgsql SICHERHEITSDEFINIERER;
ALTERE FUNKTION store.update_using( store.docs.id%TYPE , boolean ) EIGENTÜMER ZU store ;
WIDERRUFEN AUSFÜHRUNG AUF FUNKTION store.update_using( store.docs.id%TYPE , boolean ) VON public;
GEWÄHREN AUSFÜHRUNG AUF FUNKTION store.update_using( store.docs.id%TYPE ) AN service_functions;

update_check

ERSTELLEN ODER ERSETZEN FUNKTION store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
GIBT boolean ZURÜCK AS $$
DEKLARIEREN
  current_rid integer ;
  current_statid integer ;
BEGIN                

  --DBA kann Zeile anzeigen 
  WENN SESSION_USER = 'curr_dba'
  DANN
    ZURÜCKGEBEN TRUE ;
  END IF ;
  --------------------------------

 --Aktuelle Benutzerrolle abrufen 
 SELECT
   service_functions.current_rid()
  IN
er	curr_role_id ;
 --------------------------------                            

 --Löschung des Dokuments - Status ändern 
 WENN is_deleted
 DANN
   --Wenn Benutzerrolle ***
   WENN current_role_id = 3        
   DANN
      SELECT
        stat_id                                          
      IN
er	curr_statid
      VON
        store.docs
      WO
        id = current_id ;

      --Dokument im Status *** kann nicht gelöscht werden 
      WENN current_status_id = 11
      DANN
         ZURÜCKGEBEN FALSE ;
      SONST
      --Dokument in anderen Status kann gelöscht werden
        ZURÜCKGEBEN TRUE ;
      END IF ;

    --Sonst, wenn Benutzerrolle ***
    ELSIF current_role_id = 5            
    DANN
      --Alle Dokumentstatus 
      ZURÜCKGEBEN TRUE ;
    SONST
      --Andere Benutzer können Dokumente nicht löschen
      ZURÜCKGEBEN FALSE ;
    END IF ;
 SONST      
   --Aktualisierung des Dokuments erlaubt
    ZURÜCKGEBEN TRUE ;
END IF ;

ZURÜCKGEBEN FALSE ;
END
$$ SPRACHE plpgsql SICHERHEITSDEFINIERER;
ALTERE FUNKTION store.update_with_check( storg.docs.id%TYPE , boolean ) EIGENTÜMER ZU store ;
WIDERRUFEN AUSFÜHRUNG AUF FUNKTION store.update_with_check( storg.docs.id%TYPE , boolean ) VON public;
GEWÄHREN AUSFÜHRUNG AUF FUNKTION store.update_with_check( store.docs.id%TYPE ) AN service_functions;

Aktivierung der Zeilenebene-Sicherheitsrichtlinie für die Tabelle.

ZEILENEBENE-SICHERHEIT AKTIVIEREN

ALTER TABLE store.docs ZEILENEBENE-SICHERHEIT AKTIVIEREN ;

ERSTELLEN RICHTLINIE doc_select AUF store.docs FÜR SELECT AN service_functions VERWENDEN ( (SELECT store.check_select(id)) );
ERSTELLEN RICHTLINIE doc_insert AUF store.docs FÜR INSERT AN service_functions MIT PRÜFUNG ( (SELECT store.check_insert(id)) );
ERSTELLEN RICHTLINIE docs_delete AUF store.docs FÜR DELETE AN service_functions VERWENDEN ( (SELECT store.check_delete(id)) );

ERSTELLEN RICHTLINIE doc_update_using AUF store.docs FÜR UPDATE AN service_functions VERWENDEN ( (SELECT store.update_using(id , is_del )) );
ERSTELLEN RICHTLINIE doc_update_check AUF store.docs FÜR UPDATE AN service_functions  MIT PRÜFUNG ( (SELECT store.update_with_check(id , is_del )) );

Fazit

Das funktioniert.

Die vorgeschlagene Strategie ermöglichte es, die Implementierung des Rollenmodells von der Ebene der Geschäftsprozesse auf die Ebene der Datenspeicherung zu verlagern.

Funktionen können als Vorlage für die Implementierung ausgeklügelterer Datenverbergungsmodelle dienen, wenn dies die Geschäftserfordernisse verlangen.

Quelle: habr.com