Encriptação - Chave Pública - Planilha para Tabela

PostgreSQL no Raspberry Pi com FreeBSD 13

Planilha pgsites.ods aberta no OpenOffice Calc
Planilha pgsites.ods aberta no OpenOffice Calc

Usando o PostgreSQL no Raspberry Pi com FreeBSD 13, vamos mostrar como armazenar com segurança numa tabela do PostgreSQL, com as informações encriptadas usando uma chave pública PGP lida em um arquivo, os dados das contas de vários sites contidos numa planilha. A criação das chaves está mostrada em Geração das chaves de criptografia pública e privada.

Planilha OpenDocument

Os dados das contas a serem armazenados no banco de dados são lidos da planilha no formato especificado pelo Open Document Format for Office Applications (ODF) pgsites.ods, criada pelo OpenOffice Calc, que possui cinco colunas: titulo, url, usuario, senha e notas, como mostrado na imagem acima.

Extensão pgcrypto

O módulo pgcrypto permite que alguns campos sejam armazenados encriptados. Isto é útil se apenas alguns dos dados precisarem ser protegidos. É necessário se conectar ao banco de dados e criar a extensão pgcrypto para usar as funções encriptação e desencriptação dos dados.

halley@bsd:~ $ psql --username=pguser --host=raspberry.pi --dbname=pgbase --password
Password:
psql (13.3)
Type "help" for help.

pgbase=> CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION

Criar a tabela

Vamos criar a tabela sites para armazenas os dados das conexões com os sites e conceder permissões para o usuário halley:

pgbase=> DROP TABLE IF EXISTS sites CASCADE;
NOTICE:  drop cascades to view vis_user_sites
DROP TABLE
pgbase=> CREATE TABLE IF NOT EXISTS sites (
    id      SERIAL NOT NULL            ,        -- Identificador da linha
    login   VARCHAR(255) NOT NULL DEFAULT user, -- Usuário que efetuou o login no PostgreSQL
    titulo  TEXT    NOT NULL,                   -- Título do site (Google, Yahoo, etc.)
    url     BYTEA   NOT NULL,                   -- URL do site
    usuario BYTEA   NOT NULL,                   -- Nome da conta no site (nome, e-mail, telefone, etc.)
    senha   BYTEA   NOT NULL,                   -- Senha da conta no site
    notas   BYTEA   NOT NULL,                   -- Informações diversas
    CONSTRAINT pk_sites PRIMARY KEY (id)        -- Chave primária
);
CREATE TABLE
pgbase=> -- Só um título por login
pgbase=> ALTER TABLE sites
    ADD CONSTRAINT unique_login_titulo
    UNIQUE  (login, titulo);
ALTER TABLE
pgbase=> -- Conceder permissões para o usuário 'halley'
pgbase=> GRANT ALL ON sites TO halley;
GRANT
pgbase=> GRANT ALL ON sites_id_seq TO halley;
GRANT

Criar a visão

Vamos criar a visão vis_user_sites que permite ao usuário acessar apenas os seus dados na tabela e conceder permissões para o usuário halley:

pgbase=> CREATE OR REPLACE VIEW vis_user_sites AS
SELECT id, login, titulo, url, usuario, senha, notas
FROM sites
WHERE login = user;
CREATE VIEW
pgbase=> GRANT ALL ON vis_user_sites TO halley;
GRANT

Conexão com o servidor de banco de dados

As definições para conexão do programa Python com o servidor de banco de dados estão mostradas em Módulo Python libpostgres, e os parâmetros para conexão com o servidor de banco de dados são os mesmos mostrados em Encriptação - Chave Simétrica - Planilha para Tabela.

Programa

Abaixo está mostrado o programa, escrito em Python, que lê uma planilha OpenDocument contendo dados das contas de vários sites e insere ou atualiza numa tabela do PostgreSQL os dados da planilha encriptados usando uma chave pública PGP lida de um arquivo.

#!/usr/bin/env ipython
# -*- coding: utf-8 -*-
"""Criptografia pelo Usuário - Chave Pública - Planilha para Tabela.

Este programa lê uma planilha OpenDocument contendo dados das contas de vários sites
e insere ou atualiza numa tabela do PostgreSQL os dados da planilha encriptados usando
uma chave pública PGP lida de um arquivo.

"""

import sys
from pyexcel_ods3 import get_data
import textwrap
import psycopg2
import libpostgres

# Nome da seção de onde os dados de conexão
# serão obtidos no arquivo de configuração
SECAO = 'halley'

