Segurança e hardening do PostgreSQL

# /etc/postgresql/17/main/pg_hba.conf
# Formato: TYPE  DATABASE  USER  ADDRESS  METHOD

# Conexão local do superusuário: apenas via socket Unix
local   all         postgres                    peer

# Conexão local de usuários da aplicação: senha
local   all         all                         scram-sha-256

# Conexão remota: apenas do IP específico da aplicação
host    minha_db    minha_app   10.0.0.2/32    scram-sha-256

# Replicação: apenas do IP do standby
host    replication  replicador  10.0.0.3/32  scram-sha-256

# NUNCA usar em produção:
# host all all 0.0.0.0/0 trust    (sem senha, qualquer IP)
# host all all 0.0.0.0/0 md5      (MD5 é vulnerável, use scram-sha-256)

# Recarregar sem restart:
sudo -u postgres psql -c "SELECT pg_reload_conf();"

-- Conectar como superusuário
sudo -u postgres psql -d minha_db

-- Role para a aplicação (apenas operações CRUD)
CREATE ROLE app_user WITH LOGIN PASSWORD 'senha_forte';
GRANT CONNECT ON DATABASE minha_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Role apenas leitura (para dashboards, analytics)
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'senha_readonly';
GRANT CONNECT ON DATABASE minha_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

-- Role para migrações (pode criar tabelas)
CREATE ROLE migrator WITH LOGIN PASSWORD 'senha_migrator';
GRANT CONNECT ON DATABASE minha_db TO migrator;
GRANT ALL ON SCHEMA public TO migrator;

-- NUNCA dar SUPERUSER para a aplicação
-- Verificar roles criadas:
\du

-- Instalar extensão pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Tabela com coluna criptografada:
CREATE TABLE clientes (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  cpf_criptografado BYTEA,  -- armazenar CPF criptografado
  criado_em TIMESTAMPTZ DEFAULT now()
);

-- Inserir com criptografia simétrica (AES):
INSERT INTO clientes (nome, email, cpf_criptografado)
VALUES (
  'João Silva',
  'joao@example.com',
  pgp_sym_encrypt('123.456.789-00', current_setting('app.encryption_key'))
);

-- Ler com descriptografia:
SELECT nome, email,
       pgp_sym_decrypt(cpf_criptografado,
                       current_setting('app.encryption_key')) AS cpf
FROM clientes;

-- Configurar a chave de criptografia por sessão:
SET app.encryption_key = 'chave-aes-256-muito-longa-e-aleatoria';

-- Hash irreversível de senhas:
CREATE EXTENSION IF NOT EXISTS pgcrypto;
INSERT INTO usuarios (senha_hash) VALUES (crypt('senha123', gen_salt('bf', 12)));
-- Verificar senha:
SELECT * FROM usuarios WHERE senha_hash = crypt('senha123', senha_hash);

# Instalar pgaudit
sudo apt install -y postgresql-17-pgaudit

# postgresql.conf:
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role, connection'
# write: INSERT, UPDATE, DELETE, TRUNCATE
# ddl: CREATE, ALTER, DROP
# role: GRANT, REVOKE, CREATE ROLE
# connection: login/logout

# Recarregar:
sudo systemctl restart postgresql

# Verificar logs de auditoria:
sudo tail -f /var/log/postgresql/postgresql-17-main.log | grep AUDIT

# Exemplo de log gerado:
# AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,,
#   "CREATE TABLE dados_sensiveis (...)",<not logged>
# AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.usuarios,
#   "INSERT INTO usuarios VALUES (...)",<not logged>

# Auditoria por objeto específico (apenas tabela critica):
-- Conectado ao banco:
SELECT pgaudit.set_config('write', 'true', true);
ALTER TABLE dados_financeiros ENABLE ROW LEVEL SECURITY;

-- Habilitar RLS na tabela
ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;
ALTER TABLE pedidos FORCE ROW LEVEL SECURITY;  -- aplica ao owner também

-- Política: usuário só vê seus próprios pedidos
CREATE POLICY pedidos_isolamento ON pedidos
  USING (usuario_id = current_setting('app.usuario_id')::uuid);

-- Política de escrita: usuário só pode criar/editar seus pedidos
CREATE POLICY pedidos_escrita ON pedidos
  FOR INSERT
  WITH CHECK (usuario_id = current_setting('app.usuario_id')::uuid);

-- Na aplicação Node.js — configurar por sessão:
// Antes de cada query de usuário:
await db.query("SET app.usuario_id = $1", [req.user.id])

// Agora todas as queries à tabela pedidos são automaticamente filtradas:
const pedidos = await db.query("SELECT * FROM pedidos")
// Retorna APENAS os pedidos do usuário logado

-- Verificar políticas criadas:
SELECT * FROM pg_policies WHERE tablename = 'pedidos';