Mise en œuvre du modèle de contrôle d'accès basé sur les lignes avec Row Level Security dans PostgreSQL

Développement du sujet Étude sur la mise en œuvre de la sécurité au niveau des lignes dans PostgreSQL et pour une réponse développée sur commentaire.

La stratégie utilisée implique le concept de « Logique métier dans la BDD », qui a été décrit plus en détail ici — Étude sur la mise en œuvre de la logique métier au niveau des fonctions stockées PostgreSQL

La partie théorique est bien décrite dans la documentation Postgres Pro — Politiques de protection des lignes. Ci-dessous, nous examinons la mise en œuvre pratique d'un problème commercial spécifique — un modèle d'accès basé sur les rôles.

L'article n'apporte rien de nouveau, il n'y a pas de sens caché ni de connaissances secrètes. Juste un aperçu de la mise en œuvre pratique d'une idée théorique. Si cela vous intéresse, lisez. Si cela ne vous intéresse pas, ne perdez pas votre temps.

Définition du problème

Il est nécessaire de limiter l'accès à la consultation/insertion/modification/suppression d'un document en fonction du rôle de l'utilisateur de l'application. Par rôle, nous entendons une entrée dans la table roles qui est liée par une relation plusieurs-à-plusieurs avec la table users. Les détails de la mise en œuvre des tables, en raison de leur trivialité, sont omis. De même, les détails spécifiques de mise en œuvre liés au domaine sont également omis.

Mise en œuvre

Créons des rôles, des schémas, une table

Création d'objets BDD

CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
  id integer ,         --id du document
  man_id integer , --id du gestionnaire du document
  stat_id integer ,  --id du statut du document
  ...
  is_del BOOLEAN DEFAULT FALSE 
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;

Créons des fonctions pour implémenter RLS

Vérification de la possibilité d'exécuter SELECT sur la ligne

check_select

CRÉER OU REMPLACER LA FONCTION store.check_select ( current_id store.docs.id%TYPE ) RETOURNE boolean AS $$
DÉCLARE
  result boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
DÉBUT 
  -- Le DBA a accès à tous les documents
  SI SESSION_USER = 'curr_dba'
  ALORS
    RETOURNER VRAI ;
  FIN SI ;
  --------------------------------

  -- Si le document a l'étiquette 'supprimé' - ne pas afficher dans la sélection
  SÉLECTIONNER
    is_del
  DANS
    result
  DE
    store.docs
  OÙ
    id = current_id ;
 SI result = VRAI
 ALORS
   RETOURNER FAUX ;
 FIN SI ;
 --------------------------------

 -- Obtenir l'id de l'utilisateur actuel
 SÉLECTIONNER
   service_function.get_curr_pid ()
 DANS
   curr_pid ;
 --------------------------------

 -- Obtenir l'id du gestionnaire de document
 SÉLECTIONNER
   man_id
 DANS
   doc_man_id
 DE
   store.docs
  OÙ
   id = current_id ;
 --------------------------------

 -- Si le gestionnaire de document n'est pas l'utilisateur actuel ou si le gestionnaire n'est pas assigné
 -- ajouter le document à la sélection
 SI doc_man_id != curr_pid OU doc_man_id EST NULL
 ALORS
   RETOURNER VRAI  ;
 SINON
   -- Obtenir le statut actuel du document
   SÉLECTIONNER
     stat_id                                         
   DANS
     curr_statid
   DE
     store.docs
   OÙ
     id = current_id ;
    
   -- Si le statut permet de voir le document - ajouter le document à la sélection                     
   SI curr_statid = 4 OU curr_statid = 9
   ALORS
     RETOURNER VRAI ;
   SINON
   -- Sinon - exclure le document de la sélection
     RETOURNER FAUX ;
    FIN SI ;
  FIN SI ;
  --------------------------------

 RETOURNER FAUX ;
FIN
$$ LANGAGE plpgsql DÉFINI PAR LA SÉCURITÉ ;
ALTER FUNCTION store.check_select( store.docs.id%TYPE  ) PROPRIÉTAIRE À store ;
REVOQUER L'EXÉCUTION SUR LA FONCTION store.check_select( store.docs.id%TYPE  ) DE public; 
ACCORDER L'EXÉCUTION SUR LA FONCTION store.check_select( store.docs.id%TYPE  ) À service_functions; 