# Comando SQL para inserir a linha na tabela
SQLINS = textwrap.dedent("""\
    INSERT INTO vis_user_sites (titulo, url, usuario, senha, notas) VALUES(
    %(titulo)s,
    PGP_PUB_ENCRYPT(%(url)s,     DEARMOR(%(psw)s)),
    PGP_PUB_ENCRYPT(%(usuario)s, DEARMOR(%(psw)s)),
    PGP_PUB_ENCRYPT(%(senha)s,   DEARMOR(%(psw)s)),
    PGP_PUB_ENCRYPT(%(notas)s,   DEARMOR(%(psw)s))
    )ON CONFLICT (login, titulo)
    DO
        UPDATE SET
            url = EXCLUDED.url,
            usuario = EXCLUDED.usuario,
            senha = EXCLUDED.senha,
            notas = EXCLUDED.notas;""")


def inserir(conn, chave_publica, titulo, url, usuario, senha, notas):
    """Insere ou atualiza uma linha na tabela do banco de dados.

    Args:
        conn:          Objeto de conexão com o servidor de banco de dados.
        chave_publica: Chave pública para encriptar os dados.
        titulo:        Título do site (Google, Yahoo, etc.)
        url:           URL do site
        usuario:       Usuário cadastrado no site (nome, e-mail, telefone, etc.)
        senha:         Senha do usuário no site
        notas:         Senha do usuário no site
    """
    try:
        cur = conn.cursor()
        cur.execute(SQLINS, {
            'titulo':titulo,
            'url':url,
            'usuario':usuario,
            'senha':senha,
            'notas':notas,
            'psw':chave_publica
            }
        )
        conn.commit()
        cur.close()
        print('Registro inserido com sucesso')
        print(titulo, url, usuario, senha, notas)
    except (Exception, psycopg2.DatabaseError) as e:
        print(e)


def ler_chave(arquivo):
    """ Lê a chave PGP

    Args:
        arquivo: nome do arquivo contendo a chave pública ou privada

    Returns:
        Chave PGP

    """
    try:
        with open(arquivo, 'r') as file:
            chave = file.read().strip()
    except FileNotFoundError as fnf:
        print(fnf)
    except:
        print("Erro ao ler a chave", sys.exc_info())

    return chave


if __name__ == '__main__':
    # Ler a chave púbica PGP para encriptar os dados exportada
    chave_publica = ler_chave('pguser_chave_publica')
    print(chave_publica)
    # Obter os parâmetros da conexão do arquivo de configuração
    parametros = libpostgres.config(SECAO)
    # Conectar com o servidor de banco de dados
    conn = libpostgres.conectar(parametros)
    # Ler os dados da planilha
    planilha = get_data('pgsites.ods')
    folhas = list(planilha.values())
    # Inserir os dados da primeira folha da planilha na tabela
    folha = folhas[0]
    for i in range(1, len(folha)):
        linha = folha[i]
        if (len(linha) < 5):
            for j in range(len(linha), 5):
                linha.append('')
        titulo = linha[0]
        url = linha[1]
        usuario = linha[2]
        senha = linha[3]
        notas = linha[4]
        inserir(conn, chave_publica, titulo, url, usuario, senha, notas)
    # Fechar a conexão com o servidor de banco de dados
    conn.close()

Abaixo está mostrada a saída produzida pelo programa:

-----BEGIN PGP PUBLIC KEY BLOCK-----

