Segurança e Roles

Roles — CREATE ROLE

-- Criar role (usuário ou grupo)
CREATE ROLE app_user WITH LOGIN PASSWORD 'senha123';
CREATE ROLE read_only;
CREATE ROLE admin WITH LOGIN PASSWORD 'admin123' SUPERUSER;

-- Atributos de role
CREATE ROLE usuario WITH
    LOGIN              -- pode fazer login
    PASSWORD 'senha'   -- senha
    CREATEDB           -- pode criar databases
    CREATEROLE         -- pode criar roles
    SUPERUSER          -- superusuário (tudo)
    REPLICATION        -- pode fazer replicação
    BYPASSRLS          -- ignora RLS
    INHERIT            -- herda permissões de roles pai
    CONNECTION LIMIT 5 -- limite de conexões
    VALID UNTIL '2025-12-31';  -- expiração

-- Modificar role
ALTER ROLE usuario CREATEDB;
ALTER ROLE usuario NOCREATEDB;
ALTER ROLE usuario RENAME TO novo_nome;
ALTER ROLE usuario WITH PASSWORD 'nova_senha';

-- Associação
GRANT read_only TO app_user;        -- app_user herda permissões de read_only
REVOKE read_only FROM app_user;

-- Grupos e hierarquia
CREATE ROLE analistas;
CREATE ROLE gerentes;
GRANT analistas TO gerentes;        -- gerente herda permissões de analista
GRANT gerentes TO maria;            -- maria herda de gerente (e analista)

GRANT / REVOKE

-- Database
GRANT CONNECT ON DATABASE meubanco TO app_user;
GRANT CREATE ON DATABASE meubanco TO app_user;

-- Schema
GRANT USAGE ON SCHEMA public TO read_only;
GRANT CREATE ON SCHEMA vendas TO app_user;
GRANT ALL ON SCHEMA vendas TO admin;

-- Tabelas
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT INSERT, UPDATE, DELETE ON clientes TO app_user;
GRANT ALL PRIVILEGES ON pedidos TO admin;

-- Colunas específicas
GRANT SELECT (id, nome, email) ON clientes TO app_user;
-- app_user NÃO pode ver coluna cpf

-- Funções
GRANT EXECUTE ON FUNCTION processar_pedido TO app_user;

-- Sequences
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Default privileges (para objetos futuros)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO read_only;

ALTER DEFAULT PRIVILEGES FOR ROLE admin IN SCHEMA vendas
    GRANT INSERT, UPDATE ON TABLES TO app_user;

-- Revogar
REVOKE DELETE ON clientes FROM app_user;
REVOKE ALL ON DATABASE meubanco FROM PUBLIC;

RLS — Row Level Security

-- 1. Habilitar RLS na tabela
ALTER TABLE clientes ENABLE ROW LEVEL SECURITY;

-- 2. Criar política
CREATE POLICY clientes_policy ON clientes
    USING (true)               -- SELECT: todas as linhas
    WITH CHECK (false);        -- INSERT/UPDATE: nenhuma

-- Política por usuário
CREATE POLICY clientes_own ON clientes
    FOR ALL
    USING (id = current_setting('app.current_user_id')::INT)
    WITH CHECK (id = current_setting('app.current_user_id')::INT);

-- Política multi-tenant
CREATE TABLE pedidos_tenant (
    id          SERIAL PRIMARY KEY,
    tenant_id   INT NOT NULL DEFAULT current_setting('app.tenant_id')::INT,
    cliente     TEXT,
    total       NUMERIC
);

ALTER TABLE pedidos_tenant ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON pedidos_tenant
    USING (tenant_id = current_setting('app.tenant_id')::INT);

-- Permissive vs Restrictive
ALTER TABLE documentos ENABLE ROW LEVEL SECURITY;

-- Permissive (OR): qualquer política que permita = acesso liberado
CREATE POLICY docs_public ON documentos
    AS PERMISSIVE
    FOR SELECT
    USING (publico = true);

-- Restrictive (AND): todas as políticas restrictive devem permitir
CREATE POLICY docs_own ON documentos
    AS RESTRICTIVE
    FOR SELECT
    USING (owner_id = current_user_id());

