Aula 20: Usuários, roles e permissões no PostgreSQL

Nesta aula do curso PostgreSQL — Do Zero ao Avançado, você vai dominar um dos pilares mais críticos da administração de bancos de dados: o gerenciamento de usuários, roles e permissões no PostgreSQL. Diferentemente de outros SGBDs, o PostgreSQL adota um modelo unificado em que roles representam tanto usuários de login quanto grupos de privilégios, e entender essa arquitetura é fundamental para garantir a segurança, a conformidade e a organização de qualquer ambiente de produção. Em nossa experiência diária nos projetos da JRT Technology Solutions, observamos que a maioria das brechas de segurança em bancos PostgreSQL decorre de permissões mal configuradas ou da utilização indiscriminada do superusuário — problemas que esta aula vai ensinar você a evitar desde o início.

Ao longo desta aula, você aprenderá a criar roles com atributos específicos, conceder e revogar privilégios em diferentes objetos do banco, configurar a autenticação no arquivo pg_hba.conf, verificar permissões do catálogo e aplicar boas práticas de least privilege (menor privilégio). Tudo isso com comandos reais, saídas esperadas e exemplos práticos que podem ser reproduzidos em qualquer instância PostgreSQL. Este conteúdo foi desenhado para o nível intermediário do curso, assumindo que você já concluiu as aulas anteriores e possui familiaridade com o terminal, o cliente psql e conceitos básicos de SQL como CREATE DATABASE e CREATE TABLE.

Por que essa aula importa tanto? Imagine um servidor PostgreSQL que hospeda múltiplas aplicações: cada aplicação deve possuir credenciais próprias, com acesso apenas às bases e tabelas que realmente necessita. Sem um controle fino de permissões, um simples erro de configuração pode permitir que um usuário leia dados sensíveis de outros sistemas ou até apague informações críticas. Ao concluir este conteúdo, você estará apto a projetar e implementar uma matriz de permissões sólida, auditar quem tem acesso a quê e responder de forma rápida a incidentes envolvendo privilégios.

Os pré-requisitos para esta aula incluem uma instalação funcional do PostgreSQL (versão 12 ou superior), acesso ao terminal como usuário com privilégios administrativos e um editor de texto para modificar arquivos de configuração. Você não precisa de nenhuma ferramenta extra, pois todos os comandos utilizam recursos nativos do PostgreSQL — psql, pg_ctl, systemctl e o catálogo pg_catalog. Caso ainda não tenha o PostgreSQL instalado, revise a Aula 3 deste curso antes de prosseguir.

Ao final da aula, você terá construído um ambiente com roles de grupo, usuários de aplicação com permissões granulares e uma configuração de autenticação robusta baseada em scram-sha-256. Consequentemente, será capaz de aplicar essas práticas em servidores próprios ou em infraestruturas corporativas, exatamente como fazem os especialistas da JRT Technology Solutions em implementações de alta disponibilidade e segurança para clientes de médio e grande porte.

O que você vai aprender nesta aula

Antes de colocar a mão na massa, é importante ter clareza sobre os objetivos de aprendizado desta etapa do curso. Vamos trabalhar com a manipulação de usuários, roles e permissões de forma progressiva, iniciando pelos fundamentos teóricos e avançando para a configuração prática, até chegar à auditoria e às boas práticas de segurança. Abaixo, listamos os principais tópicos que serão abordados:

• Conceito de roles: entender por que no PostgreSQL não existe uma entidade separada chamada “usuário” — tudo é role, e a diferença está nos atributos.
• Atributos de roles: dominar LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, PASSWORD e demais opções do comando CREATE ROLE.
• Privilégios no PostgreSQL: compreender a granularidade dos comandos GRANT e REVOKE em bancos, esquemas, tabelas, sequências, funções e outros objetos.
• Arquivo pg_hba.conf: configurar regras de autenticação baseadas em host, banco, usuário e método, com foco em scram-sha-256 e peer.
• Verificação e auditoria: consultar o catálogo para identificar permissões concedidas e solucionar problemas de negação de acesso.
• Boas práticas: aplicar o princípio do menor privilégio, usar roles de grupo, evitar superusuário em aplicações e automatizar a gestão de permissões.

Esses conhecimentos formam a base para aulas futuras do curso, incluindo replicação, alta disponibilidade e otimização de desempenho, nas quais o controle de acesso desempenha papel essencial. Prepare seu ambiente e acompanhe cada comando com atenção — a prática constante é o que separa um administrador iniciante de um profissional de segurança e infraestrutura.

