Guia Prático de Roles e GRANT no PostgreSQL

1. Conceitos Fundamentais

No PostgreSQL:

• Role é a entidade de controle de permissões (pode representar um usuário ou um grupo).
• Uma Role pode:
  • Fazer login (ser um usuário) → atributo LOGIN
  • Conter outras roles (funciona como grupo)
  • Ter permissões específicas (CREATE, SUPERUSER, REPLICATION etc.)

🔹 Usuário = Role com LOGIN
🔹 Grupo = Role sem LOGIN

---

2. Criando Roles

-- Criar um usuário com login e senha
CREATE ROLE felipe LOGIN PASSWORD 'SenhaForte!123';

-- Criar um grupo (sem login)
CREATE ROLE dev_team;

-- Criar com atributos adicionais
CREATE ROLE admin LOGIN PASSWORD 'SenhaMuitoForte!' SUPERUSER CREATEDB CREATEROLE;

Atributos comuns:

• LOGIN → pode se conectar
• SUPERUSER → sem restrições
• CREATEDB → pode criar bancos
• CREATEROLE → pode criar/gerenciar roles
• INHERIT → herda permissões de roles associadas (padrão)
• NOCREATEROLE / NOCREATEDB → remove privilégios

---

3. Concedendo Roles (Herança de Permissões)

-- Adicionar um usuário ao grupo
GRANT dev_team TO felipe;

-- Remover usuário do grupo
REVOKE dev_team FROM felipe;

• Com INHERIT (padrão) → permissões do grupo passam automaticamente.
• Sem INHERIT → precisa usar SET ROLE para assumir privilégios.

---

4. Tipos de Privilégios que Podem Ser Concedidos

Objeto	Privilégios Disponíveis
DATABASE	CONNECT, CREATE, TEMP
SCHEMA	USAGE, CREATE
TABLE/VIEW	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
SEQUENCE	USAGE, SELECT, UPDATE
FUNCTION	EXECUTE
TYPE	USAGE

---

5. Exemplo Prático Completo

5.1 Criar usuários e grupos

CREATE ROLE app_user LOGIN PASSWORD 'senha_app';
CREATE ROLE leitura;
CREATE ROLE escrita;

5.2 Conceder privilégios no banco

GRANT CONNECT ON DATABASE minha_base TO app_user;
GRANT USAGE ON SCHEMA public TO leitura, escrita;

5.3 Privilégios em tabelas

GRANT SELECT ON ALL TABLES IN SCHEMA public TO leitura;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO escrita;

5.4 Tornar permissões padrão para tabelas futuras

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO leitura;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO escrita;

5.5 Associar usuários aos grupos

GRANT leitura TO app_user;
GRANT escrita TO app_user;

---

6. Revogando Permissões

REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM leitura;
REVOKE escrita FROM app_user;

---

7. Consultando Permissões e Roles

\du                  -- Listar roles existentes
\z tabela_exemplo    -- Mostrar privilégios de tabelas
SELECT rolname, rolcanlogin, rolsuper FROM pg_roles; -- Checar membros

---

8. Boas Práticas de Segurança

• Nunca usar SUPERUSER sem necessidade.
• Separar roles de conexão e roles de privilégio.
  • Ex.: user_api só tem LOGIN e pertence a role_leitura.
• Usar grupos para gerenciar permissões em vez de conceder direto a cada usuário.
• Alterar permissões padrão (ALTER DEFAULT PRIVILEGES) para evitar esquecer de novos objetos.
• Revisar permissões periodicamente (\du, \z).

---

9. Exemplo de Estrutura Robusta para Aplicação

CREATE ROLE app_read;
CREATE ROLE app_write;
CREATE ROLE app_prod LOGIN PASSWORD 'SenhaSegura!';

GRANT USAGE ON SCHEMA public TO app_read, app_write;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_write;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO app_write;

GRANT app_read, app_write TO app_prod;

Case Prático – Construção de Serviços e Banco de Dados Seguros

Objetivo Geral