-- BYPASSRLS: roles que ignoram RLS
CREATE ROLE admin_rls BYPASSRLS;
GRANT admin_rls TO admin_user;

SSL/TLS

# Configuração SSL no postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
ssl_crl_file = '/etc/ssl/certs/ca.crl'

# sslmode options (no cliente)
# disable       - sem SSL
# allow         - tenta SSL, aceita sem
# prefer        - prefere SSL (padrão)
# require       - exige SSL, mas não valida cert
# verify-ca     - exige SSL + valida CA
# verify-full   - exige SSL + valida CA + hostname

# Criar certificado auto-assinado (para teste)
openssl req -new -x509 -days 365 -nodes \
    -out /etc/ssl/certs/server.crt \
    -keyout /etc/ssl/private/server.key \
    -subj "/CN=postgres.example.com"
chmod 600 /etc/ssl/private/server.key
chown postgres:postgres /etc/ssl/private/server.key

pg_hba.conf

# Formato: TYPE  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# TYPE: local, host, hostssl, hostnossl

# Acesso local (socket Unix)
local   all             postgres                peer
local   all             all                     scram-sha-256

# Conexão SSL de rede local
hostssl all             app_user   192.168.1.0/24   scram-sha-256

# Conexão sem SSL (apenas específico)
hostnossl all           read_only  127.0.0.1/32     trust

# Cert authentication
hostssl all             +cert_users 10.0.0.0/8      cert
# cert = valida certificado SSL como autenticação

# LDAP
host    all             all        10.0.0.0/8       ldap \
    ldapserver=ldap.example.com \
    ldapport=389 \
    ldapbasedn="dc=example,dc=com" \
    ldapsearchattribute="uid"

# Negar acesso específico (reject deve vir antes)
host    all             all        10.0.0.0/24      reject
host    all             all        0.0.0.0/0        scram-sha-256

# Métodos de autenticação
# trust         - sem senha (apenas local!)
# password      - senha em texto plano
# md5           - hash MD5
# scram-sha-256 - hash SCRAM-SHA-256 (recomendado)
# cert          - certificado SSL
# ldap          - autenticação LDAP
# reject        - negar acesso
# peer          - usuário SO = usuário PG (local apenas)

pg_hba.conf por aplicação

# Exemplo completo pg_hba.conf
# ------------------------------------------------
# Acesso local
local   all             postgres                peer
local   all             all                     scram-sha-256

# Aplicação web (SSL obrigatório)
hostssl app_db          web_app     10.0.1.0/24     scram-sha-256

# Réplicas (usuário replicator)
hostssl replication     replicator  10.0.2.0/24     scram-sha-256

# Read-only reports (certificado)
hostssl reports_db      report_user 10.0.3.0/24     cert

# Admin via VPN
hostssl all             db_admin    10.0.0.0/24     scram-sha-256

# Negar tudo e liberar específico depois
host    all             all         0.0.0.0/0        reject

Audit Logging

pgAudit

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

-- Configurar no postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'read,write,role,ddl,misc'
pgaudit.log_level = 'notice'
pgaudit.log_relation = on
pgaudit.log_catalog = on
pgaudit.log_level = 'log'

-- Auditoria por role
-- pgaudit.role = 'auditor_role'  (GRANT a roles auditadas)

log_statement

-- Configuração no postgresql.conf
log_statement = 'all'                  -- log de todas as queries
log_duration = on                      -- duração de cada query
log_min_duration_statement = 1000      -- apenas queries > 1s
log_connections = on                   -- log de conexões
log_disconnections = on
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h'

-- Rotação de logs
log_destination = 'csvlog'             -- formato CSV
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1440                -- 1 dia
log_rotation_size = 100MB

Data Redaction

-- PostgreSQL 16+ has data redaction (column-level)
-- Abordagem com view para dados sensíveis

-- Tabela real com dados completos
CREATE TABLE funcionarios (
    id      SERIAL PRIMARY KEY,
    nome    TEXT NOT NULL,
    cpf     CHAR(11) NOT NULL,
    salario NUMERIC(10,2),
    data_nasc DATE
);

