Implementação de um modelo de acesso baseado em funções utilizando Row Level Security no PostgreSQL

Desenvolvimento do tema Estudo sobre a implementação de Row Level Security no PostgreSQL e para uma resposta detalhada para comentário.

A estratégia utilizada implica a aplicação do conceito de 'Lógica de Negócios no BD', que foi descrito um pouco mais detalhadamente aqui — Estudo sobre a implementação de lógica de negócios em funções armazenadas PostgreSQL

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 tarefa de negócio específica — modelo de acesso baseado em funções.

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

É necessário restringir o acesso para visualização/inserção/modificação/remoção de documentos de acordo com o papel do usuário do aplicativo. Por papel, entendemos uma entrada na tabela roles relacionada com uma relação muitos-para-muitos com a tabela tinham endereços de e-mail em domínios. Os detalhes da implementação das tabelas, devido à sua trivialidade, foram omitidos. Também foram omitidos detalhes específicos da implementação relacionados ao domínio.

Implementação

Criamos papéis, esquemas, tabela

Criação de objetos no BD

CREATE ROLE store;
CREATE SCHEMA store AUTHORIZATION store;
CREATE TABLE store.docs
(
  id integer ,         --id do documento
  man_id integer , --id do gerente do documento
  stat_id integer ,  --id do status do documento
  ...
  is_del BOOLEAN DEFAULT FALSE 
);
ALTER TABLE store.docs ADD CONSTRAINT doc_pk PRIMARY KEY (id);
ALTER TABLE store.docs OWNER TO store ;

Criamos funções para implementar RLS

Verificação da possibilidade de executar SELECT na linha

check_select

CRIE OU REPLACE FUNCTION store.check_select ( current_id store.docs.id%TYPE ) RETORNA boolean AS $$
DECLARE
  result boolean ;
  curr_pid integer ;
  curr_stat_id integer ;
  doc_man_id integer ;
BEGIN 
  -- DBA tem acesso a todos os documentos
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  --Se o documento tem a marca 'deletado' - não mostrar na seleção
  SELECT
    is_del
  INTO
    result
  FROM
    store.docs
  WHERE
    id = current_id ;
 IF result = TRUE
 THEN
   RETURN FALSE ;
 END IF ;
 --------------------------------

 --Obter id do usuário atual
 SELECT
   service_function.get_curr_pid ()
 INTO
   curr_pid ;
 --------------------------------

 --Obter id do gerente do documento
 SELECT
   man_id
 INTO
   doc_man_id
 FROM
   store.docs
 WHERE
   id = current_id ;
 --------------------------------

 --Se o gerente do documento não for o usuário atual ou gerente não designado
 --adicionar documento à seleção
 IF doc_man_id != curr_pid OR doc_man_id IS NULL
 THEN
   RETURN TRUE  ;
 ELSE
   --Obter o status atual do documento
   SELECT
     stat_id                                         
   INTO
     curr_statid
   FROM
     store.docs
   WHERE
     id = current_id ;
    
   --Se o status permitir visualizar o documento - adicionar documento à seleção                     
   IF curr_statid = 4 OR curr_statid = 9
   THEN
     RETURN TRUE ;
   ELSE
   --Caso contrário - excluir documento da seleção
     RETURN FALSE ;
    END IF ;
  END IF ;
  --------------------------------

 RETURN FALSE ;
END
$$ LINGUAGEM plpgsql DEFINIDOR DE SEGURANÇA;
ALTER FUNCTION store.check_select( store.docs.id%TYPE  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) FROM public; 
GRANT EXECUTE ON FUNCTION store.check_select( store.docs.id%TYPE  ) TO service_functions; 

Verificação da possibilidade de realizar um INSERT de linha

check_insert

CRIE OU REPLACE FUNCTION store.check_insert ( current_id store.docs.id%TYPE ) RETORNA boolean AS $$
DECLARE
  curr_role_id integer ;
BEGIN
  --DBA pode adicionar linha em qualquer caso
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

 --Obter id do papel do usuário atual 
 SELECT
   service_functions.current_rid()
  INTO
    curr_role_id ;
 --------------------------------

--Se o papel permitir a criação de um novo documento
--permitir
IF curr_role_id = 3 OR curr_role_id = 5     
THEN
  RETURN TRUE ;
END IF ;
--------------------------------
RETURN FALSE  ;
END
$$ LINGUAGEM plpgsql DEFINIDOR DE SEGURANÇA;
ALTER FUNCTION store.check_insert( store.docs.id%TYPE  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) FROM public;
GRANT EXECUTE ON FUNCTION store.check_insert( store.docs.id%TYPE  ) TO service_functions; 

Verificação da possibilidade de realizar um DELETE de linha

check_delete

CRIE OU REPLACE FUNCTION store.check_delete ( current_id store.docs.id%TYPE )
RETORNA boolean AS $$
BEGIN  
  --Apenas o DBA pode excluir linha 
  IF SESSION_USER = 'curr_dba'
  THEN
    RETURN TRUE ;
  END IF ;
  --------------------------------

  RETURN FALSE ;