Pré-requisitos e Ambiente

Antes de começar, você precisa de uma instalação do PostgreSQL em funcionamento. Nesta aula, mostraremos procedimentos compatíveis com as duas principais famílias de distribuições Linux: Ubuntu/Debian e CentOS/RHEL/Rocky Linux. Embora os comandos de instalação diferem entre si, o comportamento do PostgreSQL e os arquivos de configuração são os mesmos após a instalação, com pequenas variações nos caminhos dos diretórios.

Para sistemas baseados em Debian, como Ubuntu 22.04 ou Debian 12, o PostgreSQL normalmente reside em /etc/postgresql/<versão>/main/ e o serviço é gerenciado pelo systemd com o nome postgresql. Já em distribuições baseadas em RHEL, como Rocky Linux 9 ou CentOS Stream 9, o diretório de dados fica em /var/lib/pgsql/<versão>/data/ e o serviço é postgresql-<versão>. Verifique qual é o seu caso executando os comandos abaixo.

O primeiro passo é confirmar se o PostgreSQL está ativo e acessível. Em ambos os sistemas, o superusuário padrão é postgres, criado automaticamente durante a instalação. Para conectar-se ao banco, você pode usar o comando sudo -u postgres psql, que troca para o usuário do sistema operacional postgres e executa o cliente interativo.

# Verificar o status do serviço no Ubuntu/Debian
sudo systemctl status postgresql

# Verificar o status do serviço no CentOS/RHEL/Rocky Linux
sudo systemctl status postgresql-15

# Conectar-se ao PostgreSQL como superusuário (ambos os sistemas)
sudo -u postgres psql -c "SELECT version();"

Esse último comando retorna a versão exata do servidor, o que ajuda a confirmar que o cliente e o servidor estão compatíveis. A saída esperada deve conter uma linha semelhante a:

                                                        version                                                        
