Encriptação pelo Python - 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 a biblioteca cryptography do Python, os dados das contas de vários sites contidos numa planilha.

As definições para conexão do programa Python com o servidor de banco de dados estão mostradas em Módulo Python libpostgres, 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, e a criação da chave está mostrada em Geração da chave de criptografia

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.

Objetos do banco de dados

Foi utilizado o programa mostrado abaixo para definir os objetos do banco de dados usados para armazenar os dados encriptados com a biblioteca ‘cryptography’ do Python.

#!/usr/bin/env ipython
# -*- coding: utf-8 -*-
"""Criptografia pelo Usuário - Fernet - DDL.

Programa para:

Criar ou modificar os objetos do banco de dados
usados para armazenar os dados encriptados com
a biblioteca 'cryptography' do Python.

"""

import textwrap
import psycopg2
import libpostgres

# Criar a extensão pgcrypto, se não existir
SQL_CREATE_EXTENSION = textwrap.dedent("""\
CREATE EXTENSION IF NOT EXISTS pgcrypto;
""")

# Remover a tabela sites e os objetos que dependem dela
SQL_DROP_TABLE = textwrap.dedent("""\
DROP TABLE IF EXISTS sites CASCADE;
""")

# Criar a tabela sites
SQL_CREATE_TABLE = textwrap.dedent("""\
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
);
""")

# Só um título por login na tabela sites
SQL_ALTER_TABLE = textwrap.dedent("""\
ALTER TABLE sites
    ADD CONSTRAINT unique_login_titulo
    UNIQUE  (login, titulo);
""")

# Criar a visão vis_user_sites
SQL_CREATE_VIEW = textwrap.dedent("""\
CREATE OR REPLACE VIEW vis_user_sites AS
    SELECT id, login, titulo, url, usuario, senha, notas
    FROM sites
    WHERE login = user;
""")

# Conceder as autorizações para o login halley

SQL_GRANT_SITES = textwrap.dedent("""\
GRANT ALL ON sites TO halley;
""")

SQL_GRANT_SITES_ID_SEQ = textwrap.dedent("""\
GRANT ALL ON sites_id_seq TO halley;
""")

SQL_GRANT_VIS_USER_SITES = textwrap.dedent("""\
GRANT ALL ON vis_user_sites TO halley;
""")

if __name__ == '__main__':
    # Obter os parâmetros da conexão no arquivo de configuração
    parametros = libpostgres.config("pguser")
    # Conectar com o servidor de banco de dados
    conn = libpostgres.conectar(parametros)
    try:
        cur = conn.cursor()
        cur.execute(SQL_CREATE_EXTENSION)
        cur.execute(SQL_DROP_TABLE)
        cur.execute(SQL_CREATE_TABLE)
        cur.execute(SQL_ALTER_TABLE)
        cur.execute(SQL_CREATE_VIEW)
        cur.execute(SQL_GRANT_SITES)
        cur.execute(SQL_GRANT_SITES_ID_SEQ)
        cur.execute(SQL_GRANT_VIS_USER_SITES)
        conn.commit()
    except (Exception, psycopg2.DatabaseError) as e:
        print(e)
    finally:
        print("Terminado")

Programa

Abaixo está mostrado o programa 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 a biblioteca cryptography do Python.

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

Programa para:

1. Ler a chave a ser usada para encriptar os dados na console;

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

3. Inserir ou atualizar numa tabela do PostgreSQL os dados da planilha
   encriptados usando a biblioteca 'cryptography' do Python;

4. Mostrar os dados do usuário armazenados na tabela.

Fernet é uma implementação de criptografia simétrica (também conhecida como
"chave secreta") autenticada, com a garantia de que uma mensagem encriptada
utilizando essa implementação não pode ser manipulada ou lida sem a chave.

A senha de encriptação é lida do terminal ou da console.
Se não for lida de um terminal pode ser exibida na tela.

"""

import re
from pyexcel_ods3 import get_data
import textwrap
import psycopg2
from cryptography.fernet import Fernet
import getpass
import libpostgres

# Comando SQL para inserir a linha na tabela
# Se houver conflito os dados serão atualizados
SQLINS = textwrap.dedent("""\
    INSERT INTO vis_user_sites (titulo, url, usuario, senha, notas)
    VALUES(%(titulo)s, %(url)s, %(usuario)s, %(senha)s, %(notas)s)
    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, url, usuario, senha, notas
    FROM vis_user_sites
    ORDER BY titulo""")


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


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

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

    """
    try:
        cur = conn.cursor()
        cur.execute(SQLSEL)
        linhas = cur.fetchall()
        for linha in linhas:
            celulas = list(linha)
            pk = celulas[0]
            login = celulas[1]
            titulo = celulas[2]
            url = fernet.decrypt(celulas[3].tobytes()).decode()
            usuario = fernet.decrypt(celulas[4].tobytes()).decode()
            senha = fernet.decrypt(celulas[5].tobytes()).decode()
            notas = fernet.decrypt(celulas[6].tobytes()).decode()
            print(pk, login, titulo, url, usuario, senha, notas)
    except (Exception, psycopg2.DatabaseError) as e:
        print(e)


if __name__ == '__main__':
    # Ler a chave na console
    key = getpass.getpass(prompt="Forneça a chave para encriptação: ")
    # Ficar somente com a parte entre ''
    key = re.findall("(?<=\')(.*?)(?=\')", key)
    # Objeto de encriptação simétrica
    fernet = Fernet(bytes(key[0], 'utf-8'))
    # Obter os parâmetros da conexão no arquivo de configuração
    parametros = libpostgres.config("halley")
    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 = fernet.encrypt(linha[1].encode())
        usuario = fernet.encrypt(linha[2].encode())
        senha = fernet.encrypt(linha[3].encode())
        notas = fernet.encrypt(linha[4].encode())
        inserir(titulo, url, usuario, senha, notas)
    # Mostrar os dados da tabela
    mostrar(fernet)
    # Efetivar as transações
    conn.commit()
    # Fechar a conexão com o servidor de banco de dados
    conn.close()

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

Warning: Password input may be echoed.
Forneça a chave para encriptação: b'JvNU1GfIsnIU4vkVi2UqN9218SWhUe0u9KDFwgHEDOQ='
Conectado ao PostgreSQL
Registro inserido com sucesso: Google
Registro inserido com sucesso: Yahoo
Registro inserido com sucesso: Wikipédia
Registro inserido com sucesso: Priberam
Registro inserido com sucesso: ProtonMail
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

* * *