END
$$ LINGUAGEM plpgsql
DEFINIDOR DE SEGURANÇA;
ALTER FUNCTION store.check_delete( store.docs.id%TYPE  ) OWNER TO store ;
REVOKE EXECUTE ON FUNCTION store.check_delete( store.docs.id%TYPE  ) FROM public;

Verificação da possibilidade de realizar um UPDATE de linha.

atualizar_usando

CRIE OU SUBSTITUA A FUNÇÃO store.update_using ( current_id store.docs.id%TYPE , is_del boolean )
RETORNA boolean AS $$
COMEÇAR  
   --Documentos com status 'deletado' não podem ser editados
   SE is_del 
   ENTÃO
     RETORNAR FALSO ;
 OUTRA
    RETORNAR VERDADEIRO ;
  FIM SE ;

FIM
$$ LINGUAGEM plpgsql DEFINIDOR DE SEGURANÇA;
ALTERAR FUNÇÃO store.update_using( store.docs.id%TYPE ,  boolean  ) PROPRIETÁRIO PARA store ;
REVOGAR EXECUÇÃO NA FUNÇÃO store.update_using( store.docs.id%TYPE ,  boolean  ) DE público;
CONCEDER EXECUÇÃO NA FUNÇÃO store.update_using( store.docs.id%TYPE  ) A service_functions;

atualizar_verificação

CRIE OU SUBSTITUA A FUNÇÃO store.update_with_check ( current_id store.docs.id%TYPE , is_del boolean )
RETORNA boolean AS $$
DECLARAR
  current_rid inteiro ;
  current_statid inteiro ;
COMEÇAR                

  --DBA pode visualizar a linha 
  SE SESSION_USER = 'curr_dba'
  ENTÃO
    RETORNAR VERDADEIRO ;
  FIM SE ;
  --------------------------------

 --Obter id da função do papel atual do usuário 
 SELECIONAR
   service_functions.current_rid()
  PARA
    curr_role_id ;
 --------------------------------                            

 --Remoção do documento - mudança de atributo 
 SE is_deleted
 ENTÃO
   --Se o papel do usuário ***
   SE current_role_id = 3        
   ENTÃO
      SELECIONAR
        stat_id                                          
      PARA
        curr_statid
      DE
        store.docs
      ONDE
        id = current_id ;

      --Documento com status *** não pode ser deletado 
      SE current_status_id = 11
      ENTÃO
         RETORNAR FALSO ;
      OUTRA
      --Pode deletar o documento em outros status
        RETORNAR VERDADEIRO ;
      FIM SE ;

    --Caso contrário, se o papel do usuário ***
    ELSIF current_role_id = 5            
    ENTÃO
      --Todos os status do documento 
      RETORNAR VERDADEIRO ;
    OUTRA
      --Outros usuários não podem deletar documentos
      RETORNAR FALSO ;
    FIM SE ;
 SENÃO      
   --Atualização do documento é permitida
    RETORNAR VERDADEIRO ;
FIM SE ;

RETORNAR FALSO ;
FIM
$$ LINGUAGEM plpgsql DEFINIDOR DE SEGURANÇA;
ALTERAR FUNÇÃO store.update_with_check( storg.docs.id%TYPE ,  boolean   ) PROPRIETÁRIO PARA store ;
REVOGAR EXECUÇÃO NA FUNÇÃO store.update_with_check( storg.docs.id%TYPE ,  boolean   )  DE público;
CONCEDER EXECUÇÃO NA FUNÇÃO store.update_with_check( store.docs.id%TYPE  ) A service_functions;

Ativação da política de Row Level Security para a tabela.

ATIVAR SEGURANÇA DE NÍVEL DE LINHA

ALTERAR TABELA store.docs ATIVAR SEGURANÇA DE NÍVEL DE LINHA ;

CRIAR POLÍTICA doc_select SOBRE store.docs PARA SELECIONAR A service_functions USANDO ( (SELECIONAR store.check_select(id)) );
CRIAR POLÍTICA doc_insert SOBRE store.docs PARA INSERIR A service_functions COM VERIFICAÇÃO ( (SELECIONAR store.check_insert(id)) );
CRIAR POLÍTICA docs_delete SOBRE store.docs PARA DELETAR A service_functions USANDO ( (SELECIONAR store.check_delete(id)) );

CRIAR POLÍTICA doc_update_using SOBRE store.docs PARA ATUALIZAR A service_functions USANDO ( (SELECIONAR store.update_using(id , is_del )) );
CRIAR POLÍTICA doc_update_check SOBRE store.docs PARA ATUALIZAR A service_functions  COM VERIFICAÇÃO ( (SELECIONAR store.update_with_check(id , is_del )) );

Resultado

Isso funciona.

A estratégia proposta permitiu transferir a implementação do modelo de papéis do nível de funções de negócios para o nível de armazenamento de dados.

As funções podem ser usadas como um modelo para implementar modelos mais sofisticados de ocultação de dados, se os requisitos de negócios assim exigirem.

Fonte: habr.com