Encriptação - Chave Simétrica - 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 simétrica PGP lida em um arquivo de configuração, os dados das contas de vários sites contidos numa planilha.

A tabela usada é idêntica a criada em Encriptação - Chave Simétrica - psql, e as definições para conexão com o servidor de banco de dados estão mostradas em Módulo Python libpostgres.

Os algoritmos de chave simétrica são algoritmos de criptografia que utilizam as mesmas chaves criptográficas tanto para a encriptação de texto simples quanto para a desencriptação de texto criptográfico. Pretty Good Privacy (PGP) é um programa de encriptação que fornece privacidade criptográfica e autenticação para a comunicação de dados.

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.

Parâmetros da conexão

Os parâmetros da conexão com o servidor de banco de dados são lidos da seção especificada no arquivo de configuração database.ini, mostrado abaixo:

[pguser]
user=pguser
host=raspberry.pi
database=pgbase
password=pguserpwd
port=5432
[halley]
user=halley
host=raspberry.pi
database=pgbase
password=halleypwd
port=5432

Chave de encriptação/desencriptação

A chave simétrica PGP para encriptação/desencriptação dos dados é lida da seção especificada no arquivo de configuração password.ini, mostrado abaixo:

[pguser]
psw = 'Minh4Senh@Fort3'
[halley]
psw = 'Minh4Outr@Senh@Fort3'

Programa

Abaixo está mostrado o programa que lê os dados das contas dos sites contidos na planilha OpenDocument, e insere ou atualiza estes dados numa tabela do PostgreSQL com as informações encriptadas usando uma chave simétrica PGP lida em um arquivo de configuração.

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

Programa para:

1. Ler uma planilha OpenDocument contendo dados das contas de vários sites;

2. Inserir ou atualizar numa tabela do PostgreSQL os dados da planilha
   encriptados usando uma chave simétrica PGP lida de um arquivo de
   configuração;

3. Mostrar os dados armazenados na tabela.
"""

from pyexcel_ods3 import get_data
import textwrap
import psycopg2
import libpostgres

# Nome da seção de onde os dados de conexão e a senha
# simétrica PGP serão obtidos nos arquivos de configuração
SECAO = 'halley'

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

# Comando SQL para ler as linhas do usuário na tabela
SQLSEL = textwrap.dedent("""\
    SELECT id, login, titulo,
    PGP_SYM_DECRYPT(url::bytea,     %(psw)s) as url,
    PGP_SYM_DECRYPT(usuario::bytea, %(psw)s) as usuario,
    PGP_SYM_DECRYPT(senha::bytea,   %(psw)s) as senha,
    PGP_SYM_DECRYPT(notas::bytea,   %(psw)s) as notas
    FROM sites
    WHERE login = %(login)s;""")




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

    Args:
        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':psw
            }
        )
        conn.commit()
        cur.close()
        print('Registro inserido com sucesso')
        print(titulo, url, usuario, senha, notas)
    except (Exception, psycopg2.DatabaseError) as e:
        print(e)


def mostrar(login):
    """Mostra as linhas da tabela.

    Args:
        login: login do usuário no servidor de banco de dados.

    """
    try:
        cur = conn.cursor()
        cur.execute(SQLSEL, { 'psw': psw, 'login': login})
        linhas = cur.fetchall()
        for linha in linhas:
            print(linha)
    except (Exception, psycopg2.DatabaseError) as e:
        print(e)


if __name__ == '__main__':
    # Ler a senha simétrica PGP para encriptar
    # os dados no arquivo de configuração
    params = libpostgres.config(SECAO, 'password.ini')
    psw = (params["psw"])
    # Obter os parâmetros da conexão do arquivo de configuração
    parametros = libpostgres.config(SECAO)
    login = parametros["user"]
    # 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(titulo, url, usuario, senha, notas)
    # Mostrar os dados da tabela
    mostrar(login)
    # Fechar a conexão com o servidor de banco de dados
    conn.close()

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

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
(1, 'halley', 'Google', 'https://www.google.com.br/', 'usuario_google', 'senha_google', 'Site de busca, e-mail,...')
(4, 'halley', 'Priberam', 'https://dicionario.priberam.org/', '', '', 'Dicionário de Português Contemporâneo')
(5, 'halley', 'ProtonMail', 'https://mail.protonmail.com/l', 'usuario_proton', 'senha_proton', 'E-mail')
(3, 'halley', 'Wikipédia', 'https://pt.wikipedia.org/', 'usuario_wikipedia', 'senha_wikipedia', 'Enciclopédia livre')
(2, 'halley', 'Yahoo', 'https://br.yahoo.com/', 'usuario_yahoo', 'senha_yahoo', 'Site de busca, e-mail, finanças, …

Referências

* * *