Integrar todos os conceitos de segurança aprendidos para criar um banco e serviço API seguros, aplicando autenticação, criptografia, auditoria e testes de vulnerabilidades.

---

1. Preparando o Ambiente

1.1 Instalar e iniciar o PostgreSQL

No Linux:

sudo apt update && sudo apt install postgresql postgresql-contrib
sudo service postgresql start

No Windows/Mac: instalar via PostgreSQL Installer ou Docker:

docker run --name postgres-seguro -e POSTGRES_PASSWORD=admin123 -p 5432:5432 -d postgres:15

---

2. Criação e Modelagem Segura do Banco

2.1 Criar banco e usuário de aplicação

-- Criar usuário de aplicação com senha forte
CREATE ROLE app_user LOGIN PASSWORD 'SenhaForte!@123';

-- Criar banco de dados
CREATE DATABASE empresa_segura OWNER app_user;

-- Restringir permissões
REVOKE ALL ON DATABASE empresa_segura FROM PUBLIC;
GRANT CONNECT ON DATABASE empresa_segura TO app_user;

2.2 Criar tabelas com boas práticas

\c empresa_segura

-- Criar schema
CREATE SCHEMA sistema AUTHORIZATION app_user;

-- Tabela de usuários
CREATE TABLE sistema.usuarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    senha_hash TEXT NOT NULL, -- senha armazenada como hash
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de registros de acesso
CREATE TABLE sistema.logs_acesso (
    id SERIAL PRIMARY KEY,
    usuario_id INT REFERENCES sistema.usuarios(id),
    ip_origem VARCHAR(45),
    data_hora TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    acao TEXT
);

---

3. Inserindo Dados com Criptografia e Hash

CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO sistema.usuarios (nome, email, senha_hash)
VALUES (
    'Admin',
    '[email protected]',
    crypt('SenhaUltraSegura123!', gen_salt('bf'))
);

---

4. Criando a API Segura

4.1 Exemplo em Node.js + Express

import express from "express";
import pkg from "pg";
import bcrypt from "bcrypt";
import jwt from "jsonwebtoken";

const { Pool } = pkg;
const pool = new Pool({
  user: "app_user",
  host: "localhost",
  database: "empresa_segura",
  password: "SenhaForte!@123",
  port: 5432,
  ssl: false // Em produção, usar SSL!
});

const app = express();
app.use(express.json());

app.post("/login", async (req, res) => {
  const { email, senha } = req.body;
  const { rows } = await pool.query("SELECT * FROM sistema.usuarios WHERE email=$1", [email]);

  if (rows.length === 0) return res.status(401).json({ erro: "Credenciais inválidas" });

  const usuario = rows[0];
  const senhaValida = await bcrypt.compare(senha, usuario.senha_hash);

  if (!senhaValida) return res.status(401).json({ erro: "Credenciais inválidas" });

  const token = jwt.sign({ id: usuario.id }, "segredo", { expiresIn: "1h" });
  res.json({ token });
});

app.listen(3000, () => console.log("API Segura rodando na porta 3000"));

Boas práticas aplicadas:

• Uso de hash (bcrypt) para senhas.
• JWT para autenticação.
• Consultas parametrizadas para evitar SQL Injection.

---

5. Implementando Logs e Auditoria

CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'read, write';
SELECT pg_reload_conf();

---

6. Testando Segurança

6.1 Teste com SQLMap

sqlmap -u "http://localhost:3000/login" --data="[email protected]&senha=123" --batch

6.2 Teste com OWASP ZAP

1. Abrir OWASP ZAP.
2. Configurar como proxy do navegador.
3. Navegar na API e realizar scan.

6.3 Análise Estática com SonarQube

sonar-scanner -Dsonar.projectKey=api_segura -Dsonar.sources=. -Dsonar.host.url=http://localhost:9000

---

7. Checklist LGPD/GDPR

✅ Coleta apenas dados necessários.
✅ Consentimento informado (em endpoints de cadastro).
✅ Senhas armazenadas de forma segura.
✅ Criptografia em trânsito (HTTPS).
✅ Plano de resposta a incidentes.

