RBAC clássico: roles, permissions, e seus limites

-- Entidades base
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE roles (
  id UUID PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,        -- 'admin', 'editor', 'viewer'
  parent_role_id UUID REFERENCES roles(id)  -- RBAC1 hierarchy
);

CREATE TABLE permissions (
  id UUID PRIMARY KEY,
  resource TEXT NOT NULL,           -- 'invoices', 'users'
  action TEXT NOT NULL,             -- 'read', 'write', 'delete'
  UNIQUE (resource, action)
);

-- Associações N:N
CREATE TABLE user_roles (
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
  assigned_at TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (user_id, role_id)
);
CREATE INDEX idx_user_roles_user ON user_roles(user_id);

CREATE TABLE role_permissions (
  role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
  permission_id UUID REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

-- Dado um user_id, retornar todas as perms efetivas (incluindo herdadas)
WITH RECURSIVE role_tree AS (
  -- base: roles diretas do user
  SELECT r.id, r.parent_role_id
  FROM roles r
  JOIN user_roles ur ON ur.role_id = r.id
  WHERE ur.user_id = $1

  UNION

  -- step: sobe na hierarquia, mas espera, herança é DESCENDENTE
  -- se editor herda de viewer, queremos perms de viewer ao olhar editor
  SELECT child.id, child.parent_role_id
  FROM roles child
  JOIN role_tree rt ON child.parent_role_id = rt.id
)
SELECT DISTINCT p.resource, p.action
FROM role_tree rt
JOIN role_permissions rp ON rp.role_id = rt.id
JOIN permissions p ON p.id = rp.permission_id;

-- SoD estática modelada em SQL via tabela de pares conflitantes
CREATE TABLE role_conflicts (
  role_a UUID REFERENCES roles(id),
  role_b UUID REFERENCES roles(id),
  PRIMARY KEY (role_a, role_b)
);

-- Trigger que bloqueia INSERT em user_roles violando conflito
CREATE FUNCTION check_sod() RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN role_conflicts rc
      ON (rc.role_a = NEW.role_id AND rc.role_b = ur.role_id)
      OR (rc.role_b = NEW.role_id AND rc.role_a = ur.role_id)
    WHERE ur.user_id = NEW.user_id
  ) THEN
    RAISE EXCEPTION 'SoD violation: conflicting role';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_sod BEFORE INSERT ON user_roles
  FOR EACH ROW EXECUTE FUNCTION check_sod();

// Decorator-style authz middleware
import type { Request, Response, NextFunction } from 'express';

type Permission = `${string}:${string}`;  // 'invoices:read'

export function requires(perm: Permission) {
  const [resource, action] = perm.split(':');
  return async (req: Request, res: Response, next: NextFunction) => {
    const user = req.user;  // garantido por authn middleware anterior
    if (!user) return res.status(401).json({ error: 'unauthenticated' });

    const perms = await getEffectivePermissions(user.id);  // cache redis
    const allowed = perms.has(`${resource}:${action}`);

    // Audit log — sempre, permitido ou não
    audit.log({
      user_id: user.id,
      resource, action,
      decision: allowed ? 'permit' : 'deny',
      ts: new Date(),
    });

    if (!allowed) return res.status(403).json({ error: 'forbidden' });
    return next();
  };
}

// Uso
app.get('/invoices/:id', requires('invoices:read'), handleGetInvoice);
app.delete('/users/:id', requires('users:delete'), handleDeleteUser);