Implementación de un modelo de acceso basado en roles utilizando Row Level Security en PostgreSQL

Desarrollo del tema Estudio sobre la implementación de Row Level Security en PostgreSQL y para una respuesta ampliada en comentario.

La estrategia utilizada implica la utilización del concepto de 'Lógica de negocio en la base de datos', que se describe con más detalle aquí — Estudio sobre la implementación de la lógica de negocio en funciones almacenadas de PostgreSQL

La parte teórica está excelentemente descrita en la documentación Postgres Pro — Políticas de protección de filas. A continuación, se analiza la implementación práctica de una tarea empresarial específica: un modelo de acceso a datos basado en roles.

El artículo no contiene nada nuevo, no hay significados ocultos ni conocimientos secretos. Simplemente es un bosquejo sobre la implementación práctica de una idea teórica. Si a alguien le interesa, que lea. Si no le interesa, no pierda su tiempo en vano.

Planteamiento del problema

Es necesario delimitar el acceso para ver/insertar/modificar/eliminar documentos según el rol del usuario de la aplicación. Por rol, se entiende un registro en la tabla roles relacionada con la tabla mediante una relación de muchos a muchos. usuariosLos detalles de la implementación de las tablas se omiten debido a su trivialidad. También se omiten detalles específicos de implementación relacionados con el dominio.

Implementación

Creamos roles, esquemas, tabla

Creación de objetos de la base de datos

CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
  id integer ,         --id del documento
  man_id integer , --id del gerente del documento
  stat_id integer ,  --id del estado del documento
  ...
  is_del BOOLEAN DEFAULT FALSE 
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;

Creamos funciones para implementar RLS

Comprobación de la posibilidad de realizar SELECT en la fila

check_select

CREAR O REEMPLAZAR FUNCIÓN store.check_select ( current_id store.docs.id%TYPE ) DEVUELVE boolean AS $$
DECLARE
  resultado boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- DBA tiene acceso a todos los documentos
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  --Si el documento tiene la etiqueta 'eliminado' - no mostrar en la selección
  SELECT
    is_del
  INTO
    resultado
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF resultado = TRUE
 THEN
   RETURN FALSE ;
 END IF ;
 --------------------------------

 --Obtener id del usuario actual
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Obtener id del gerente del documento
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Si el gerente del documento no es el usuario actual o no hay gerente asignado
 --incluir documento en la selección
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE  ;
 ELSE
   --Obtener el estado actual del documento
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Si el estado permite ver el documento - incluir documento en la selección                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE ;
   ELSE
   --De otro modo - excluir documento de la selección
     RETURN FALSE ;
    END IF ;
  END IF ;
  --------------------------------

 RETURN FALSE ;
END
$$ LENGUAJE plpgsql DEFINICIÓN DE SEGURIDAD;
ALTERAR FUNCIÓN store.check_select( store.docs.id%TYPE  ) PROPIETARIO A store ;
REVOCAR EJECUTAR EN FUNCIÓN store.check_select( store.docs.id%TYPE  ) DE publico; 
OTORGAR EJECUTAR EN FUNCIÓN store.check_select( store.docs.id%TYPE  ) A service_functions; 

Comprobación de la posibilidad de realizar un INSERT de fila

check_insert

CREAR O REEMPLAZAR FUNCIÓN store.check_insert ( current_id store.docs.id%TYPE ) DEVUELVE boolean AS $$
DECLARE
  curr_role_id integer ;
BEGIN
  --DBA puede añadir una fila en cualquier caso
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Obtener id del rol del usuario actual 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Si el rol permite la creación de un nuevo documento
--permitir
IF curr_role_id = 3 OR curr_role_id = 5     
THEN
  RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE  ;
END
$$ LENGUAJE plpgsql DEFINICIÓN DE SEGURIDAD;
ALTERAR FUNCIÓN store.check_insert( store.docs.id%TYPE  ) PROPIETARIO A store ;
REVOCAR EJECUTAR EN FUNCIÓN store.check_insert( store.docs.id%TYPE  ) DE publico;
OTORGAR EJECUTAR EN FUNCIÓN store.check_insert( store.docs.id%TYPE  ) A service_functions; 

Comprobación de la posibilidad de realizar un DELETE de fila

check_delete

CREAR O REEMPLAZAR FUNCIÓN store.check_delete ( current_id store.docs.id%TYPE )
DEVUELVE boolean AS $$
BEGIN  
  --Solo DBA puede eliminar la fila 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  RETURN FALSE ;
END
$$ LENGUAJE plpgsql
DEFINICIÓN DE SEGURIDAD;
ALTERAR FUNCIÓN store.check_delete( store.docs.id%TYPE  ) PROPIETARIO A store ;
REVOCAR EJECUTAR EN FUNCIÓN store.check_delete( store.docs.id%TYPE  ) DE publico;

