Estudio sobre la implementación de Row Level Security en PostgreSQL

Como complemento a Estudio sobre la implementación de la lógica de negocio en funciones almacenadas de PostgreSQL y principalmente para una respuesta detallada en comentario.

La parte teórica está excelentemente descrita en la documentación Postgres Pro — Políticas de protección de filas. A continuación se presenta la implementación práctica de una pequeña tarea empresarial concreta: ocultar datos eliminados. Este estudio se dedica a la implementación del modelo de roles utilizando RLS presentado por separado.

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

Sin profundizar en el área temática, brevemente, la tarea puede formularse de la siguiente manera: hay una tabla que implementa una cierta entidad de negocio. Las filas de la tabla pueden ser eliminadas, pero no se pueden eliminar físicamente, deben ser ocultadas.

Porque se dice: "No elimines nada, solo renombra. Internet guarda TODO"

Además, es deseable no reescribir las funciones almacenadas existentes que operan con esta entidad.

Para implementar este concepto, la tabla tiene un atributo is_deleted. Luego, todo es simple: debe hacerse de manera que el cliente pueda ver solo las filas donde el atributo is_deleted es falso. Para eso se utiliza el mecanismo Row Level Security.

Implementación

Creamos un rol y un esquema separados

CREATE ROLE repos;
CREATE SCHEMA repos;

Creamos la tabla objetivo

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

Activamos Row Level Security

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 de negocio — 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 del documento no ve

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

Pero en la base de datos el documento no se ha eliminado, solo se ha cambiado el atributo is_del

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

Lo que se requería en la formulación de la tarea.

Summary

Si el tema es interesante, en el próximo estudio se puede mostrar un ejemplo de implementación de un modelo de roles para la separación de acceso a los datos utilizando Row Level Security.

Fuente: habr.com