Estudio de implementación de la seguridad a nivel de fila en PostgreSQL

Como complemento del Etude sobre la implementación de la lógica empresarial a nivel de funciones almacenadas de PostgreSQL y principalmente para una respuesta detallada al comentario .



La teoría está bien descrita en la documentación de PostgreSQL - Políticas de seguridad de filas . A continuación se muestra una implementación práctica de una pequeña tarea empresarial específica: ocultar los datos eliminados. Se presenta por separado un estudio sobre la implementación del modelo a seguir utilizando RLS .

El artículo no es nada nuevo, no hay ningún significado oculto ni conocimiento secreto. Solo un boceto sobre la implementación práctica de una idea teórica. Si alguien está interesado, siga leyendo. Quién no está interesado, no pierda el tiempo.

Formulación del problema

Sin profundizar en el área temática, brevemente, la tarea se puede formular de la siguiente manera: existe una tabla que implementa una determinada entidad empresarial. Las filas de la tabla se pueden eliminar, pero no puede eliminar físicamente las filas, debe ocultarlas.



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



A lo largo del camino, es aconsejable no volver a escribir las funciones almacenadas existentes que funcionan con esta entidad.



Para implementar este concepto, la tabla tiene el atributo is_deleted . Entonces todo es simple: debe hacerlo de modo que el cliente pueda ver solo las líneas en las que el atributo is_deleted sea falso. Para qué se utiliza el mecanismo de seguridad de nivel de fila.

Implementación

Cree una función y un esquema separados

CREATE ROLE repos;
CREATE SCHEMA repos;

Crea la tabla de destino

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

Activar la seguridad de 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 una 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 : eliminación de documentos

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 el documento no se ha eliminado en la base de datos, 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)

Esto es lo que se requería en la formulación del problema.

Salir

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