Estudo sobre a implementação de Row Level Security no PostgreSQL

Como complemento a Estudo sobre a implementação de lógica de negócios em funções armazenadas PostgreSQL e principalmente para uma resposta detalhada para comentário.

A parte teórica está bem explicada na documentação Postgres Pro — Políticas de proteção de linhas. Abaixo, discutiremos a implementação prática de uma pequena tarefa comercial específica — ocultação de dados removidos. Este estudo é dedicado à implementação do modelo de papel usando RLS apresentado separadamente.

Não há nada de novo no artigo, nenhuma mensagem oculta 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 não estiver interessado — não perca seu tempo.

Formulação do problema

Sem mergulhar profundamente no assunto, de forma sucinta, a tarefa pode ser formulada da seguinte maneira: existe uma tabela que implementa uma certa entidade de negócios. As linhas na tabela podem ser excluídas, mas não podem ser fisicamente removidas, apenas ocultadas.

Pois está dito — "Nada deve ser excluído, apenas renomeado. A internet guarda TUDO"

Além disso, é desejável não reescrever as funções armazenadas já existentes que operam com essa entidade.

Para implementar esse conceito, a tabela possui um atributo is_deleted. A partir daí, é simples — precisamos garantir que o cliente veja apenas as linhas em que o atributo is_deleted é falso. Para isso, utiliza-se o mecanismo Row Level Security.

Implementação

Criamos um papel e um esquema separado

CREATE ROLE repos;
CREATE SCHEMA repos;

Criamos a tabela de destino

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

Ativamos Row Level Security

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 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 — exclusão de 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;

Resultados

O cliente exclui o documento

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

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

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

Mas no banco de dados o documento não foi excluído, apenas 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)

O que era necessário na formulação da tarefa.

Resultado

Se o tema for interessante, no próximo estudo podemos mostrar um exemplo de implementação do modelo de papel de separação de acesso a dados usando Row Level Security.

Fonte: habr.com