---

8. Resultado Esperado

• Banco PostgreSQL seguro com controle de acesso.
• API protegida contra ataques comuns (SQL Injection, vazamento de dados).
• Logs e auditoria habilitados.
• Testes de vulnerabilidade realizados e corrigidos.
• Sistema em conformidade básica com LGPD/GDPR.

---

Case Prático 2 – Testando Ataques e Implementando Camadas de Segurança

1. Cenário

Criar um ambiente controlado com PostgreSQL e uma API vulnerável que permita SQL Injection.
Depois, aplicar as correções e implementar camadas de segurança para proteção contra ataques comuns.

---

2. Preparando o Ambiente

2.1 Banco vulnerável

CREATE DATABASE seguranca_teste;
\c seguranca_teste;

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    senha VARCHAR(100) NOT NULL
);

INSERT INTO usuarios (nome, email, senha)
VALUES
('Admin', '[email protected]', 'senha123'),
('João', '[email protected]', 'abc123');

---

3. API Vulnerável (Exemplo em Node.js)

import express from "express";
import pkg from "pg";
const { Pool } = pkg;

const pool = new Pool({
  user: "postgres",
  host: "localhost",
  database: "seguranca_teste",
  password: "postgres",
  port: 5432
});

const app = express();
app.use(express.json());

app.post("/login", async (req, res) => {
  const { email, senha } = req.body;

  // ❌ Consulta vulnerável
  const query = `SELECT * FROM usuarios WHERE email = '${email}' AND senha = '${senha}'`;
  const result = await pool.query(query);

  if (result.rows.length > 0) {
    res.json({ sucesso: true, usuario: result.rows[0] });
  } else {
    res.status(401).json({ sucesso: false });
  }
});

app.listen(3000, () => console.log("API vulnerável rodando na porta 3000"));

---

4. Testando o Ataque

Usando SQLMap:

sqlmap -u "http://localhost:3000/login" --data="[email protected]&senha=123" --batch --level=5 --risk=3

Ou manualmente via Postman:

{
  "email": "[email protected]",
  "senha": "' OR '1'='1"
}

✅ Resultado: o sistema loga sem precisar da senha correta.

---

5. Aplicando Camadas de Segurança

5.1 Prevenindo SQL Injection

const result = await pool.query(
  "SELECT * FROM usuarios WHERE email = $1 AND senha = $2",
  [email, senha]
);

5.2 Hashing de Senhas

import bcrypt from "bcrypt";

const hash = await bcrypt.hash(senha, 10);
await pool.query(
  "INSERT INTO usuarios (nome, email, senha) VALUES ($1, $2, $3)",
  [nome, email, hash]
);

const senhaValida = await bcrypt.compare(senhaDigitada, senhaHashBanco);

5.3 Controle de Acesso

import jwt from "jsonwebtoken";

const token = jwt.sign({ id: usuario.id }, "chave-secreta", { expiresIn: "1h" });

5.4 Criptografia em Trânsito

No pg_hba.conf:

hostssl seguranca_teste app_user 0.0.0.0/0 md5

5.5 Auditoria com PgAudit

CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'read, write';
SELECT pg_reload_conf();

---

6. Testando Novamente

sqlmap -u "http://localhost:3000/login" --data="[email protected]&senha=' OR '1'='1" --batch

✅ Resultado esperado: falha do ataque (nenhum registro retornado).

---

7. Camadas de Segurança Resumidas

1. Validação e sanitização de entrada (não confiar no cliente).
2. Queries parametrizadas.
3. Hashing de senhas (bcrypt/Argon2).
4. Controle de acesso (JWT/RBAC).
5. Criptografia em trânsito (TLS/SSL).
6. Auditoria e monitoramento (PgAudit).
7. Backups seguros e testados.

---

8. Resultado Esperado

• API segura contra SQL Injection.
• Senhas protegidas.
• Tráfego criptografado.
• Logs e auditoria ativos.
• Prática de ataque + defesa consolidada.