Un estudio sobre la implementación de seguridad de nivel de fila en PostgreSQL

Como complemento a Un estudio sobre la implementación de lógica de negocios a nivel de funciones almacenadas de PostgreSQL и principalmente para una respuesta detallada en comentario.

La parte teórica está bien descrita en la documentación. PostgreSQL - Políticas de protección de filas. A continuación se muestra una implementación práctica de un pequeño tarea empresarial específica: ocultar datos eliminados. Boceto dedicado a la implementación. Modelado de roles utilizando RLS presentado por separado.

No hay nada nuevo en el artículo, no hay ningún significado oculto ni conocimiento secreto. Sólo un esbozo sobre la implementación práctica de una idea teórica. Si a alguien le interesa que lo lea. Si no estás interesado, no pierdas el tiempo.

Formulación del problema

Sin profundizar en el área temática, brevemente, el problema se puede formular de la siguiente manera: Hay una tabla que implementa una determinada entidad comercial. Las filas de la tabla se pueden eliminar, pero no se pueden eliminar físicamente; deben estar ocultas.

Porque se dice: “No borres nada, simplemente cámbiale el nombre. Internet almacena TODO"

En el camino, es aconsejable no reescribir las funciones almacenadas existentes que funcionan con esta entidad.

Para implementar este concepto, la tabla tiene el atributo esta borrado. Entonces todo es simple: debe asegurarse de que el cliente solo pueda ver las líneas en las que se encuentra el atributo. esta borrado FALSO ¿Para qué se utiliza el mecanismo? Seguridad a nivel de fila.

implementación

Crear un rol y un esquema separados

CREATE ROLE repos;
CREATE SCHEMA repos;

Crear la tabla de destino

CREATE TABLE repos.file
(
...
is_del BOOLEAN DEFAULT FALSE
);
CREATE SCHEMA repos

Encender Seguridad a nivel de fila

ALTER TABLE repos.file  ENABLE ROW LEVEL SECURITY ;
CREATE POLICY file_invisible_deleted  ON repos.file FOR ALL TO dba_role USING ( NOT is_deleted );
GRANT ALL ON TABLE repos.file to dba_role ;
GRANT USAGE ON SCHEMA repos TO dba_role ;

Función de servicio — eliminar una fila en la tabla

CREATE OR REPLACE repos.delete( curr_id repos.file.id%TYPE)
RETURNS integer AS $$
BEGIN
...
UPDATE repos.file
SET is_del = TRUE 
WHERE id = curr_id ; 
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

Función empresarial — eliminar un documento

CREATE OR REPLACE business_functions.deleteDoc( doc_for_delete JSON )
RETURNS JSON AS $$
BEGIN
...
PERFORM  repos.delete( doc_id ) ;
...
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

resultados

El cliente elimina el documento.

SELECT business_functions.delCFile( (SELECT json_build_object( 'CId', 3 )) );

Después de la eliminación, el cliente no ve el documento.

SELECT business_functions.getCFile"( (SELECT json_build_object( 'CId', 3 )) ) ;
-----------------
(0 rows)

Pero en la base de datos no se elimina el documento, solo se cambia el atributo es_del

psql -d my_db
SELECT  id, name , is_del FROM repos.file ;
id |  name  | is_del
--+---------+------------
 1 |  test_1 | t
(1 row)

Que es lo que se requería en el planteamiento del problema.

Total

Si el tema es interesante, en el próximo estudio puede mostrar un ejemplo de implementación de un modelo basado en roles para separar el acceso a datos usando Row Level Security.

Fuente: habr.com