🥇Implementierung des Rollenmodells für den Zugriff mit Row Level Security in PostgreSQL

Themenentwicklung Studie zur Umsetzung von Row Level Security in PostgreSQL und für eine ausführliche Antwort findet man Kommentar.

Die verwendete Strategie basiert auf dem Konzept der "Business-Logik in der Datenbank", was hier etwas ausführlicher beschrieben wurde — Studie zur Implementierung von Geschäftslogik auf der Ebene von PostgreSQL-Stored Functions

Der theoretische Teil ist hervorragend in der Dokumentation dargestellt Postgres Pro — Zeilenvergabepolitiken. Im Folgenden wird die praktische Umsetzung einer spezifischen Geschäftsanfrage – ein Rollenmodell für den Datenzugriff – betrachtet.

Der Artikel bietet nichts Neues, keine versteckten Bedeutungen oder geheimen Kenntnisse. Es ist einfach eine Skizze zur praktischen Umsetzung einer theoretischen Idee. Wer interessiert ist – lesen Sie weiter. Wer nicht interessiert ist – vergeuden Sie Ihre Zeit nicht.

Problemstellung

Es ist notwendig, den Zugriff auf die Anzeige/Einfügung/Änderung/Löschung von Dokumenten entsprechend der Rolle des Benutzers der Anwendung zu trennen. Mit Rolle ist der Eintrag in der Tabelle roles gemeint, der in einer Viele-zu-Viele-Beziehung mit der Tabelle steht users. Details zur Implementierung der Tabellen wurden aus Gründen der Trivialisierung weggelassen. Ebenso wurden spezifische Implementierungsdetails, die mit dem Anwendungsgebiet verbunden sind, ausgelassen.

Implementierung

Wir erstellen Rollen, Schemas, Tabellen

Erstellung von DB-Objekten

ROLLEN erstellen store;
SCHEMA store AUTHORIZATION store erstellen;
TABELLE store.docs erstellen
(
  id integer ,         --ID des Dokuments
  man_id integer , --ID des Dokumentenmanagers
  stat_id integer ,  --ID des Dokumentenstatus
  ...
  is_del BOOLEAN STANDARD FALSE 
);
ALTER TABELLE store.docs HINZUFÜGEN EINSCHRÄNKUNG doc_pk PRIMARY KEY (id);
ALTER TABELLE store.docs EIGENTÜMER ZU store ;

Funktionen zur Umsetzung von RLS erstellen

Überprüfen, ob SELECT für die Zeile ausgeführt werden kann

check_select

CREATE OR REPLACE FUNCTION store.check_select ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
  result boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- Der 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 ist oder kein Manager zugewiesen ist
 -- Dokument in die Auswahl aufnehmen
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE  ;
 ELSE
   -- Aktuellen Status des Dokuments abrufen
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Wenn der Status es erlaubt, das Dokument anzuzeigen - Dokument in die Auswahl aufnehmen                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE ;
   ELSE
   --Andernfalls - Dokument aus der Auswahl ausschließen
     RETURN FALSE ;
    END IF ;
  END IF ;
  --------------------------------

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

Überprüfung der Möglichkeit, einen INSERT-Befehl auszuführen

check_insert

CREATE OR REPLACE FUNCTION store.check_insert ( current_id store.docs.id%TYPE ) RETURNS boolean AS $$
DECLARE
  curr_role_id integer ;
BEGIN
  --Der DBA kann in jedem Fall eine Zeile hinzufügen
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Holen Sie sich die ID der Rolle des aktuellen Benutzers 
 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
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) TO service_functions;

Überprüfung der Möglichkeit, einen DELETE-Befehl auszuführen

check_delete

CREATE OR REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )
RETURNS boolean AS $$
BEGIN  
  --Nur der DBA kann eine Zeile löschen 
  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  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE  ) FROM public;

Überprüfung der Möglichkeit, einen UPDATE-Befehl auszuführen.

update_using

CREATE OR REPLACE FUNCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean  )
RETURNS boolean AS $$
BEGIN  
   --Dokumente mit dem Status 'löschen' können nicht bearbeitet werden
   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 kann die Zeile einsehen 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

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

 --Dokument löschen - Änderungskennzeichen 
 IF is_deleted
 THEN
   --Wenn die Benutzerrolle ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      INTO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      --Dokument im Status *** kann nicht gelöscht werden 
      IF current_status_id = 11
      THEN
         RETURN FALSE ;
      ELSE
      --Dokument in anderen Status kann gelöscht werden
        RETURN TRUE ;
      END IF ;

    --Sonst, wenn die Benutzerrolle ***
    ELSIF current_role_id = 5            
    THEN
      --Alle Status des Dokuments 
      RETURN TRUE ;
    ELSE
      --Andere Benutzer dürfen Dokumente nicht löschen
      RETURN FALSE ;
    END IF ;
 ELSE      
   --Dokumentaktualisierung erlaubt
    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;

Aktivierung der Row Level Security für die Tabelle.

ROW LEVEL SECURITY AKTIVIEREN

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

Zusammenfassung

Das funktioniert.

Die vorgeschlagene Strategie ermöglicht es, das Rollenkonzept von der Ebene der Geschäftsprozesse auf die Ebene der Datenspeicherung zu verlagern.

Funktionen können als Vorlage für die Implementierung ausgefeilterer Datenmaskierungsmodelle verwendet werden, wenn dies von den Geschäftsanforderungen gefordert wird.

Quelle: habr.com