Un estudo sobre a implementación de seguranza a nivel de fila en PostgreSQL

Como complemento de Un estudo sobre a implementación da lóxica empresarial a nivel de funcións almacenadas PostgreSQL и principalmente para unha resposta detallada en comentario.

A parte teórica está ben descrita na documentación PostgreSQL - Políticas de protección de filas. A continuación móstrase unha implementación práctica dun pequeno tarefa empresarial específica: ocultar os datos eliminados. Esbozo dedicado á implementación Modelado de roles mediante RLS presentado por separado.

Non hai nada novo no artigo, non hai significado oculto nin coñecemento secreto. Só un esbozo sobre a implementación práctica dunha idea teórica. Se alguén está interesado que lea. Se non estás interesado, non perdas o tempo.

Declaración de problemas

Sen mergullarse profundamente na área temática, brevemente, o problema pódese formular do seguinte xeito: Hai unha táboa que implementa unha determinada entidade empresarial. As filas da táboa pódense eliminar, pero as filas non se poden eliminar fisicamente; deben ocultarse.

Pois dise: “Non borres nada, só cambialle o nome. Internet almacena TODO"

Durante o camiño, é recomendable non reescribir as funcións almacenadas existentes que funcionen con esta entidade.

Para implementar este concepto, a táboa ten o atributo está_eliminado. Entón todo é sinxelo: cómpre asegurarse de que o cliente pode ver só as liñas nas que o atributo está_eliminado falso Para que serve o mecanismo? Seguridade a nivel de fila.

Implantación

Crea un papel e un esquema separados

CREATE ROLE repos;
CREATE SCHEMA repos;

Crea a táboa de destino

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

Incluímos Seguridade 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 servizo — borrar unha fila da táboa

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

Descubrimentos

O cliente elimina o documento

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

Despois da eliminación, o cliente non ve o documento

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

Pero na base de datos non se elimina o documento, só se cambia o 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)

Que é o que se requiría na declaración do problema.

Total

Se o tema é interesante, no seguinte estudo podes mostrar un exemplo de implementación dun modelo baseado en roles para separar o acceso aos datos mediante a Seguridade a nivel de fila.

Fonte: www.habr.com