Qual é a melhor maneira de armazenar o certificado X509 no banco de dados PostgreSQL?

A duplicação não é o ideal, mas neste caso é provavelmente a melhor escolha. Defina as permissões da tabela para que o proprietário da tabela não seja o usuário operacional diário do banco de dados com o qual seu aplicativo é executado e apenas GRANTseu aplicativo a capacidade de gravar na coluna de dados do certificado, não nas colunas de "cache" com expiração etc. Tenha um SECURITY DEFINERgatilho A função interceptar grava no campo do certificado e, como um usuário privilegiado, atualiza as colunas de cache indexadas usando uma biblioteca X.509 para extrair os campos do certificado depois de verificá-lo.

Como alternativa, você pode escrever uma função PL/Python, PL/Perl ou até mesmo uma função C SQL que chama uma biblioteca de analisador de certificados X.509 para extrair campos e retorná-los. Então você diria extract_x509_field(cert, 'subject'). Ou talvez até mesmo um formulário de retorno de linha como SELECT subject, issuerName FROM (SELECT extract_x509_fields(cert) FROM the_table)where extract_x509_fieldsretorna uma linha de todos os dados de certificado relevantes. Com essa abordagem, você pode criar índices funcionais como os CREATE INDEX cert_issuer ON certificate_table( extract_x509_field(cert,'issuer') );que podem ser usados ​​para corresponder a WHEREexpressões. Você não precisaria ter colunas de tabela para os dados extraídos. A desvantagem é que isso provavelmente seria mais lento, pois o certificado seria analisado várias vezes durante a criação do índice, durante as novas verificações do índice, etc.

De qualquer forma, é vital que seu aplicativo opere como um usuário do PostgreSQL que não seja o proprietário do banco de dados, não seja um superusuário e não seja o proprietário das tabelas e índices em questão. Devem ser GRANTconcedidos os direitos mínimos necessários e nada mais. Se você tiver tarefas bastante separadas (digamos, somente leitura versus gravação e atualização), considere usar diferentes usuários de banco de dados para elas, de modo que, mesmo que a parte "leitura" do seu aplicativo seja enganada para tentar gravar um campo/atualizar um certificado /etc, ele não tem permissão para isso.

Abordei um problema semelhante em meu blog Armazenando certificados digitais X.509 (e outras coisas confusas) e alguns comentários anteriores. (É muito longo recortar e colar aqui.) Muitos dos pontos levantados aqui são muito mais fáceis se você puder criar uma função definida pelo usuário que extraia os campos que você precisa armazenar em cache.

Abordando um outro ponto acima - é possível escrever um gatilho que usa SPI para verificar se o emissor do certificado está presente no banco de dados. Você precisaria incluir exceções para certificados raiz autoassinados e certificados 'confiáveis' fornecidos por outros. Isso, mais algumas outras verificações de sanidade (por exemplo, o emissor tem a capacidade 'básica'? Existem restrições de DN?) daria a você um repositório muito mais forte.

É sábio fazer isso? Eu continuo indo e voltando sobre isso. Meus pensamentos atuais são de que funcionaria se você emitisse e gerenciasse seus próprios certificados, mas seria uma grande dor de cabeça se você incorporasse certificados de terceiros. O problema é que existem muitos certificados viciados por aí. Se você for rigoroso, excluirá muitos certificados em uso e, se for negligente, por que se preocupar com a lógica extra?

Não entendi bem por que você deseja armazenar os vários campos de assunto/emissor separadamente, a menos que vá consultar as informações do banco de dados; especialmente porque você terá que ler o certificado para verificar seus detalhes (seu segundo item da lista).

Se você estiver armazenando isso para expirar/invalidar certificados automaticamente, poderá executar uma tarefa separada que faça isso.

Além disso, como o postgresql permite que você use python como uma linguagem de procedimento; você pode escrever um gatilho ou exibição que retornará as informações "analisadas" para seu aplicativo - se você realmente quiser descarregá-las de seu aplicativo.

Além disso, eu faria o seguinte, já que você está armazenando certificados:

1. Certifique-se de que a conexão entre os clientes e o servidor esteja criptografada (você deve fazer isso de qualquer maneira). Consulte a seção 17.9 na documentação.

2. Certifique-se de que a tabela/coluna esteja criptografada. Consulte a pycryptodocumentação do módulo.

Acrescentando à excelente resposta de Craig, que uma vez que você começa a extrair informações de uma biblioteca X509, você pode fazer outras coisas interessantes com ela. Não sei se o SQLAlchemy sempre usa aliases de tabela ou se você pode forçá-lo a fazê-lo, mas se puder, evite a duplicação de informações chamando as rotinas de análise diretamente nos certificados X509 e até indexe a saída dessas funções para que você não precise chamá-las no horário selecionado (ou seja, as funções são executadas no horário de inserção/atualização), como ele menciona.

Uma coisa que gostaria de apontar é que você pode criar métodos de tabela que, se você conseguir que seu ORM sempre use aliases de tabela, poderá substituir as colunas. Por exemplo:

CREATE OR REPLACE FUNCTION issuer(x509table) returns text 
LANGUAGE SQL IMMUTABLE AS $$
    select issuer_cn FROM extract_x509info($1.x509data);
$$;

Isso pode ser encontrado por:

SELECT x.issuer FROM x509table x;  --works

Observe que o seguinte não é válido e, portanto, meus comentários sobre o ORM:

SELECT issuer FROM x509table; --does not work

O motivo é que, se não houver coluna do emissor, o analisador converterá a primeira instrução para:

SELECT issuer(x) from x509table x;

Observe que a indexação de muitas colunas mudará muito o tempo de computação de leitura para gravação (tornando inserções/atualizações mais lentas, mas operações de leitura mais rápidas).

No final, é difícil chegar a uma recomendação completa sem saber exatamente o que você está fazendo, quais são as cargas de trabalho esperadas etc. A vantagem da duplicação é separar os dados das funções de extração para leitura e gravação, permitindo mais otimizações ser possível. A vantagem de manter tudo funcional (supondo que seu ORM suporte isso) é que ele fornece garantias adicionais de integridade de dados.