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

Como complemento 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 linha. Abaixo está uma implementação prática de um pequeno tarefa comercial específica - ocultar dados excluídos. Esboço dedicado à implementação Modelagem de papéis usando RLS apresentado 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 mergulhar profundamente na área temática, brevemente, o problema pode ser formulado da seguinte forma: Existe uma tabela que implementa uma determinada entidade empresarial. As linhas da tabela podem ser excluídas, mas as linhas não podem ser excluídas fisicamente; elas devem ser ocultadas.

Pois está dito: “Não exclua nada, apenas renomeie. A Internet armazena TUDO"

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

Para implementar este conceito, a tabela possui o atributo esta deletado. Então tudo é simples - você precisa ter certeza de que o cliente pode ver apenas as linhas nas quais o atributo esta deletado falso Para que serve o mecanismo? Segurança em nível de linha.

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

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 - deletando uma linha na 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 vê o documento

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

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

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

Qual é o que foi exigido na declaração do problema.

Total

Se o tópico for interessante, no próximo estudo você poderá mostrar um exemplo de implementação de um modelo baseado em funções para separar o acesso a dados usando Row Level Security.

Fonte: habr.com