-------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.8 (Ubuntu 15.8-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 15.8
(1 row)

Além do serviço ativo, você precisará de um editor de texto para modificar o pg_hba.conf — recomendamos nano ou vim. Se estiver em um servidor remoto, certifique-se de ter acesso SSH e permissões de sudo. Outro pré-requisito importante é ter concluído as aulas anteriores, pois você deve saber criar bancos, tabelas e utilizar comandos básicos do psql, como \l, \c e \dt. Todos os exemplos desta aula partem do pressuposto de que existe uma base chamada appdb e um esquema public, padrão do PostgreSQL.

Por fim, recomendamos que você faça um backup lógico de qualquer banco de produção antes de testar comandos de revogação de permissões. Embora os procedimentos desta aula sejam seguros, é sempre prudente trabalhar em um ambiente de desenvolvimento ou laboratório. Em nossos treinamentos na JRT Technology Solutions, orientamos os alunos a criarem uma instância isolada para práticas, evitando impactos em dados reais.

Conceitos Fundamentais — Usuários, roles e permissões no PostgreSQL

No PostgreSQL, o termo role (papel) abrange tanto usuários de login quanto grupos de permissões. Ao contrário do MySQL ou do SQL Server, onde existem contas de usuário separadas de grupos ou roles, aqui a entidade é única: um role pode ou não ter o atributo LOGIN. Quando um role possui LOGIN, ele pode autenticar-se no servidor; quando não possui, funciona como um grupo, servindo apenas para agregar privilégios e ser concedido a outros roles. Essa flexibilidade simplifica a administração e permite criar hierarquias de permissões de forma elegante.

Cada role possui um conjunto de atributos que definem suas capacidades. O atributo SUPERUSER, por exemplo, concede todos os privilégios possíveis e ignora qualquer verificação de permissão — por isso deve ser usado com extrema cautela. Outros atributos importantes incluem CREATEDB, que permite criar bancos de dados, CREATEROLE, que permite criar e gerenciar outros roles, e REPLICATION, necessário para tarefas de replicação e backup físico. A tabela abaixo resume os atributos mais relevantes que você pode definir ao criar ou alterar um role:

Além dos atributos, o PostgreSQL implementa um sistema de privilégios extremamente granular, baseado nos comandos GRANT e REVOKE. Os privilégios são aplicados a objetos específicos — como bancos, esquemas, tabelas, sequências, funções e tablespaces — e podem ser concedidos a roles individuais ou a grupos. Por exemplo, o privilégio SELECT em uma tabela permite ler suas linhas, enquanto INSERT, UPDATE e DELETE correspondem às operações de escrita. O privilégio USAGE é necessário para acessar esquemas e sequências, e EXECUTE para executar funções. Essa granularidade permite que você restrinja um usuário a apenas ler uma tabela específica, sem conceder acesso a todo o banco.

Para visualizar os roles existentes e seus atributos, você pode utilizar o comando \du dentro do psql ou consultar a visão pg_roles. A consulta abaixo retorna os principais campos de cada role, incluindo se pode logar, se é superusuário, se pode criar bancos e se a senha está definida:

-- Listar roles e seus atributos a partir do catálogo
SELECT rolname,
       rolcanlogin AS pode_logar,
       rolsuper AS superusuario,
       rolcreatedb AS pode_criar_bd,
       rolcreaterole AS pode_criar_role,
       rolvaliduntil AS valido_ate
FROM pg_roles
ORDER BY rolname;

A execução desse comando em uma instalação padrão retorna algo como:

 rolname   | pode_logar | superusuario | pode_criar_bd | pode_criar_role | valido_ate 
-----------+------------+--------------+---------------+-----------------+------------
 postgres  | t          | t            | t             | t               | 
(1 row)

Observe que o role postgres, criado por padrão, possui todos os atributos habilitados. Em produção, você nunca deve utilizar esse role para aplicações — a prática correta é criar roles específicos com o mínimo de privilégios necessário. Esse entendimento dos fundamentos é o alicerce para as seções práticas a seguir, onde você criará seus primeiros roles de grupo e usuários de aplicação.

Passo a Passo — Criando roles e usuários do zero

Agora que você compreende a teoria, vamos partir para a prática. Nesta seção, você criará uma estrutura de roles que simula um cenário real: um role de grupo chamado app_readonly, um usuário de aplicação chamado app_user e um administrador chamado dba_admin. O objetivo é demonstrar como conceder privilégios de leitura em um banco específico e verificar o resultado de cada etapa. Siga os passos na ordem exata.

O procedimento será executado inteiramente dentro do psql, como superusuário postgres. Primeiro, conecte-se ao servidor com o comando sudo -u postgres psql. Em seguida, siga a sequência numerada abaixo para criar os roles e aplicar as permissões. Cada comando será explicado linha por linha logo após o bloco de código.

1. Criar o role de grupo app_readonly sem privilégio de login, pois ele será apenas um agregador de permissões.
2. Criar o usuário de aplicação app_user com login habilitado e senha forte.
3. Criar o role de administrador dba_admin com permissão para criar bancos e roles, mas sem superusuário completo.
4. Conceder o role de grupo app_readonly ao app_user, para que ele herde os privilégios do grupo.
5. Conceder privilégios de leitura no banco appdb ao grupo app_readonly.
6. Testar a conexão e os privilégios com o usuário app_user.

Execute os comandos abaixo no psql. Note que utilizamos o comando \c appdb para conectar ao banco de aplicação antes de conceder permissões no banco correto.

-- Passo 1: Criar role de grupo sem login (apenas agrega privilégios)
CREATE ROLE app_readonly;

-- Passo 2: Criar usuário de aplicação com login e senha
CREATE ROLE app_user WITH LOGIN PASSWORD 'Str0ng@Passw0rd!';

-- Passo 3: Criar role de administrador com privilégios limitados
CREATE ROLE dba_admin WITH LOGIN CREATEDB CREATEROLE PASSWORD 'Adm!n@2026';

-- Passo 4: Conceder o grupo app_readonly ao usuario app_user
-- Isso faz com que app_user herde todos os privilégios de app_readonly
GRANT app_readonly TO app_user;

-- Passo 5: Conectar ao banco de aplicação
-- Em uma instalação real, o banco appdb já deve existir (crie com CREATE DATABASE appdb; se necessário)
\c appdb

-- Passo 6: Conceder privilégio de conexão ao banco appdb para o grupo
GRANT CONNECT ON DATABASE appdb TO app_readonly;

-- Passo 7: Conceder privilégio de uso do esquema public ao grupo
GRANT USAGE ON SCHEMA public TO app_readonly;

-- Passo 8: Conceder SELECT em todas as tabelas existentes no esquema public
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

-- Passo 9: Garantir que futuras tabelas do esquema public também tenham SELECT para o grupo
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

Vamos analisar cada comando. O CREATE ROLE app_readonly; cria um role sem atributo LOGIN, portanto incapaz de autenticar-se — servirá apenas como grupo. O CREATE ROLE app_user WITH LOGIN PASSWORD '...'; cria um role que pode se conectar, com senha definida. O CREATE ROLE dba_admin WITH LOGIN CREATEDB CREATEROLE PASSWORD '...'; cria um administrador com poderes de criar bancos e roles, mas sem superusuário, seguindo o princípio do menor privilégio. O comando GRANT app_readonly TO app_user; estabelece a associação de grupo, fazendo com que app_user herde os privilégios que forem concedidos a app_readonly.

Após a conexão ao banco appdb, o comando GRANT CONNECT ON DATABASE appdb TO app_readonly; permite que os membros do grupo estabeleçam conexão com esse banco. Sem esse privilégio, o usuário não consegue nem entrar no banco. Em seguida, GRANT USAGE ON SCHEMA public TO app_readonly; permite acessar o esquema public — sem USAGE, o usuário não pode referenciar objetos dentro do esquema, mesmo que tenha SELECT na tabela. O GRANT SELECT ON ALL TABLES IN SCHEMA public concede leitura em todas as tabelas existentes no momento. Por fim, o ALTER DEFAULT PRIVILEGES garante que tabelas criadas futuramente herdem automaticamente o privilégio de leitura para o grupo.

Para verificar se a criação foi bem-sucedida, execute o comando \du no psql. A saída deve listar os roles criados, com a associação de grupo indicada na coluna Member of:

                                   List of roles
 Role name   |                         Attributes                         |    Member of    
-------------+------------------------------------------------------------+-----------------
 app_user    |                                                            | {app_readonly}
 dba_admin   | Create DB, Create role                                     | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 app_readonly| Cannot login                                               | {}

(4 rows)

Observe que app_readonly aparece com o atributo Cannot login, indicando que é um grupo. O role app_user está listado como membro de app_readonly, confirmando a associação. O dba_admin possui apenas Create DB e Create role, sem superusuário. Esta estrutura já é suficiente para controlar o acesso de aplicações com segurança.

Configuração Detalhada — pg_hba.conf e autenticação

O arquivo pg_hba.conf (Host-Based Authentication) controla como os clientes se autenticam no PostgreSQL. Cada regra define o tipo de conexão, o banco de dados, o usuário, o endereço de origem e o método de autenticação. O PostgreSQL processa as regras em ordem, da primeira à última, e a primeira correspondência é aplicada. Portanto, a ordem das entradas é crucial: uma regra muito permissiva no início pode bloquear regras mais restritivas definidas posteriormente.

Em sistemas Ubuntu/Debian, o arquivo reside em /etc/postgresql/<versão>/main/pg_hba.conf. Em CentOS/RHEL/Rocky Linux, fica em /var/lib/pgsql/<versão>/data/pg_hba.conf. Você pode localizar o arquivo com o comando SHOW hba_file; dentro do psql. Para editar, utilize sudo nano <caminho> ou sudo vim <caminho>. Após modificar o arquivo, recarregue a configuração sem reiniciar o serviço com SELECT pg_reload_conf(); ou sudo systemctl reload postgresql (ajuste o nome do serviço conforme sua distribuição).

Abaixo apresentamos um exemplo completo e comentado do arquivo pg_hba.conf para um ambiente de produção. Este modelo utiliza scram-sha-256 para conexões remotas, que é o método recomendado atualmente, e peer para conexões locais do usuário postgres. Ele também inclui regras para replicação e redes internas, demonstrando como segregar acessos por origem e banco.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# ---------------------------------------------------------------
# "local" é para conexões via socket Unix
local   all             postgres                                peer
local   all             all                                     scram-sha-256
# Conexões IPv4 locais (loopback)
host    all             all             127.0.0.1/32            scram-sha-256
# Conexões IPv6 locais
host    all             all             ::1/128                 scram-sha-256
# Rede interna de produção (ajuste o CIDR conforme sua topologia)
host    appdb           app_readonly    10.0.0.0/24             scram-sha-256
host    appdb           app_user        10.0.0.0/24             scram-sha-256
# Banimento de todas as outras conexões remotas ao appdb
host    all             all             0.0.0.0/0               reject
# Regras de replicação
host    replication     replica         10.0.1.0/24             scram-sha-256
host    replication     all             0.0.0.0/0               reject

Cada linha do arquivo contém cinco campos principais, separados por espaços ou tabulações. O campo TYPE pode ser local (socket Unix), host (TCP/IP com ou sem SSL), hostssl (somente SSL) ou hostnossl (somente sem SSL). O campo DATABASE pode ser um nome específico, all ou uma lista separada por vírgulas. O campo USER aceita nomes de roles, all ou grupos com prefixo +. O campo ADDRESS usa notação CIDR para restringir origens de rede. Por fim, o campo METHOD define o método de autenticação, como scram-sha-256, md5, peer, ident ou reject.

É importante notar a ordem das regras: a regra para o postgres via peer garante que o superusuário local não precise de senha, pois a autenticação é feita pelo usuário do sistema operacional. As regras seguintes restringem o banco appdb a redes internas específicas e, finalmente, a regra reject bloqueia qualquer outra tentativa remota. A tabela abaixo compara os principais métodos de autenticação:

Após editar o arquivo, é imprescindível recarregar a configuração. O comando SELECT pg_reload_conf(); pode ser executado diretamente no psql como superusuário, ou você pode usar o systemctl. A saída esperada para o comando SQL é simples, indicando sucesso:

-- Recarregar a configuração sem reiniciar o servidor
SELECT pg_reload_conf();

-- Verificar se as regras foram carregadas
SELECT * FROM pg_hba_file_rules ORDER BY line_number;

 pg_reload_conf 
----------------
 t
(1 row)

A consulta a pg_hba_file_rules exibe todas as regras interpretadas pelo servidor, permitindo confirmar que o arquivo foi lido corretamente. Se houver um erro de sintaxe, o PostgreSQL registra o problema nos logs e mantém a configuração anterior ativa. Portanto, sempre verifique os logs após alterações no pg_hba.conf, especialmente em servidores de produção.

Gerenciando Usuários, roles e permissões no dia a dia

No cotidiano de um administrador de banco, as tarefas envolvem alterar atributos de roles, conceder e revogar privilégios em diferentes objetos e auditar o que já foi concedido. Esta seção apresenta os comandos essenciais para esse gerenciamento, com exemplos práticos e referência rápida. Você aprenderá a usar ALTER ROLE, GRANT, REVOKE e ALTER DEFAULT PRIVILEGES de forma sistemática.

O comando ALTER ROLE permite modificar atributos de um role existente. Por exemplo, para alterar a senha do app_user, conceder o atributo CREATEDB ao dba_admin ou definir uma data de expiração para um usuário temporário, você pode executar os comandos abaixo. Cada alteração é aplicada imediatamente e afeta as próximas conexões; conexões já estabelecidas não são interrompidas, exceto se você revogar o privilégio de conexão.

-- Alterar a senha de um usuário (use sempre senhas fortes)
ALTER ROLE app_user WITH PASSWORD 'N0va@Senha!2026';

-- Conceder atributo CREATEDB a um role admin limitado
ALTER ROLE dba_admin WITH CREATEDB;

-- Definir expiração de um role temporário
ALTER ROLE temp_user WITH VALID UNTIL '2026-12-31';

-- Renomear um role (útil em reorganizações)
ALTER ROLE app_user RENAME TO app_user_v2;

-- Remover um role (apenas se não possuir objetos dependentes)
DROP ROLE temp_user;

O gerenciamento de privilégios exige atenção especial aos diferentes níveis de objetos. A tabela a seguir resume os comandos GRANT e REVOKE mais comuns, organizados por tipo de objeto, para que você possa consultá-la rapidamente durante o dia a dia:

Um ponto frequentemente negligenciado é o privilégio de USAGE em sequências. Se uma tabela possui uma coluna SERIAL, o PostgreSQL cria uma sequência associada. Ao conceder apenas INSERT na tabela, o usuário pode receber erros ao tentar inserir registros, pois não tem permissão para usar a sequência. A boa prática é conceder USAGE, SELECT nas sequências do esquema juntamente com os privilégios de tabela, conforme mostrado abaixo:

-- Conceder privilégios de escrita em tabelas e sequências do esquema public
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_write;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_write;

-- Configurar privilégios padrão para futuras tabelas e sequências
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_write;

Para auditar permissões, o PostgreSQL oferece visões