Um estudo sobre a implementação de segurança em nível de linha no PostgreSQL

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

A parte teórica está bem descrita na documentação PostgreSQL - Políticas de proteção de linhaA seguir, apresentamos uma implementação prática de um pequeno Uma tarefa comercial específica: ocultar dados excluídos. Um estudo sobre implementação Modelagem de papéis usando RLS apresentados separadamente.

Não há nada de novo no artigo, não há significado oculto ou conhecimento secreto. Apenas um esboço sobre a implementação prática de uma ideia teórica. Se alguém estiver interessado, leia. Se você não está interessado, não perca tempo.

Formulação do problema

Sem entrar em muitos detalhes sobre o assunto, o problema pode ser resumidamente formulado da seguinte maneira: Existe uma tabela que implementa uma determinada entidade de negócio. As linhas da tabela podem ser excluídas, mas a exclusão física de linhas não é permitida; elas precisam ser ocultadas.

Pois dizem: "Não apague nada, apenas renomeie. A internet armazena TUDO."

Ao mesmo tempo, é aconselhável não reescrever funções armazenadas existentes que trabalham com essa entidade.

Para implementar esse conceito, a tabela possui um atributo. é_excluídoEntão tudo é simples: você precisa fazer com que o cliente veja apenas as linhas em que o atributo está presente. é_excluído Falso. É para isso que o mecanismo serve. Segurança em nível de linha.

Implementação

Crie uma função e um esquema separados.

CREATE ROLE repos;
CREATE SCHEMA repos;

Criar uma tabela de destino

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

Ligar Segurança em 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 - excluir 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 de negócios — excluir um 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;

Descobertas

O cliente exclui o documento.

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

Após a exclusão, o cliente não visualiza 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 foi alterado. é_del

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

Que é exatamente o que foi solicitado no enunciado do problema.

Total

Caso o tema seja de interesse, o próximo estudo poderá apresentar um exemplo de implementação de um modelo baseado em funções para separar o acesso a dados utilizando a Segurança em Nível de Linha (Row Level Security).

Fonte: habr.com