Role somente leitura no PostgreSQL: permissoes corretas para analytics e BI

Analistas e ferramentas de BI precisam ler seus dados sem o poder de altera-los. No PostgreSQL a resposta limpa e uma role dedicada de somente leitura. Aqui esta uma receita que funciona e a pegadinha que derruba quase todo mundo.

Criar a role e conceder acesso ao schema

Uma role de somente leitura costuma ser uma role de grupo (sem login) a qual os usuarios reais sao adicionados. Os privilegios ficam no grupo; voce so adiciona e remove pessoas uma a uma.

CREATE ROLE readonly NOLOGIN;

CREATE ROLE analyst_anna LOGIN PASSWORD 'change_me';
GRANT readonly TO analyst_anna;

O primeiro passo pouco obvio e o USAGE no schema. Sem ele a role nao enxerga uma unica tabela dentro de public, mesmo que as permissoes no nivel de tabela ja estejam concedidas.

GRANT USAGE ON SCHEMA public TO readonly;

O USAGE abre o schema para que seu conteudo fique visivel, mas ainda nao deixa fazer nada com as tabelas. Isso e o proximo grant.

SELECT nas tabelas existentes

Agora conceda acesso de leitura a todas as tabelas que ja vivem no schema. Um unico comando com ALL TABLES cobre todas elas.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Depois disso, a role consegue ler os dados mas nao consegue gravar nada:

-- works for readonly
SELECT country, count(*) AS users_cnt
FROM users
GROUP BY country
ORDER BY users_cnt DESC;

-- fails: permission denied
UPDATE orders SET status = 'cancelled' WHERE amount < 0;

Se o schema tem sequences que seus relatorios precisam inspecionar, conceda-as a parte:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

A pegadinha: tabelas novas nao sao legiveis

E aqui que a maioria das configuracoes quebra. GRANT ... ON ALL TABLES e um retrato unico: ele concede permissoes apenas sobre as tabelas que existem naquele momento. Crie employees amanha e o analista bate em um erro de permissao.

CREATE TABLE employees (
  id         bigint PRIMARY KEY,
  name       text NOT NULL,
  manager_id bigint,
  dept       text,
  salary     numeric(12,2)
);

-- readonly gets: permission denied for table employees
SELECT dept, avg(salary) FROM employees GROUP BY dept;

A correcao e o ALTER DEFAULT PRIVILEGES. E uma regra voltada para o futuro: qualquer tabela criada dali em diante concede automaticamente SELECT a nossa role.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

Importante: os privilegios padrao estao atrelados a role que cria os objetos. A regra acima dispara para tabelas criadas pela role atual. Se as migracoes rodam como outro usuario (por exemplo app_owner), defina-a de forma explicita: ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO readonly;. Caso contrario, as tabelas novas voltam a ficar fora do acesso.

A ordem correta e: primeiro ALTER DEFAULT PRIVILEGES para as tabelas futuras, depois GRANT ... ON ALL TABLES para as existentes. Juntos eles cobrem passado e futuro.

Acesso para analytics e BI

As ferramentas de BI (Metabase, Superset, Looker) se conectam com esta role e montam dashboards sem dor; fisicamente nao conseguem gravar no banco. Para limitar a carga, defina um tempo maximo por comando:

ALTER ROLE readonly SET statement_timeout = '30s';

Se o acesso deve ser parcial, nao exponha as tabelas cruas por inteiro. Entregue uma view e conceda SELECT nela:

CREATE VIEW orders_public AS
SELECT id, user_id, amount, status, created_at
FROM orders
WHERE status <> 'fraud';

GRANT SELECT ON orders_public TO readonly;

Diferencas em outros bancos

• MySQL: schemas e bancos sao a mesma coisa, entao nao ha um USAGE ON SCHEMA separado. Uma role de somente leitura se monta assim: GRANT SELECT ON shop.* TO 'readonly'@'%';. O padrao shop.* cobre as tabelas futuras de forma automatica, entao nao e preciso um equivalente do ALTER DEFAULT PRIVILEGES.
• ClickHouse: use GRANT SELECT ON shop.* TO readonly;, enquanto as restricoes costumam ser expressas por perfis e cotas (readonly = 1) em vez de permissoes por objeto.

Lembre do essencial: uma role de grupo sem login, USAGE no schema, SELECT nas tabelas existentes e ALTER DEFAULT PRIVILEGES para as futuras. Pule o ultimo passo e o acesso quebra em silencio na proxima migracao.