Estudo de implementação de segurança em nível de linha em PostgreSQL

Como um suplemento ao Etude sobre a implementação da lógica de negócios no nível das funções armazenadas do PostgreSQL e principalmente para uma resposta detalhada ao comentário .



A teoria está bem descrita na documentação do PostgreSQL - Políticas de segurança de linha . Abaixo está uma implementação prática de uma pequena tarefa de negócios específica - ocultar dados excluídos. Um estudo sobre a implementação do Role Model usando RLS é apresentado separadamente.

O artigo não é novidade, não há significado oculto e conhecimento secreto. Apenas um esboço sobre a implementação prática de uma ideia teórica. Se alguém estiver interessado, continue lendo. Quem não está interessado - não perca seu tempo.

Formulação do problema

Sem aprofundar a área temática, resumidamente, a tarefa pode ser formulada da seguinte forma: há uma tabela que implementa uma determinada entidade empresarial. As linhas da tabela podem ser excluídas, mas você não pode excluí-las fisicamente; é necessário ocultá-las.



Pois é dito - “Não apague nada, apenas renomeie. A Internet armazena TUDO. ”



Ao longo do caminho, é aconselhável não reescrever funções armazenadas existentes que funcionam com esta entidade.



Para implementar esse conceito, a tabela possui o atributo is_deleted . Então tudo é simples - você precisa fazer isso de forma que o cliente possa ver apenas as linhas nas quais o atributo is_deleted é falso. Para que o mecanismo de Segurança em nível de linha é usado .

Implementação

Crie uma função e um esquema separados

CREATE ROLE repos;
CREATE SCHEMA repos;

Crie a tabela de destino

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

Ative a segurança de nível de linha

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 ;

Função de serviço - exclusão de uma linha em uma tabela

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;

Função empresarial - exclusão de 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

Cliente exclui documento

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

Após a exclusão, o cliente não vê o documento

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

Mas o documento não foi excluído do banco de dados, apenas o atributo is_del foi alterado

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

Isso é o que foi exigido na formulação do problema.

Resultado

Se o tópico for interessante, no próximo estudo você pode mostrar um exemplo de implementação de um modelo baseado em funções para compartilhar o acesso a dados usando a Segurança de Nível de Linha.