mQGNBGDF/BYBDADC5A/ZtlmQXOtm8yuyusq/OeRfMydmYiZXyoFn2Bdcjtm0GFzk
GYTGO8/BpbJwTUKGD1wZ7qhv/VtN1T5OlfMxdNi/ofF/Ee0+Kq3V81PDiZxiRV+K
LGU3h0DF9nTtGsMJXNFx/+yCX/9z3SCsOgnKhCnfgFLQTASzjnb/UOTvJQF6Nm4V
sBbTDwPQVKjRB25fGwaxYXZaEjDrESYkPfykqREvTk9PolVpTNBmXxFRk2r4UijA
zsm7zf/ODPbBw/WwzidcyPkSWZw38/SO0sXqFedNef8LukXTyVhlKxndF/OuRCpO
eyV8UAi4tHDVRdROnZGVrKwLfWgWnBn+9MlgjayDIGIYEjKR4DRdHLDHHEsZY8kp
xQborLJxskLwsUzc6P38sA3pgfbtmHVUxawxV7EHVCNyKUU60KTrKXSkFIokwuPA
xpi58mbgZWOXINb8XFsdyoqSFulh3drb1Ol3O3QQzw402sUqnEpSc3ciSyCfVP/3
/QjAPM4YE5c/90sAEQEAAbQGcGd1c2VyiQHUBBMBCAA+FiEEniAJ319HG/m8TgIL
fiKnbO5ofBoFAmDF/BYCGwMFCQPCZwAFCwkIBwIGFQoJCAsCBBYCAwECHgECF4AA
CgkQfiKnbO5ofBrtYQv/WEhn8fKIrZA5aGsy0wMbgGLEU0pOxMghBgjMgQloxDyH
lO7VCyq00ffsA719UNQKvE376o9+5Zu9LqwmiCqraOgdxnrvNeXETwHIk35M4jvA
DGYN6OpZ5B/rkutAbGxgShz2nHHn4P3EoaC51La3VjIdwgUaW1/2Ti9+gD0XFlaL
s4Jd1ZU3Xuz/PpuIGfyamqyAMb02VQqAOElGVZw9ufMAie+9Jt5W32SuuWWzthA/
ujj/3r+IK/CzXaZYPYDJRqwpKMiSdSK0Qo00PJUIYYEnkDKmODvH2pI0jhiQqrwq
taChIjDO5wBJ+7Q6JMpw3hDLwD7JjfYG6YxMr5irXlzd42jiUKqinX1Syo6Q4I8R
qceFH1rJD3jU6t3ZKz5nn47YeIZmybRXgZWUlxKH5PDvqJ2mha8YZoePajjdBuPc
zgu3LYBn5WqbTSMMxJRcbbpUajgsZKmK2Fy7HIoyegbPtsghJmCrkTyuJd8BmRVQ
nQ1Bg1Ah3WTzo/3ALkqluQGNBGDF/BYBDAC++l+VvdZ5o0G/DDpMyaD6XA60YooV
x2z/Tl+dvhpkIyT+SIvw9KC7gq7lo6uQPbtRImPyVGJ93chE1EhAbj+yn34Yqj9V
Cr6fX97IHB/XcuRcjDJBSOxzy0hy//ZCIRYdmvtJVO4J1BuwN65fNcHyPnhV+E31
e5/IKGfYHHIrxVtnIH3Zil2vrecIkXiCf9R2vb0mSEmZPSZz8cZlL1rZr+/NdaPv
lWvvi+mVaflSs+X9kXUv7O7T94I2h6G4Kmk51GH0PxwRRDVz+b+SufXa8t7gXk4b
kFd4oN8RGK7lz2TXcnMohHGDtnTXmFOkjmWDyJyAd+2SDnMQeOQ0IVWjrwLIW6BB
6h0TU4qUKopRyQKrP3H+HwG0NlNiZnOz+H6gGqcmBeIkT5QVkYQTqSyzKQVN1pWt
AdPRM/j2f7B3UqiOd61IVbO4ribt6ybLW+1pc+D9zfNcvzPiHl0cm4cYp5lVqjFd
cD3zwTfDDrTix3L6SbcHJloXAm07VPHhiOsAEQEAAYkBtgQYAQgAIBYhBJ4gCd9f
Rxv5vE4CC34ip2zuaHwaBQJgxfwWAhsMAAoJEH4ip2zuaHwaX9EMAKuJrsCISUoy
+RgfL0vd8EQuxs3gZ6OypsKj+Oqqit0d8fTBfaZJ2yePDPNrZtB9TZmzorc0Ty6W
oNynn7r4WVz88Pq9rhnooZgl1/oOP3cyHxQYUVpVj9k7mgdIf8LbxUE0VlwZ+5bZ
gALh0q0b++yaj994rqR/JKXmKX6iGC9yG2fbStkd4dqqD/AjNiE99MEqnPNpaEAo
JUl445HZdu10FGmSdm8RcT5x5FwfeddTaWiKGfulGfdptN4VdaDRrAsrdgCpLcuP
wUSeluc7FSpVaQuKENgbAfceSWypiubzTfH+5TUxueu5ZpHHFUHpk/WGfYMwtkHe
HMcQTf9UCd81+k4pn7vqQsBxaJo2KBiBpTdS4ed3K3S+Ow9Y0UBPqbC/tfdVktaB
Q5UmDcgq4YilUkgV/Z3csxnTvBuN09BCy0avftNqmuX6fBLTnyX3BhnUw0WGbIUz
ZdOl3+Zjvf6OjdGg+vFFM90zrPERyy6S3/TXXT+xdRU7vwxJgobe3Q==
=8Z7B
-----END PGP PUBLIC KEY BLOCK-----
Conectado ao PostgreSQL
Registro inserido com sucesso
Google https://www.google.com.br/ usuario_google senha_google Site de busca, e-mail,...
Registro inserido com sucesso
Yahoo https://br.yahoo.com/ usuario_yahoo senha_yahoo Site de busca, e-mail, finanças, …
Registro inserido com sucesso
Wikipédia https://pt.wikipedia.org/ usuario_wikipedia senha_wikipedia Enciclopédia livre
Registro inserido com sucesso
Priberam https://dicionario.priberam.org/   Dicionário de Português Contemporâneo
Registro inserido com sucesso
ProtonMail https://mail.protonmail.com/l usuario_proton senha_proton E-mail

Referências

* * *