Comprobación de la posibilidad de realizar un UPDATE de fila.

actualizar_usando

CREAR O REEMPLAZAR FUNCIÓN store.actualizar_usando ( current_id store.docs.id%TYPE , is_del boolean  )
RETORNA boolean AS $$
COMIENZA  
   --Los documentos con estado 'eliminado' no se pueden editar
   SI is_del 
   ENTONCES
     RETORNAR FALSE ;
 SINO
    RETORNAR TRUE ;
  FIN SI ;

FIN
$$ LENGUAJE plpgsql DEFINIDOR DE SEGURIDAD;
ALTERAR FUNCIÓN store.actualizar_usando(  store.docs.id%TYPE ,  boolean  ) PROPIETARIO A store ;
REVOCAR EJECUCIÓN EN FUNCIÓN store.actualizar_usando(  store.docs.id%TYPE ,  boolean  ) DE público;
OTORGAR EJECUCIÓN EN FUNCIÓN store.actualizar_usando( store.docs.id%TYPE  ) A service_functions;

actualizar_verificar

CREAR O REEMPLAZAR FUNCIÓN store.actualizar_con_verificación ( current_id store.docs.id%TYPE , is_del boolean )
RETORNA boolean AS $$
DECLARAR
  current_rid entero ;
  current_statid entero ;
COMIENZA                

  --El DBA puede ver la fila 
  SI SESSION_USER = 'curr_dba'
  ENTONCES
    RETORNAR TRUE ;
  FIN SI ;
  --------------------------------

 --Obtener id del rol del usuario actual 
 SELECCIONAR
   service_functions.current_rid()
  EN INTO
    curr_role_id ;
 --------------------------------                            

 --Eliminación del documento - cambio de marca 
 SI is_deleted
 ENTONCES
   --Si el rol del usuario ***
   SI current_role_id = 3        
   ENTONCES
      SELECCIONAR
        stat_id                                          
      EN INTO
        curr_statid
      DE
        store.docs
      DONDE
        id = current_id ;

      --El documento en estado *** no se puede eliminar 
      SI current_status_id = 11
      ENTONCES
         RETORNAR FALSE ;
      SINO
      --Se puede eliminar el documento en otros estados
        RETORNAR TRUE ;
      FIN SI ;

    --De lo contrario, si el rol del usuario ***
    O SINO current_role_id = 5            
    ENTONCES
      --Todos los estados del documento 
      RETORNAR TRUE ;
    O SINO
      --Otros usuarios no pueden eliminar documentos
      RETORNAR FALSE ;
    FIN SI ;
 SINO      
   --La actualización del documento está permitida
    RETORNAR TRUE ;
FIN SI ;

RETORNAR FALSE ;
FIN
$$ LENGUAJE plpgsql DEFINIDOR DE SEGURIDAD;
ALTERAR FUNCIÓN store.actualizar_con_verificación( storg.docs.id%TYPE ,  boolean   ) PROPIETARIO A store ;
REVOCAR EJECUCIÓN EN FUNCIÓN store.actualizar_con_verificación( storg.docs.id%TYPE ,  boolean   ) DE público;
OTORGAR EJECUCIÓN EN FUNCIÓN store.actualizar_con_verificación( store.docs.id%TYPE  ) A service_functions;

Activación de la política de Seguridad a Nivel de Fila para la tabla.

ACTIVAR SEGURIDAD A NIVEL DE FILA

ALTERAR TABLA store.docs ACTIVAR SEGURIDAD A NIVEL DE FILA ;

CREAR POLÍTICA doc_seleccionar EN store.docs PARA SELECCIONAR A service_functions USANDO ( (SELECCIONAR store.check_select(id)) );
CREAR POLÍTICA doc_insertar EN store.docs PARA INSERTAR A service_functions CON VERIFICACIÓN ( (SELECCIONAR store.check_insert(id)) );
CREAR POLÍTICA docs_eliminar EN store.docs PARA ELIMINAR A service_functions USANDO ( (SELECCIONAR store.check_delete(id)) );

CREAR POLÍTICA doc_actualizar_usando EN store.docs PARA ACTUALIZAR A service_functions USANDO ( (SELECCIONAR store.actualizar_usando(id , is_del )) );
CREAR POLÍTICA doc_actualizar_verificar EN store.docs PARA ACTUALIZAR A service_functions  CON VERIFICACIÓN ( (SELECCIONAR store.actualizar_con_verificación(id , is_del )) );

Summary

Esto funciona.

La estrategia propuesta permitió trasladar la implementación del modelo de roles del nivel de funciones comerciales al nivel de almacenamiento de datos.

Las funciones pueden ser utilizadas como plantilla para implementar modelos más sofisticados de ocultamiento de datos, si así lo requieren las exigencias comerciales.

Fuente: habr.com