Vérification de la possibilité d'exécuter un INSERT de ligne

check_insert

CRÉER OU REMPLACER LA FONCTION store.check_insert ( current_id store.docs.id%TYPE ) RETOURNE boolean AS $$
DÉCLARE
  curr_role_id integer ;
DÉBUT
  -- Le DBA peut ajouter une ligne dans tous les cas
  SI SESSION_USER = 'curr_dba'
  ALORS
    RETOURNER VRAI ;
  FIN SI ;
  --------------------------------

 -- Obtenir l'id du rôle de l'utilisateur actuel 
 SÉLECTIONNER
   service_functions.current_rid()
  DANS
    curr_role_id ;
 --------------------------------

-- Si le rôle permet de créer un nouveau document
-- autoriser
SI curr_role_id = 3 OU curr_role_id = 5     
ALORS
  RETOURNER VRAI ;
FIN SI ;
--------------------------------
RETOURNER FAUX  ;
FIN
$$ LANGAGE plpgsql DÉFINI PAR LA SÉCURITÉ ;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) PROPRIÉTAIRE À store ;
REVOQUER L'EXÉCUTION SUR LA FONCTION store.check_insert( store.docs.id%TYPE  ) DE public;
ACCORDER L'EXÉCUTION SUR LA FONCTION store.check_insert( store.docs.id%TYPE  ) À service_functions; 

Vérification de la possibilité d'exécuter un DELETE de ligne

check_delete

CRÉER OU REMPLACER LA FONCTION store.check_delete ( current_id store.docs.id%TYPE )
RETOURNE boolean AS $$
DÉBUT  
  -- Seul le DBA peut supprimer une ligne 
  SI SESSION_USER = 'curr_dba'
  ALORS
    RETOURNER VRAI ;
  FIN SI ;
  --------------------------------

  RETOURNER FAUX ;
FIN
$$ LANGAGE plpgsql
DÉFINI PAR LA SÉCURITÉ ;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE  ) PROPRIÉTAIRE À store ;
REVOQUER L'EXÉCUTION SUR LA FONCTION store.check_delete( store.docs.id%TYPE  ) DE public;

Vérification de la possibilité d'exécuter un UPDATE de ligne.

mise_à_jour_utiliser

CRÉER OU REMPLACER LA FONCTION store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
BEGIN  
   --Les documents ayant le statut 'supprimé' ne peuvent pas être modifiés
   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;

mise_à_jour_vérification

CRÉER OU REMPLACER LA FONCTION store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
RETURNS boolean AS $$
DECLARE
  current_rid integer ;
  current_statid integer ;
BEGIN                

  --Le DBA peut consulter la ligne 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Obtenir l'id du rôle de l'utilisateur actuel 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------                            

 --Suppression du document - modification du flag 
 IF is_deleted
 THEN
   --Si le rôle de l'utilisateur ***
   IF current_role_id = 3        
   THEN
      SELECT
        stat_id                                          
      INTO
        curr_statid
      FROM
        store.docs
      WHERE
        id = current_id ;

      --Le document au statut *** ne peut pas être supprimé 
      IF current_status_id = 11
      THEN
         RETURN FALSE ;
      ELSE
      --Le document peut être supprimé dans d'autres statuts
        RETURN TRUE ;
      END IF ;

    --Sinon, si le rôle de l'utilisateur ***
    ELSIF current_role_id = 5            
    THEN
      --Tous les statuts du document 
      RETURN TRUE ;
    ELSE
      --D'autres utilisateurs ne peuvent pas supprimer des documents
      RETURN FALSE ;
    END IF ;
 ELSE      
   --La mise à jour du document est autorisée
    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;

Activation de la politique de sécurité au niveau des lignes pour la table.

ACTIVER LA SÉCURITÉ AU NIVEAU DES LIGNES

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

Conclusion

Cela fonctionne.

La stratégie proposée a permis de transférer la mise en œuvre du modèle de rôle du niveau des fonctions commerciales au niveau du stockage des données.

Les fonctions peuvent être utilisées comme modèle pour mettre en œuvre des modèles de masquage de données plus sophistiqués, si les exigences commerciales l'exigent.

Source : habr.com