-- View com dados mascarados
CREATE VIEW vw_funcionarios AS
SELECT
    id,
    nome,
    '***.' || RIGHT(cpf, 3) AS cpf_mascarado,
    CASE
        WHEN current_user IN ('admin', 'rh_user') THEN salario
        ELSE NULL
    END AS salario,
    data_nasc
FROM funcionarios;

-- ou: coluna redacted via função
CREATE OR REPLACE FUNCTION mask_cpf(cpf TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN '***.' || RIGHT(cpf, 3);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Aplicar em view
CREATE VIEW vw_clientes AS
SELECT id, nome, mask_cpf(cpf) AS cpf, email
FROM clientes;

Lab: Configuração de Segurança

cat > setup-security.sh << 'SCRIPT'
#!/bin/bash
set -euo pipefail

PGHOST="${PGHOST:-localhost}"
PGPORT="${PGPORT:-5432}"
PGUSER="${PGUSER:-postgres}"

echo "=== CONFIGURAÇÃO DE SEGURANÇA ==="

# 1. Roles com privilégios mínimos
echo "[1/4] Criando roles..."

psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" << SQL
-- Role para leitura
CREATE ROLE read_only WITH LOGIN PASSWORD 'read123' INHERIT;
GRANT CONNECT ON DATABASE meubanco TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO read_only;

-- Role para escrita
CREATE ROLE app_user WITH LOGIN PASSWORD 'app123' INHERIT;
GRANT CONNECT ON DATABASE meubanco TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON clientes, pedidos TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Role admin
CREATE ROLE db_admin WITH LOGIN PASSWORD 'admin123' CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE meubanco TO db_admin;

-- Role auditor (sem dados)
CREATE ROLE auditor WITH LOGIN PASSWORD 'audit123' INHERIT;
GRANT CONNECT ON DATABASE meubanco TO auditor;
GRANT SELECT ON auditoria, pg_stat_activity, pg_stat_user_tables TO auditor;
GRANT EXECUTE ON FUNCTION current_setting TO auditor;
SQL

echo "Roles criadas."

# 2. RLS
echo "[2/4] Configurando RLS..."
psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d meubanco << SQL
-- Multi-tenant RLS
ALTER TABLE clientes ADD COLUMN IF NOT EXISTS tenant_id INT DEFAULT 1;
ALTER TABLE clientes ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_policy ON clientes
    USING (tenant_id = current_setting('app.tenant_id')::INT);

-- Política para pedidos
ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;
CREATE POLICY pedidos_tenant ON pedidos
    USING (cliente_id IN (
        SELECT id FROM clientes WHERE tenant_id = current_setting('app.tenant_id')::INT
    ));
SQL

# 3. SSL
echo "[3/4] Configurando SSL..."
if [[ ! -f /etc/ssl/certs/server.crt ]]; then
    sudo openssl req -new -x509 -days 365 -nodes \
        -out /etc/ssl/certs/server.crt \
        -keyout /etc/ssl/private/server.key \
        -subj "/CN=$PGHOST" 2>/dev/null
    sudo chmod 600 /etc/ssl/private/server.key
    sudo chown postgres:postgres /etc/ssl/private/server.key
fi

# 4. pg_hba.conf
echo "[4/4] Atualizando pg_hba.conf..."
cat << 'HBA' | sudo tee /etc/postgresql/16/main/pg_hba.conf
# LOCAL
local   all             postgres                peer
local   all             all                     scram-sha-256

# SSL REQUIRED
hostssl all             app_user   10.0.0.0/8     scram-sha-256
hostssl all             read_only  10.0.0.0/8     scram-sha-256
hostssl replication     replicator 10.0.0.0/8     scram-sha-256

# WITHOUT SSL (apenas rede interna)
hostnossl all           db_admin   127.0.0.1/32    scram-sha-256

# REJECT ALL OTHERS
host    all             all        0.0.0.0/0       reject
host    all             all        ::0/0           reject
HBA

sudo systemctl reload postgresql
echo "Setup de segurança concluído!"
SCRIPT

chmod +x setup-security.sh

Roles com privilégios mínimos (least privilege). GRANT em schema, tabela, coluna e função. RLS isola dados por tenant/usuário. pg_hba.conf com scram-sha-256 e SSL. pgAudit para auditoria completa.